At SQLBits, MVP Jasper Smith of SQLDBATips.com held a session on what’s newin SQL Server 2008 R2 for database administrators.

Application and Multi-Server Management

To illustrate the problem with server sprawl, Jasper gave some quick stats about a big enterprise.  Microsoft IT has around 5,000 SQL Server instances with 100,000 databases, averaging CPU utilization under 10%.

To help solve this problem, SQL 2008 R2 introduced Data-Tier Applications (DAC packs) as a new unit of deployment for T-SQL apps.  They contain developer intent as policies – for example, the developers may want high availability, 2 CPUs and 2 gigs of memory.  Of course, this can present problems down the road – developers will always want more resources.  Jasper demoed the SQL Server Control Point.

SQL Server Connection Director & Connection Plans

Right now, SQL Server connection strings are tied to the server name and database name.  It’s not easy for DBAs to move heavily utilized databases to a more powerful server because we have to touch every client.  The problem is made worse because over time, we need to continuously consolidate databases together as older databases seem to stay on old SQL Server versions.  Take SQL Server 2000 – many of us are still managing a handful of 7 or 2000 servers because a handful of apps just won’t work on newer versions.  We need to combine these old things onto the minimum number of servers possible, but database moves are painful.

There’s help coming in the long term, but not in SQL Server 2008 R2.  The CTPs of SQL Server and Visual Studio for next year’s release contain a feature called the Connection Director, but Microsoft has already said Connection Director won’t be included in the RTM builds.  Bummer!

The new declarative connection plans will let us specify connection strings that lives in Active Directory or on a file share as shown in Books Online.  We can specify a cache refresh interval much like DNS time-to-live parameters – we would put a long cache refresh interval in the plan normally, and then turn that time down shorter when we plan to do maintenance.  Jasper demoed doing exactly this, changing the plan while running an app that regularly connects to SQL.

Me likey!  Can’t wait for that to be introduced.