September 21, 2010 at 2:42 pm
We have set up a Disaster-Recovery (D/R) environment by mirroring the databases from the production environment.
However, this does not take care of a host of other settings: jobs, logins, server permissions, trace flags, sp_configure settings, linked servers etc.
Ideally, we would like to keep the D/R and production environments in sync at all times, and that includes the server-level settings outlined above.
How do people approach this challenge? I would be interested in a discussion on this issue.
__________________________________________________________________________________
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]
September 21, 2010 at 8:38 pm
We would script out the settings and then submit them to source control. When a failover occurs, then run the scripts as a step in the DR plan.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 22, 2010 at 3:52 am
CirquedeSQLeil (9/21/2010)
We would script out the settings and then submit them to source control. When a failover occurs, then run the scripts as a step in the DR plan.
Thanks for your reply.
Yes, that is certainly an option, but it would need to be done on a regular basis to ensure production changes are captured over time. Also, that would mean that a certain length of time would be acceptable for running the scripts and bringing the D/R environment up to speed, in case of a disaster.
In our implementation, an automated process scans both the production and D/R environments for differences and alerts the DBA team if there are any, so they can be fixed; so we are doing it in an incremental way.
__________________________________________________________________________________
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]
September 22, 2010 at 5:17 am
SSIS running a package every x minutes will do this very well and we have used this in OLA 1 production enviroments for some time.
we use the following tasks from within SSIS;
Transfer job Task
Transfer Logins Task
And add some TSQL coding to handle server level perms and your done.
September 22, 2010 at 8:39 am
Geoff A (9/22/2010)
SSIS running a package every x minutes will do this very well and we have used this in OLA 1 production enviroments for some time.we use the following tasks from within SSIS;
Transfer job Task
Transfer Logins Task
And add some TSQL coding to handle server level perms and your done.
Thanks, that would certainly work, but you would still need some way of comparing settings between environments to ensure the automated sync process is working accurately.
Jobs, logins and permissions are definitely some of the most important things to look at, but there are also others: trace flags, sp_configure settings, system-db custom objects and permissions, application-file directory structure (if applicable), SQL agent proxies, linked servers etc.
I wonder if there is a tool out there that compares server-level settings between database environments, ie. those settings outside the databases themselves, and provides a report on differences to users. That would certainly come in handy...
__________________________________________________________________________________
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]
September 23, 2010 at 4:23 pm
By using central management server in SQL 2008 you may run multi server script and compare the outputs!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 23, 2010 at 4:34 pm
Geoff A (9/22/2010)
SSIS running a package every x minutes will do this very well and we have used this in OLA 1 production environments for some time.we use the following tasks from within SSIS;
Transfer job Task
Transfer Logins Task
be careful, transfer logins task works fine for windows authenticated ids but for SQL authenticated ids the task actually resets the password, setting you up for problems. does not set the default language for the id either.
I prefer sp_help_revlogin for this task.
---------------------------------------------------------------------
September 23, 2010 at 4:39 pm
Perry Whittle (9/23/2010)
By using central management server in SQL 2008 you may run multi server script and compare the outputs!
Thank you that's a good suggestion! In the current implementation I have created an SSIS package that performs data transfers from D/R to prod servers so I can compare the data in one place and report on it. With CMS in SQL 2008 this will probably be less painful.
__________________________________________________________________________________
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]
September 23, 2010 at 4:41 pm
george sibbald (9/23/2010)
Geoff A (9/22/2010)
SSIS running a package every x minutes will do this very well and we have used this in OLA 1 production environments for some time.we use the following tasks from within SSIS;
Transfer job Task
Transfer Logins Task
be careful, transfer logins task works fine for windows authenticated ids but for SQL authenticated ids the task actually resets the password, setting you up for problems. does not set the default language for the id either.
I prefer sp_help_revlogin for this task.
Yes, I've noticed that with the SSIS task; I also use sp_help_revlogin for tranferring logins between SQL instances.
__________________________________________________________________________________
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]
September 23, 2010 at 4:44 pm
Perry Whittle (9/23/2010)
By using central management server in SQL 2008 you may run multi server script and compare the outputs!
I must look into this as we migrate to SQL2008.
Presently we are very careful about change control. all server level changes are done by the DBA team and we duplicate what we do in DR at the time we do it in live. good change control processes are vital.
Also Server level objects are reversed engineered out at regular intervals via SQLAgent jobs and the results copied to the DR server. then should a problem arise the information is available to easily remedy it.
Multi-site clusters get around most of these issues if you have the money...........
---------------------------------------------------------------------
September 23, 2010 at 11:36 pm
With each new release of SQL Server, MS are providing the DBA with more tools that are netter equipped. CMS is really starting to become a seasoned tool IMHO!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 24, 2010 at 9:57 am
just been trying out CMS.
Is it me or are the instructions in MSDN on setting it up inaccurate? I don't get the drop down options as they specify them and don't seem to be getting results returned from both servers in testing.
---------------------------------------------------------------------
September 25, 2010 at 4:55 am
george sibbald (9/24/2010)
just been trying out CMS.Is it me or are the instructions in MSDN on setting it up inaccurate? I don't get the drop down options as they specify them and don't seem to be getting results returned from both servers in testing.
whic version are you using (SP, CU)?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 25, 2010 at 5:48 am
SQL 2008 SP1 CU1 (10.0.2710)
when the instructions say right click select 'new,' new is not an option, and then connect, connect is not an option
specifically these instructions seem inaccurate
http://msdn.microsoft.com/en-us/library/bb934126(v=SQL.100).aspx
---------------------------------------------------------------------
September 25, 2010 at 12:50 pm
yes, those instructions definitely dont seem to stack up.
Right click "central managements servers" and select "register central management server"
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply