SQL Central Management Server (SQL-CMS)

Ever had to document 20+ SQL instances by yourself?  If so, you will recognize the agony of having to run the same script over and over again just to get the same info from all your instances. I have worked at clients with 200+ instances and little or no documentation. It can be a true headache.

Fortunately the fine folks of the Microsoft have had the same problem (I used to work for Microsoft and believe me their SQL Server landscape is HUGE!!) and someone (Buck Woody, I think) created and implemented a nice solution to cope with the ever increasing numbers of SQL instances. This too little known feature is called the SQL Central Management Server or SQL-CMS for short.

What is it…. Well is it a way to centrally administer the connection strings to instances in your landscape. These connections are (securely) accessible by other DBA’s or developers and will help you get and keep control over your landscape. Is that it… Nope, it is much more…. Once you have set it up and registered and grouped your SQL instances you can actually connect to 1, a group or all SQL instances in your CMS with just 1 click. It will open a new query window in the SSMS but this window is connected to all the servers in the CMS group you connected to. As you might imagine, this will come in very handy when collecting information about your instances, servers, databases, etc…. Oowww is that all. Well no it’s not… you can also do some really nice other stuff. For one you can also use the power of policy based management and evaluate your entire landscape on the correct use of e.g. the auto-close setting for databases. Or you can run all the Best-Practices policies (they come with the Dev and Enterprise editions) against your landscape… Hhhmmmm now things start getting interesting, right….

A few things to think about…. Does it do everything you might want? Nope! The security model to protecting the registered server in the CMS is limited. You either can see them all, or you can’t see any. There is no option of limiting access to a certain group of server in the CMS. Is that unfortunate yes, but it is no problem. Just make sure you have the proper security set up on your instances and you should be fine. Another thing to be mindful of. It only works with Windows Integrated Security. Again not really a big problem in most cases, but you will always run into that one exceptional client where they do not use an AD. Oohhh well, nothing is perfect right? 🙂 Still it has proven it’s usefulness to me on more then one occasion and the best part is… it is free!!! It comes with all versions of SQL, even the Express version. I know, I know, there are other products that do pretty much the same thing but most of those cost money and don’t integrate into the Management Studio.

In my opinion this is one of the most underrated features of the SQL Server product. It is free, useful, easy to setup and flexible enough to tune if you would want to. So why not look at it and play around with it. I promise you it will be worth it!

More info: http://msdn.microsoft.com/en-us/library/bb895144.aspx

SQL Central Management Server (SQL-CMS)

SQL Server Tools and Add-ins

Is it bad form to reblog a reblogged post…. Ooohh well…. Clint reblogged this posting about some useful SQL Server Tools and add-ins. I thought you might like it too.

Clint Huijbers' Blog

I stumbled upon a post from Pat Phelan and I found it worth copy/pasting it here:

(I don’t know these guys btw)

In terms of SSMS (SQL Server Management Studio) add-ins, the only one that I’ve ever used (or even considered) was SSMS Tools Pack which is written and maintained by Mladen Prajdić.

Another tool that runs outside of SSMS, but is probably more useful to me personally is SQL Spec. This tool will document an entire SQL Server from logins and linked servers down to columns, datatypes, and samples of the data! It will also document SSIS package, Access Databases, DB2, Oracle, and just about anything else that you need to understand when doing a database project. I can’t say enough good things about Jesse and the only bad thing that I can say is that he doesn’t come here often enough!

A great tool to have when…

View original post 141 more words

SQL Server Tools and Add-ins

Respected SQL Server Resources

For all you people that wonder where I get scripts and other cool SQL stuff I compiled a lil’ list of links to sites with good SQL Server content. Hope you will enjoy there sites as much as you will enjoy mine (hopefully in time). 🙂

  • http://www.brentozar.com/
    If you don’t know Brent Ozar you have not been in the game long enough… The Blitz scripts he and his team created are truly kick-ass!! Check out his site and enjoy their wicked sense of humour.
  • https://www.sqlskills.com/
    Paul Randal, Kimberly Tripp, Jonathan Kehayias and a few other great minds do their work here. If you need info about the DBCC and locking and blocking issues, this the site I look at first…
  • http://www.sqlpass.org/
    The worldwide organisation of SQL Server Professionals. If you are not a member but work with SQL anyways, you should become a member TODAY!
  • http://blog.sqlauthority.com/
    Ever heard of Dave Pinal? He is the expert behind this site and an experienced MVP. He has this great blog with easy to understand explanations and examples.
  • http://blogs.msdn.com/b/sqlcat/
    The official blog of the SQLCAT (Customer Advisory Team) guys. This is the team that Microsoft lets loose on all the truly high-end customer issues. They build some of the biggest and highest performing SQL setups around and blog about some really low-level stuff. Working with high-performance, VLDB’s or leading edge solutions, check out this blog for tips!
  • https://ola.hallengren.com/
    Managing SQL Server backups and indexes has never been more easy then with his maintenance solution. You will never use native SQL maintenance plans anymore once you learned the power of his award winning maintenance solution. Really, this is a MUST know solution.!!!!

Read everything one these sites, remember it and you will be an instant guru…. To bad it will take you about 10 bazillion years to read everything, but still…. These sites and the people behind them have so much knowledge and experience, I can only be in awe (kuddo’s for the first one to post a good 32bit joke here). Respect for all them and I suggest you all follow their blogs, sites and twitter feeds. It will make you a better DBA, I promise. 🙂

Respected SQL Server Resources