One of the toughest aspects of managing multiple SQL Server instances is the “multiple” part, especially when you have a configuration change that needs to be made consistently across servers -- or even databases -- that support the same business application. You must go clicking through the graphical user interface
It’s tedious work, and worse, there’s huge potential for human error; people get bored of clicking, and it’s easy to miss a click or two on the odd server.
Where there’s pain, however, there’s almost always a third-party tool to help make life easier. Idera, for example, makes a SQL Admin Toolset that includes two dozen tools designed to accomplish various tasks, and many of them are capable of targeting multiple SQL Servers at once. There’s even a tool to compare the configuration or two or more servers so that you can catch any “tedium-errors” that someone may have made in the past. Quest’s Toad is a hugely popular tool that includes capabilities for making things happen on multiple servers or databases.
And where third parties break ground, Microsoft is sure to follow. One of the major new features in SQL Server 2008 R2 is an enhanced set of multi-server management tools. SQL Server has had some basic multi-server capabilities for a while, as part of its multiple-server jobs feature. Basically, you’d create a SQL Server Agent job. That job can do anything, like run Transact-SQL statements, which can reconfigure just about anything in SQL Server. You’d target the job to multiple SQL Servers, and the various instances of Agent would coordinate to make it happen.
In SQL Server 2008 R2, however, a new tool called SQL Server Utility provides a more unified view of multiple SQL Server instances, including performance and utilization information. Most features are unchanged in the final release of SQL 2008 R2.
This tool isn’t designed to push jobs out to multiple computers; the existing multi-server job functionality continues to handle that. It does, however, give you a unified view of what’s happening on multiple instances – including a very nifty dashboard that shows you servers that are dangerously approaching max utilization in terms of CPU, memory or disk space. It’s a great additional capability for larger multi-server environments.
Miss a tip? Check out the archive page for Don's series, SQL Server for the Reluctant DBA.
ABOUT THE AUTHOR
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at www.ConcentratedTech.com.
This was first published in November 2010