Application Security Testing: An Integral Part of DevOps
Microsoft Visual Studio has been a bit slow to embrace the database developer. Until Visual Studio 2005 for Database Professionals there was no serious Visual Studio database development environment. If you're like most TSQL database developers you probably use features of Visual Studio Database Edition for source control and syncing TSQL code, but your development is probably done in some other tool. Visual Studio 2010 may change all that. This article will demonstrate new database development features in Microsoft Visual Studio 2010.
Beta 2, Licensing, GDR
This article is based on features in the Visual Studio Ultimate Edition Beta 2. I'm not only using a Beta version, I'm using a high-end license version. Though a second Beta will likely have feature parity with a product release, not all licenses will have all the features I demonstrate in this article.
Creating a Project in Microsoft Visual Studio 2010
There are multiple SQL Server project templates in Microsoft Visual Studio 2010.
Figure 1: SQL Server Database Project Templates
CLR and Data Tier development is beyond the scope of this article. I'm going to focus on more traditional TSQL development using the SQL Server 2008 Database Project and SQL Server 2008 Server Project templates.
A database project and server project include many of the same template items. Server projects include some additional server specific items like Service Broker Endpoints, Server Accounts, and linked servers. Database projects include some additional storage item templates like filestreams. As you explore each project template you'll also note differences in the template folder structure and deployment options.
To demonstrate key features across the templates I created 3 projects.
- An empty Database Project.
- An empty Server Project.
- A Database Project imported from the AdventureWorks database.
Unless I note otherwise, all demonstrations in this article will use the AdventureWorks database project.
Editor Feature Overview
Intellisense is probably the first feature a developer will notice. As I mentioned earlier, prior versions of the database development environment lacked a good TSQL editor. Below is a short intellisense demonstration.
Figure 2: Using Intellisense
There are some tricks you'll want to follow to work with the intellisense. As you may have observed, I filled in the
FROM clause before adding fields.
FROM is entered first because there is no way intellisense can guess what you will place in the
SELECT portion, unless each field is fully qualified with a Schema and table name. If you do find intellisense annoying you can disable it.
Instead of typing all your code, from the Schema view you can drag and drop fields into your TSQL statements. Unfortunately there is no way to simply drag and drop the field without fully qualifying it.
Code snippets are another nice editor feature. Below is what this behavior looks like.
Figure 3: Code Sinppets
I always forget the signature of various TSQL statements. Creating temporary tables and building database structures are common in Stored Procedures. Creating the signature from an item template would take almost as long as combing through help for the statement signature. Rather than hunting for a statement signature, it was nice to see all the standard statements already built into the editor through code snippets.
If you develop in other Visual Studio languages, you'll appreciate living in a single tool for all of your development. The improved editor completes all parts of application development, from the database, through the data tier, and into the core of the application.
A new editor is great, but database development is difficult without some test data.
Schema compare was what originally drew me to using the Database Project in Visual Studio 2005. In Visual Studio 2010 the Schema compare is there and enhanced. A developer will notice quite a few more compare options to activate and deactivate.
In prior versions, you could create test data with a separate testing project and with Data Generation plans. Data Generation plans have been enhanced a bit in Visual Studio 2010. A Data Generation plan appears below:
Figure 4: Data Generation Plan
You can create a Data Generation Plan from the Data menu or add a Data Generation Plan from the project "Add Items" menu. Selecting the column properties and the Output allows a developer to configure what sort of data is generated in the given column. Output properties appear below.
Figure 5: Data Generation Column output
Data Generation plans are useful for Testing and Debugging, the next stop in the tour.
Testing and Debugging
To do proper testing with other parts of an application a developer must deploy the database. The Deployment section on the project properties includes some basic configuration information. A snippet from the deployment section appears below.
Figure 6: Deployment Properties
Later in the article I'll show where you can extend and customize deployment scenarios. Debugging can be done with a test database or from a local "Database File". Target Connection options appear below.
Figure 7: Target options
Thus far, all the features I've discussed have been productivity boosts. There are other features extending a developers ability to configure and control database execution context.
A database design often goes beyond table, view, function, and stored procedure definitions. Assumptions about case sensitivity and how NULL is handled play a large role in the behavior of the code. I think of the collective configurations as the code execution context. A lot of this information is stored in a set of Properties files. Below are the AdventureWorks project Properties.
Figure 8: Database Properties
Earlier I mentioned configuring and customizing deployment scenarios. Many more deployment options can be found under the
Database.sqlsettings stores things like: NULL handling, Warning levels, Recover mode, and other general database behavior under.
SQL Server Projects have different properties. As you might guess some properties are targeted to the server configuration. Below are properties from a SQL Server Project.
Figure 9: Server Properties
The final feature to discuss is Code Analysis.
Code Analysis tools have been part of Visual Studio for some time, but have really begun to shine in Visual Studio 2008. I've always been intrigued by analysis results and code analysis potential. Though Analysis is not a silver bullet for maintaining supportable code, it can bolster what a developer may know intuitively, but may not have "hard facts" to support. Code analysis can also be embedded in "check-in" and "check-out" policies.
Analysis has been extended to TSQL code. Analysis options can be configured on the Project properties. Analysis properties appear below.
Figure 10: Configure Analysis
Executing Code analysis generates a report. A developer can configure how forcefully to apply code analysis or remove an analysis rule altogether.
Visual Studio 2010 includes a feature rich database development project template for the SQL Database as well as the server. New features include an improved editor, testing, deployment options, and code analysis.