July 19, 2011 at 5:49 pm
I've got several SQL instances which I want to manage with a central management server. However, although tables, sprocs, functions, etc have the same definitions across the instances, the database name that houses the objects (which I'd query) does is not the same.
Is there a way to create a synonym (or something like that) at the database level?
For example, let's say I have 3 instances in the CMS. Server1 has DBName1, Server2 has DBOthername, and Server3 has DB_Name3. If I wanted to SELECT (or ALTER a sproc) How can I since "USE DBName1" would only be applicable to the one server? Is there some way to say that "DBOthername", "DBName1", and "DB_Name3" all are to be referred to as "DBName"? So I could write the query with a "USE DBName" and have the query work across all servers?
July 19, 2011 at 10:51 pm
Hi Skippy
Welcome to the conundrum of central management. It's a nice problem to have and there are many ways around it.
Some prerequisites.
Any data etc that you are going to pull with this type of work will go via linked server and you will most likely need to setup RPC and MSDTC and also check the comms between the boxes and ensure all the ports are open and also make sure the security is setup. You'll need to configure msdtc which you can do at component services.
What I do, (it's not the greatest but I find it the most stable)
Use a linked server and send the code to your server in terms of dynamic SQL. Why this ?
Well let's say you can modify your proc across all the servers, great! but you still need to maintain them. If you have 5 servers no problem.
But lets say you monitor 5 indicators, 5 procs. across 5 servers. Although ti's the same proc techinically you are managing 25 unique procs.
Write a generic proc that you can run on any server via <linked server >..sp_executesql @query.
If you have 5 servers again you can choose to hardcode those server names or if you don't you can nest the dynamic SQL in another loop to get the servernames. Then you use the @output param for sp_execute sql to pass whatever you need between them.
It does get a bit tricky though as you might need to use dynamic SQL on the box you are executing on (where you diagnose).
Another way which I have used before which I really like, and is good in the long run.
Use transactional replication and publisher your stored procedure.
So you just change the publication and whether you have 1 or 50 subscriber to the proc (the relevant monitored servers) it will update when you alter your master proc.
The nice things about this is you can also replication the procudures' execution. which means, if your servers are all in the same timezone you can simple exec you monitoring proc on your publisher and it will execute you monitoring proc on all you subscribers. It's a very cool setup and fun to get to learn replication.
If the database is already heavily publisized and use replication for production i.e generating $$$ then you should rather not "test" it on there.
You can also check out policy management and the ability to call SQL scripts in the policy. Then you can also remotely execute your query. But it's done via the GUI.
For your problem now.
you will have to atleast create some lookup of the databases that contain your proc to alter, if you don't have the you will first have to connect to each server, and then go through every DB, allocate your proc and then flag that DB as the correct one. It's do-able but probably a bit messy.
Create a lookup of DB's, cycle it with a while loop and build SQL to execute on each box.
You can't use synonyms for procs to alter them.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply