Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

creating table on all sql servers in the environment Expand / Collapse
Author
Message
Posted Monday, September 16, 2013 2:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:54 PM
Points: 92, Visits: 737
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...
Post #1495278
Posted Monday, September 16, 2013 3:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1495303
Posted Tuesday, September 17, 2013 8:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:54 PM
Points: 92, Visits: 737
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...

Post #1495528
Posted Tuesday, September 17, 2013 9:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
Another option would be creating a master server and creating the job there. Start with this BOL topic



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1495552
Posted Tuesday, September 17, 2013 11:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:54 PM
Points: 92, Visits: 737
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...
Post #1495604
Posted Tuesday, September 17, 2013 12:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:18 PM
Points: 206, Visits: 2,030
This can be done with an SSIS package, as documented in the article:

Looping through multiple servers in SSIS
Post #1495632
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse