June 23, 2009 at 4:49 pm
Let's see, if this can be done on SQL Server. I have 20 SQL Servers (SQL 2005 - but will be moving to SQL 2008 pretty soon) all of these servers are Pre-prod Servers. These servers has similar databases and every now and then i get request from developers to run there code on these server - databases. i want to avoid the manual process of going to each server and run it manually from there. i was wondering, is there a way, i could automate this? Say i get a update script to run and i put it in some sort of central policy and fire it and then it updates all the sql servers?
possible? If yes, can someone please share to make this happen?
thanks,
June 23, 2009 at 10:00 pm
This is possible in SQL Server 2008 .
We call it Central Management Server.All you need to do is to choose a SQL Server 2008 server and register other servers to it .There is an option called as "register central management server".
Once you have registered other instances to this server , open a query window and run a query .It will show you that it ran on all the registered servers and show you the outputs form those servers .
Check Kimberly's blog :
HTH
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 24, 2009 at 5:50 pm
one issue:
I registered my SQL 2008 under central management server along with another SQL 205 instance. when i try to run the query, system complains "Operation is not supported on SQL Server 205. (Microsoft.sqlserver.management.sdk.sfc) But the article URL you provided talks about running queries on SQL 2005 without any issues.
am i missing anything here? Appreciated any input here.
Thanks,
June 24, 2009 at 9:16 pm
As i said my friend : this is only supported in SQL Server 2008.
All your instances should be on SQL server 2008 .
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 24, 2009 at 9:20 pm
I have never tried this on 2005 from 2008 .
Unfortunately i cannot try it now but let me see if that is possible ...
Yes she does say that its possible to have it work with 2005 from 2008 ..
Let me check and get back to you ..
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 24, 2009 at 9:34 pm
Hey,
We have the same type of requirement, I have automated the process using OSQL utility and .bat file and I is running successfully.
go through the below URL for more info about OSQL utility
http://technet.microsoft.com/en-us/library/aa213088(SQL.80).aspx
right now I am at home, I can send you the code once I reach office. ..
Rajesh Kasturi
June 25, 2009 at 10:38 am
Abhay - the article talks about running your queries on SQL 2005 as well. Please look into it and see, what am i missing?
Rajesh - if you could please post the code here, that will be great.
Thank you Guys for all your help.
June 25, 2009 at 11:10 am
There are also 3rd party tools you can look at for this. I have used both Red-Gate SQL Multi-Script as well as Idera Multi Query and am pleased with both of them. They let you specify which servers and which databases you want to code ran against.
Chad
June 25, 2009 at 11:31 am
Chad - thanks for the info.
We have a large SQL footprint and if SQL 2008 can do what we need - then we want to avoid having any thirdparty's footprint at our company. i am sure, sql 2008 can do what we are looking for. but dont know, if it can run against sql 2005 or not. still waiting for abhay's feedback.
June 25, 2009 at 2:17 pm
I hope u can create linked servers poiting to all server from a common server and try running queries
June 25, 2009 at 8:17 pm
Linked server will not serve that purpose my friend .
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 25, 2009 at 11:40 pm
Yes its possible :
1) install SQL Server 2008
2) open the management studio
3) in the toolbar go to view >> select Registered servers
4) in the database Engine section you will see the icon of Central management servers.
5) Right click on it and select Register central management server
6) Register your 2008 instance .
7) right click your 2008 instance that is now acting as central management server >> select new server registration and register 2005/2000 instances .
8) Once done right click on the central management server (and not the registerwed servers under it) and select new query .
9) test it by running select @@version or query any system database .it will show you results from all the registered databases .
10) You owe a bottle of chempegene to me . I went throuhg all the pain right from installing (as its blocked in our domain but still could creck through the websence filters) 😀
have a nice weekend ...
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 26, 2009 at 10:10 am
Hey just tried it in 2008 you can run queries using the central management server against 2005 and 2000 as long as you don't use some thing that is not supported in the older versions. http://msdn.microsoft.com/en-us/library/bb964743.aspx
June 26, 2009 at 11:08 am
Is the query you're running doing something that is not supported in 2005? Can you manually run the script against an '05 instance without issue? You can most definitely register 2000/2005/2008 servers in your central management server and hit them all the same ... under the assumption it's supported per version.
As others have stated, there are multiple other avenues. You can use osql/sqlcmd/vbscript/powershell/opendatasource/linked servers/3rd party apps, etc. etc. Yet the easiest way would indeed be to simply use the multi query abilities now native in 2008's management studio.
June 26, 2009 at 1:39 pm
Hey everyone, you don't need a CMS available to do this. All you need is the 2008 client installed. Once you have that, create a group in Registered Servers (either local or on your CMS).
Once the group is created, add the registered servers you want in that group. Right-click the group and select New Query. That will open up a multi-query window that is connected to all servers in the group.
Anything run in that query window will be executed against all servers in the group. If you look in Options, you can decided whether or not you want results returned as separate results for each server, or combined results and whether or not you want a column added to identify the server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply