• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

What is necessary to implement a SQL Server Redundancy Solution?

I would like to set up a complete redundancy solution for a business database our company uses. This will include new server hardware and SQL
server replication between the two servers.

I would like to move the current live environment to a new server, and use the current as the redundant back up. I would also like SQL server
replication of the database between the two, to ensure the redundant  server is completely up to date with the live one.

Can this be set up within SQL Server regardless of the database, or do the database tables have to have CLSID columns for example or any other changes? If it is possible, what needs to be done to make this happen?

I would also like to know if it will be possible to set up the clients to use a live server, but have a simple option of selecting the redundant one
if necessary. If so, how is it done?
  • 3
  • 2
1 Solution
Any database can be made to publish for replication, SQL Server will add the GUID column's as required and you set the rest of the options such as identity ranges

As for clients swithing to the redundant server do something like ->

If Not Connection.Open Then
      Connection.ConnectString = "Redundant Server Con String"
End If
purplesoupAuthor Commented:
Interesting - unfortunately neither of these are realistic options. Contractually we can't change the schema of the database we are using, so we can't add GUID columns, and we can't get to the source code of the application, so we can't add the code to switch the connection if the first one fails.
If you can't change the schema forget Replication. You need to add fields, tables, views and another database for distribution. Try another method such as Log Shipping or backup/restoring on schedule.

If the application uses a DSN to connect to the database then you are in luck because to switch to another SQL Server you just change the DSN. Find were the connection string is stored - it is rarely in the application - check the registry, an ini file, a config file.

If it is Access then you change the connection property under the file menu (for an ADP project) or the Linked table manager for an MDE/MDB
purplesoupAuthor Commented:
I thought you only needed to add GUID columns if you were running two-way replication - isn't this scenario just a one-way real-time replication where the transactions from one database are just automatically applied to the backup. Why do you need fields, tables and views to apply a transaction twice?

I'm still not clear how you automatically change DSN if one database goes down? Do you just mean the users have to know the DSN to the backup database and manually change it? It doesn't sound a very smooth way to handle a database going down.

Can you explain more about log shipping - how does that work?
You will need two way replication because if the primary server goes down and users operate on the backup you will need a method to get the data back to the primary server ni real time (or close enough) when it recovers. The difference between log shipping and replication is that log shipping will create a read-only copy. That is if your users change add or delete data on the backup server it will not be reflected on the primary server.

The fields, the tables and the views all get created to aid replication - the schema is significantly different once you setup a database to publish. Replication provides more complete redundancy in my opinion and enables transparent operation for users.

Redundancy can go on for ever depending on what you want to spend and the time you have. My prefered method is to install MSDE on every client, and set it up for merge replication to 2 backend servers (with options set to use server2 if server1 fails) no DSN changes needed if the database is sitting on the local machine and replication will deal with failing over for synchronisation

As for the DSN since you can not chaneg the app source you can always craft a tiny exe that will do the swap for them - then the end user can click the exe and the DSN will be acurate, then just restart the app.

as for log shipping as per books online
In Microsoft® SQL Server™ 2000 Enterprise Edition, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database. This allows you to have a backup server and also provides a way to offload query processing from the main computer (the source server) to read-only destination servers.


Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now