Restore many SQL Servers to one Box with Many named instances???
-
We are looking into various ways to save money with our disaster recovery testing. One option is to reduce the number of servers by installing named instances of SQL Server on a few large servers at our DR site. I'm going over in my mind the possible problems associated with this approach. The biggest problems appear to be the fully-qualified server name \\server\instance1 and also DTS connections.
To see what would happen I created a default instance and one named instance. I stopped the SQL services for both instances and started the named instance in single-user mode. From here I restored a backup of the master database and tried to restart SQL Server. The service would not start because it could not find the location of master.mdf and master.ldf contained in the database backup file.
I copied master.mdf and master.ldf from the original server to the new named instance, in the appropriate drive locations, and then was able to start SQL Server. It came up with all user databases marked as suspect, which I expected. I believe I also could have changed the startup parameters, stopped SQL Server and moved the master MDF and LDF files.
Since the user databases have yet to be restored, all were marked SUSPECT. The next step would be to restore each database, specifying the new location of each database.
Does this make sense and am I missing anything?
In regards to DTS packags, I believe they may kill this entire approach since restoring MSDB will create DTS packages containing connection properties referecing server names that do not exist in the DR site. My only hope is to create an alias per named instance and see if this will work for both DTS and linked servers.
Again, does this make sense?
All this to save a few bucks
Thanks and have a great New Year.
Dave
-
What may be simpler is to script out the logins/passwords (encrypted) on a regular basis (make sure you get the SIDs correct, too, for those SQL Server logins). In that way you're only having to restore the user databases. Well, I'm leaving out DTS and SQL Agent jobs... I'll get to those. That's one of the approaches we've looked at to make the job easier. If the scripting gets done periodically you've eliminated the issue with system databases in the wrong places.
Yes, using aliases you could eliminate a lot of the referencing issues. If you had a server that was MyServer7 and it's not MyDRServer\Instance7, if you know the port (and set it statically), creating an alias of MyServer7 pointing to MyDRServer, port for Instance7 and you're set.
That leaves DTS... which if you're exporting to files in order to have an additional copy (I think Brian Knight has a recommendation to do this every so often as a just in case step), you can reload the DTS packages. Failing that, if you have a temporary SQL Server which you can restore msdb to and then save off the DTS packages to your instance, you can get them back up and running that way, although that's a very manual way of doing it.
K. Brian Kelley
@kbriankelley -
Brian,
In regards to saving the DTS packages to files, is there a way to automate this process to ensure every time a package is created or modified the package is then saved to a file?
I don't recall any way to automate the process.
Thanks, Dave
-
I would choose having all the databases on one default instance to reduce resource contention. It's easier than using "WITH MOVE" etc for restore or messing with SQL startup parameters.
It does make it harder to maintain logins etc - you are consolidaiting several servers' worth of logins, linked servers, DTS packages, SQL Agent jobs etc onto one box.
It also makes the client conenction easier. Set up some CNAME DNS entries all pointing to the DR SQL box and the apps simply connected using a DNS lookup to an IP address, port 1433... no client alias or client app changes at all.
The DNS workaround also fixes DTS package issues...
Can you consolidate this way? Is it an option?
-
Not that I am aware of. However, this gets into a question of change control. If something is changing in production, especially when you're talking about ETL type of stuff, there probably needs to be a business process wrappering it... so long as that is followed, it would catch changes and you can do the exports accordingly.
K. Brian Kelley
@kbriankelley -
Unfortunately change control is something that needs to be improved upon at this organization. I've only been here for a few months and the lack of change control became apparent immediately. Implementing change control procedures is a goal of mine for this year, however it will take a great deal of time to fully employ. Currently the developers create, modify and migrate DTS packages without my knowledge or consent, and I being the only DBA supporting nearly 40 production servers. Making sure they save DTS packages to files for DR purposes may be a lost cause without a complete and enforced changed control process.
Dave
-
A couple things I have done in the past, to help with similar numbers and DR requirements...
Move all DTS to one server...I had one SQL instance that was devoted to scheduling and DTS. This gave me a much simpler back up means for DTS and Agent jobs, as well as giving me one place to change aliases if I needed to.
Create a 'datalist' for your servers...While not my idea originally, this is a good way to have one place to change your connection information in one spot. On the scheduling server mentioned above, I had a table with the connection information for every server instance. Then in each DTS package, simply query the table for the server info you need and put in the new connection information. It may actually work for you to put the info into a text file instead. There are many options here, but maybe this gives you a thought that will help in your situation.
Using instances...I offer this thought up with hesitation, as my remembrance of the details are sketchy, sorry. I encountered a problem with recovering a default instance into a named instance. To counter this, I shifted all of our servers into instances, allowing me to fully have the DR site up and running with the same instance names for testing and so on. In my situation, the IT Execs liked this change as it added a level of complexity to protect from network attacks.
Hope some of this helps.
-
Thanks for the feedback. Centralizing DTS package connection property data in one location such as an INI file is something I've seen recommended by other people. This would take some time to accomplish in our environment given they have several hundred packages that would need to be changed. It is something I plan on recommending as a standard going forward. I believe the process is described on SQLDTS.com. I'll look for it again.
Dave
-
You may be able to run a job which queries the appropriate tables in msdb on a nightly basis and then compares the results with the previous night's run. If there's a difference it sends you an email. That shouldn't be too difficult to implement. At least it would let you catch a change after the fact.
K. Brian Kelley
@kbriankelley
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply