August 28, 2014 at 4:36 pm
Hello SSC,
Does anybody have a tool to deploy an object like a stored procedure to all servers and then be able to verify they were in fact deployed to all server?
I work with multiple production servers and each production, each has two development servers. I would like to be able to deploy the same procedure to all servers with T-SQL or PowerShell if possible. Also, I would prefer to be able to do this without using ::SQLCMD, if possible.
Thank in advance for your time.
Cheers!
Tim
The pain of Discipline is far better than the pain of Regret!
August 28, 2014 at 7:23 pm
Hi mate,
Seeing as how no one else wants to bite at this. I'll have a go.
You haven't mentioned how you manage your servers, e.g. How do you know what the connection details are? What databases on those servers are supposed to have this stored proc change?
I'd probably tackle this through a local SQL Server on a "management machine" - or your machine.
1) Add all the linked servers that you intend to manage.
2) Start with a cursor that loops over the linked servers (listed in sys.servers), with what ever logic you want to select them.
3) Use the EXEC command with the AT linkedservername parameter to alter or create the stored procedure
4) Use something like this
SELECT sql_modules.definition
FROM sys.sql_modules
INNER JOIN sys.objects ON sql_modules.object_id= sql_modules.object_id
WHERE objects.name = '<name>'
To get the text of the stored procedure at the other end. But really if the exec command succeeded, I'm not sure why this is necessary.
If you really want to get tricky and keep the schemas of all those databases the same, you could have a master schema on your "management machine" and write a DDL trigger that then propergates that change to the other linked servers.
August 29, 2014 at 12:36 am
I would utilize Registered Servers within SSMS. If you add all the servers in question, grouping as you see fit [e.g. all development, all production, all staging/QA]. You open a multiserver query to all of them or the group you want to deploy it to first and then you treat it just like deploying it to a single server.
I do this to update the DBA database I have running at client environments if I am adding new tables and procedures, or updating them. A decent example[/url] I found online that included some screenshots.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 29, 2014 at 6:16 am
GM Adam,
Sorry about that. Yes they are all SQL servers, SSMS 2008 R2. I thought about using Execute At using linked server, which is a great idea. I just do not have control over creating the linked servers. So I will test out your idea and this way I would only have to connect to three servers versus 15. Thank you for the idea.
Cheers!
The pain of Discipline is far better than the pain of Regret!
August 29, 2014 at 6:17 am
GM Shawn,
Thanks for the reply, I will also test out your idea as well, thanks a bunch, I appreciate it. I did not know this was an option.
The pain of Discipline is far better than the pain of Regret!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply