It's time to celebrate! On November 7, we release SQL Server 2005 and Visual Studio 2005 to the world. I hope that many of you reading this column have already had a chance to try these great new products, but if you haven't, I recommend getting your hands on them soon so that you can experience all the new features and functionality. To highlight the products' dual launch, this month's column focuses on how SQL Server 2005 and Visual Studio 2005 work together to make database-development tasks easier, faster, and better. The road to this launch has been long, and tougher than most because of the challenge of trying to simultaneously beta test and ship two major products that rely heavily on each other. The two products are tightly aligned in many areas, the most obvious of which is in the SQL Server 2005 Common Language Runtime (SQLCLR), but the products also overlap in other, less obvious, places. For example, all of the data-access functionality (e.g., ADO.NET 2.0) was created by developers working on the SQL Server development team, then integrated into Visual Studio by the Visual Studio development team.
One exciting feature, CacheSync, lets the database notify the application of changes to any data underlying the query that the application is using in the middle or client tier. This feature was so exciting to the ASP.NET development team that they fully integrated it as part of ASP.NET 2.0. To turn the feature on, you simply use a SQL- DataSource control to access your SQL Server data, then set the SQLDataSource's EnableCaching property to true. This feature lets applications query the database on only the initial page view. The first time an application views a page, the application calls the database and requests a dataset based on a query or view in the database. ASP.NET then caches the data in the application tier, and all subsequent page views use the data stored in the cache until the database notifies the application that some of the underlying data has changed. At that point, the application can decide when to query the database again. The main benefit of this feature is that developers don't have to use some form of polling mechanism to develop their own cache invalidation code. In addition, the feature significantly improves both the database and application performance, thanks to fewer database calls and less network traffic.
From a developer point of view, one way the integration between the two products really pays off is in debugging. Anybody who has tried to use the debugging features in SQL Server 2000 knows that it's not the best experience. Debugging in SQL Server 2005 and Visual Studio 2005 is greatly improved. What you now get is true integrated debugging across languages and across tiers. What this means is that a developer working in Visual Studio can set a breakpoint on code running on his machine, then seamlessly step through that code as usual. If the code calls a database object (e.g., a T-SQL stored procedure), the debugger can step into the T-SQL language and continue the debug process. In addition, the T-SQL code doesn't need to be running on the developers' machine; assuming the administrator has granted the correct permissions on the database, the T-SQL code could actually be running on the server, and the developer would be able to step through it without needing to know where it's actually executing. From within the T-SQL procedure, you can also continue to debug a managed code procedure, then have full fidelity on your return to the local code on the developer's machine. Throughout this process, you get all of the debug information from the call stack that you'd expect in a fully featured debugger.
To further simplify control of your database-development projects, you can now place all your managed code database objects into the same source-code control system you use in your regular applications. This feature gives you the ability to easily control all of your managed code—regardless of where it lives—from one place.
One key goal in the release of SQL Server 2005 and Visual Studio 2005 was to make it possible to consolidate a developer's work environment into a single place. With previous SQL Server and Visual Studio releases, a developer working on a database project had to use multiple tools to get all parts of his job done. Now, developers need only one tool, Visual Studio, to perform all their tasks. Visual Studio 2005 supports some new project types for working with databases. Each language is now associated with a database project that lets a developer create a managed-code database object such as a stored procedure or user-defined function (UDF). Visual Studio 2005 also supports some completely new project types, one of which is a Database project. From this project, a developer can easily use the database-design tools to create a new database and generate SQL scripts that create or change the database. The Database project also lets you debug T-SQL as you're creating it. This functionality saves developers from having to open Enterprise Manager to create and modify database objects. Developers can also use query scripts to create and run queries against the database directly from Visual Studio, which negates the need for a developer to start Query Analyzer.
Because you can create, modify, and work with databases directly from within Visual Studio, the Visual Studio development team sensibly included an easy way to deploy a database with your application—another example of the cohesion between the two products. SQL Server Express, which ships with Visual Studio 2005, is a version of the SQL Server database engine designed for client and embedded scenarios. Developers can deploy SQL Server Express with a single click, creating a local database that they use with their applications. And developers can deploy the database at the same time they deploy their applications. The SQL Server Express service can automatically connect and disconnect from the database, so developers don't have to write scripts to attach and detach the database before deploying the application.
Database developers have never had it so good, thanks to the tight integration between SQL Server 2005 and Visual Studio 2005. Finally, you can work on a database project from one interface and use many pieces of new and enhanced functionality—in the application layer as well as the database layer—to develop richer and more powerful applications.
Over the last few months, I've covered many of the enhancements and new developer features in SQL Server 2005, and I'll continue to introduce and explain features in upcoming columns. Next month, I look at what it means to upgrade your current applications to SQL Server 2005 and what steps you should take to take advantage of all the new features. To finish this month, I want to send a big thank you to all the teams who work on SQL Server 2005 and Visual Studio 2005 for shipping a pair of great products that will change the way you build database applications.