SQL Server
I have been testing out the SQL Server 2008 R2 CTP from August and been experimenting with the SQL Server Utlity to perform multi-server management from a central server. You can also monitor Data-tier applications and their activity on the database server. Visual Studio 2010 allows you to create a DAC (Data-tier Application Component) which is a definition of the data objects that an application needs. This may include the obvious database objects like Tables, Views and Stored Procedures but also server level objects like Logins. It gives a convenient unit of deployment for an application. VS 2010 generates a new file extension, namely the .dacpac. The idea is that the development team can define this when developing the app so that it can be handed over to the DBA team for deployment with the application. The deployment could be to a staging server for final system and integration testing before going into production. Previously, we had to fill out forms defining exactly the dependencies between the database and the app which were prone to errors because of the disconnect. If you already have a Database defined you can create a .dacpac retrospectively using SSMS 2008 R2 (Database/Tasks). This will gather together the database objects and server logins for deployment. Either way, you can then use the new Deploy Data-tier Application wizard to actually deploy those objects (without the data) to your staging server for final testing. You will have to organize another method for pumping the data across but SSIS is perfect for this. Once the DAC has been deployed to the staging server you can then monitor performance of that Data-tier application through the SQL Server Utility, in a similar way to Managed Instances are monitored. The SSU, new in R2, provides a centralized dashboard view of the health of your servers and database applications. You can drill down to view individual storage utilization or focus in on the most common queries and their performance metrics using provided reports that you can customize or create yourself. Once deployed to production, the idea is that you can do the same with your production servers. If you use the Data Collector on the database server, even more information will find its way to the UMDW, the Utility Management Data Warehouse. To me, this is the Enterprise Performance Data Warehouse, used to analyse performance of our multiple servers and applications. The way this is done, is that jobs are setup on the Managed Instances to collect performance data through Perfmon and running DMVs. These metrics are then uploaded to the UCP (Utility Control Point) that hosts the UMDW. Recommend a separate server for this database…I was only testing for a while and it grew to 16GB before I knew it! Of course, you have to evaluate the extra overhead of the “collect and upload” process running on each server (by default this process runs every 15 minutes). But at the end of the day you have a dedicated Data Warehouse that is populated with all the general performance metrics you would need to evaluate the health of your servers and databases. One major drawback: The CTP requires that the Managed Instances and Data-tier Applications be present on 2008 R2 Servers using the Enterprise edition but that will surely change in future CTPs. Otherwise, it wouldn’t be health care for all, would it? cheers Brian
