Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 102, Visits: 909
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: Today @ 7:54 AM
Points: 10,784, Visits: 14,679
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 102, Visits: 909
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: Today @ 7:54 AM
Points: 10,784, Visits: 14,679
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 102, Visits: 909
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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 2:21 PM
Points: 317, Visits: 3,032
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