April 6, 2011 at 9:58 am
Hi, I have been asked to provide some sort of disaster recovery for our payroll system which has databases running on SQL Server 2005. As I haven't looked at SQL Server since about 1998 things are a bit foggy!
I'd really appreciate some advice on the best solution and how to go about implementing it.
I have determined that all that is required is a daily copy of the databases to be copied from the primary SQL server to a secondary SQL server, and be accessible from there if required. Also obviously once the issue with the primary SQL server is resolved there will be a requirement to overwrite the existing databases on it with the newly updated ones on the from the secondary SQL server.
I have looked at mirroring and think that's a bit above what I need, and at replication but don't know if that is the right solution either (I have tried setting replication up but doesn't seem to want to work). I then looked at just copying the databases but wasn't sure if the secondary SQL server setup had to be identical to the first.
Basically any advice gratefully received and pointing me in the direction of any good reading material on the subject would also be of immense value.
Thanks,
Dave
April 6, 2011 at 10:03 am
If you your primary servers goes down and you restore the recent backup to the secondary, how does the application know to switch over to the secondary database server?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 6, 2011 at 10:07 am
Mirroring would be less overhead than what you are describing. Also, you may consider clustering. Another consideration to make is that you should work with the network team to have a process in place to change a DNS record for the database when it fails to be pointed to the new server.
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
April 6, 2011 at 11:06 am
The app has a connection screen at login where you can define the database to connect to
April 6, 2011 at 11:08 am
So the application does a network discovery or would the users have to know what the database server name is?
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
April 6, 2011 at 1:19 pm
It saves connections in a drop down so I can create a "live" and "backup" connection. I figured if the primary SQL server went down I can just tell them to connect using the "backup" connection
April 6, 2011 at 1:26 pm
Mirroring would move the connections automatically, though it does require a more regular connection between the systems, or a good log size managements strategy.
Clustering isn't likely what you need, though log shipping might work fine for you, especially since you have the drop down. As a quick, short implementation I might lean towards log shipping.
http://msdn.microsoft.com/en-US/library/ms190016%28v=SQL.90%29.aspx
I would agree replication is probably not what you want.
April 7, 2011 at 4:19 am
The databases recovery mode is set to simple so don't believe I can do this ?
April 7, 2011 at 4:58 am
davegoree (4/7/2011)
The databases recovery mode is set to simple so don't believe I can do this ?
that would be you re first action if you want something inplace concerning disaster recovery. Full recovery mode and a solid log backup strategy.
step 2 is deciding which kind of DR will suit you.
April 7, 2011 at 5:31 am
I use a well known 3rd party SQL Server backup product that mirrors backup files to the DR server.
These backups are then automatically restored to the DR SQL Server and also logins are synchronised from Live to DR too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2011 at 12:10 pm
If you keep the databases in simple mode, you can really only DR from the night before. That's OK, but really ask your payroll people if the db fails at 3:00pm, are they going to be OK with re-doing all work since the overnight backup?
If not, look at moving to full mode, setting up and managing log backups, and then using mirroring or log shipping.
May 3, 2011 at 2:09 pm
According to me Log Shipping is the best way to support you.
May 3, 2011 at 2:24 pm
To all, thankyou for taking the time to reply to my question.
I solved this by implementing scheduled backups that were
then copied to the 2nd server and restored there.
Works really well and payroll dept are happy
May 3, 2011 at 2:28 pm
Log shipping will also do the same job automatically, it will take transactional logs backup from primary server and will restore it to secondary server (Standby mode).
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply