Developing Enterprise SQL Server Features in Visual Studio database tooling
SQL Server 2014 introduced a number of new enterprise features. The most notable of these are memory optimized tables and natively compiled stored procedures. When developing databases with enterprise features in Visual Studio there are a few best practices to be aware of.
Debugging enterprise features
We encourage users to apply multiple levels of validation during database development: live syntax error highlighting, build time validation, F5 debug deployment, and unit testing for comprehensive validation of actual behavior. Modify, build, deploy, and test.
F5 debug deployment is an important step in this validation loop but the default LocalDB debug target may not support all the features you use in your database. To validate databases with these features we encourage you to use an alternative SQL Server with full support. Common approaches include obtaining a license for a Developer Edition and installing a server on each developer’s machine, or using one server for the development team and applying a standard naming scheme to distinguish each developer’s tests databases from each other.
Regardless of which approach you choose you will need to change the debug target for your database projects. At present this is a manual step for each project in your solution.
Changing the debug target inside Visual Studio
For each project:
- Right-click on the project and choose Properties
- Select the Debug tab
- Click the Edit button under Target Connection String to change the debug target
- Enter the name of your development server and a database name that will avoid conflicts with other developers / databases in other solutions.
-
- Note that to auto-generate the name for a project you can set $(USERNAME)_$(Name) as the database name in the connection properties. If you log into a machine as UserA, and the project is called Project1, this will deploy to a database named UserA_Project1.
- Click OK, then save the Properties page and close it
- Hit F5 and verify that the expected debug database has been created
Changing the debug target by editing the user file
If you are updating multiple projects might be easier to update the user file. For a project Project1, this will be a file called Project1.sqlproj.user located in the root folder of your project. Edit this in notepad and add the following entries to specify the server name and an auto-generated database name to deploy to. Note that MyServerName should be replaced with the server you wish to connect to.
<PropertyGroup> <TargetDatabase>$(USERNAME)_$(Name)</TargetDatabase> <TargetConnectionString>Data Source=MyServerName;Initial Catalog=$(USERNAME)_$(Name);Integrated Security=True;Pooling=False</TargetConnectionString> </PropertyGroup> |
What features are not supported by LocalDB?
Features not supported by LocalDB include, but are not limited to:
- Memory optimized Tables
- Memory optimized FileGroups
- Natively Compiled Stored Procedures
- Full text indexes
- Filestreams
- Partitioning
Validation of SQL Server 2014 features
We strongly believe in the benefit of build time validation. At build time most issues that would block database deployment will be marked as errors and block project build. It is important to know that some new SQL Server 2014 features do not have this level of validation. Memory optimized tables and natively compiled stored procedures introduce a number of restrictions on TSQL syntax that will not be enforced at build time. The best practice to when implementing these features is to use the F5 debug deployment to catch any deploy time issues.
If you want to catch these issues earlier you could also add custom Static Code Analysis rules to catch these at build time. There are a number of examples of rules that check for SQL Server 2014-specific issues in our samples project. These are planned for inclusion in future releases of the database tooling as either validation rules that run automatically during build or as standard Static Code Analysis rules. Suggestions for additional rules are welcome and can be sent via our forum or Microsoft Connect page.