asked on

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?

andrewbleakley

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"
Connection.Open
End If

purplesoup

ASKER

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.

andrewbleakley

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

purplesoup

ASKER

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?

ASKER CERTIFIED SOLUTION

andrewbleakley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial