September 16, 2013 at 2:33 pm
Hi,
I have a requirement to create a table in database on all sql server instances in our environment.
We have a monitoring tool and it uses a sql instance.I can get the list of instances ( dev and prod ) from the monitoring tool. My concern is to connect to each and every instance and create a table.
With SQL agent i dont think its possible but i guess we can acheive this through SSIS.. Please post your valuable suggestions and let me know if you have any questions....
Thanks in advance...
September 16, 2013 at 3:22 pm
You can create a server group in SSMS (Registered Servers tab), right-click on the group and select new query. Then the query will be run against all the servers at one time. As long as the database you are creating the table in has the same name it will work. You can see an article about mult-server queries here.
You could also use PowerShell, but I think a multi-server is query the way to go.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2013 at 8:23 am
Jack Corbett (9/16/2013)
You can create a server group in SSMS (Registered Servers tab), right-click on the group and select new query. Then the query will be run against all the servers at one time. As long as the database you are creating the table in has the same name it will work. You can see an article about mult-server queries here.You could also use PowerShell, but I think a multi-server is query the way to go.
Thanks Jack for your response. I need to drop and create this table and insert some values for every month....so its kind of automated process.....i believe we can achieve this through powershell....please post your ideas...thanks in advance...
September 17, 2013 at 9:19 am
Another option would be creating a master server and creating the job there. Start with this BOL topic
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2013 at 11:34 am
Jack Corbett (9/17/2013)
Another option would be creating a master server and creating the job there. Start with this BOL topic
Thanks Jack...
i found this website which is exactly i'm looking for..using powershell execute sql scripts on multiple sql instances..i'm posting here ....it might help some people in future...
http://bi-bigdata.com/2013/03/14/powershell-for-executing-a-sql-query-on-multiple-servers/
Thanks for all your help...
September 17, 2013 at 12:32 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply