November 10, 2008 at 11:21 am
The question of how to best persist information about linked-server configuration settings has been bothering me for some time.
In the event of a disaster or even unintended corruption of settings by a careless administrator, how can one or more linked servers be brought back to their former pristine state in as timely a fashion as possible?
I'm thinking scripting is the best solution, ie. enforcing a policy that linked server changes in the production environment (linked-server additions and modifications) be made strictly through scripting and NOT through the management studio UI.
Through scripting and a proper release management process that will ensure versioning, an enterprise should be able to revert back to any desired state of its linked-server configuration in a relatively short period of time.
What does everyone think of this?
What are some other strategies people are following with respect to persisting linked-server settings?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 10, 2008 at 11:45 am
Is your linked server environment that large and constantly changing? I would think source control for the changes would be overkill, but I suppose if you have 30 linked servers and they get changed regularly, it may be an option. Of course, if that really is your environment, I would be concerned about the design itself.
I tend to just script them out when there is a change and keep the script with my admin login information - since the linked servers tend to have some login information hard-coded into them.
November 10, 2008 at 12:29 pm
Michael Earl (11/10/2008)
Is your linked server environment that large and constantly changing? I would think source control for the changes would be overkill, but I suppose if you have 30 linked servers and they get changed regularly, it may be an option. Of course, if that really is your environment, I would be concerned about the design itself.I tend to just script them out when there is a change and keep the script with my admin login information - since the linked servers tend to have some login information hard-coded into them.
We do suffer from what I consider linked-server sprawl, and, yes, I totally agree it's a question of proper design.
We had to roll back a new product release recently because one of the new linked servers I had to create prior to the release differed in a single setting from the linked server used in the QA environment; that caused the entire release to fail.
The difference in setting between the 2 environments would have been averted, had I created the linked server using a script, as opposed to creating it on the fly using the SSMS dialogs.
Also, the question of disaster recovery is a frightening one. In the event of the server instance going down irreparably for whatever reason, it would be a slow and laborious process to try and rebuild the linked servers one by one according to specs (especially in the absence of DR solutions such as SQL clustering).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 10, 2008 at 1:09 pm
I can see that, but I would not go overboard on this one.
They probably are not changing daily. I would script them, not allow them to be created through the GUI in production, and keep the scripts somewhere - possibly in source control, but somewhere relatively secure. I don't think I would go crazy with version control on something that should be "relatively" static.
November 10, 2008 at 1:47 pm
Michael Earl (11/10/2008)
I can see that, but I would not go overboard on this one.They probably are not changing daily. I would script them, not allow them to be created through the GUI in production, and keep the scripts somewhere - possibly in source control, but somewhere relatively secure. I don't think I would go crazy with version control on something that should be "relatively" static.
True, the issue is not so much version control but being able to store at least the latest working version of the linked-server setup somewhere safe, in case of a disaster (natural or human-triggered).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply