1.) Create a link between the two machines so that it is no longer necessary for me to copy the LDF and MDF files.
2.) If one database gets an update it should automatically update the other one as well.
How do I do this?
From your question it sounds like you're copying the MDF and LDF files from your primary database to your backup database. I'd guess that you're doing this on a nightly basis. You can achieve similar or better functionality using these approaches:
|Restore a nightly backup||Overnight|
|Log shipping||5 to 60 minutes|
|Transactional replication||Very quick. A couple minutes|
By latency, I mean the time difference between when an update is posted to the primary database until it is posted to the backup database. The less latency the more similar the two databases are.
The first approach, restore a nightly backup, is similar to the database file copy that you use now. By switching to restoring a backup you can avoid taking SQL Server down.
The second approach, log shipping, depends on making transaction log backup files from your primary database at frequent intervals and then applying them to the backup database. Log shipping is part of the SQL Server 2000 Enterprise Edition. However, it can be achieved in Standard edition through some script writing.
Transactional replication pumps updates from the primary database to the backup database at a very high frequency. It can keep the backup database almost in synchronization with the primary database. However, it uses the most resources.
Dig Deeper on Microsoft SQL Server Installation
Related Q&A from Andrew Novick
Can't decide whether to use nullable fields in a table. Development expert Andrew Novick discusses what to do. Continue Reading
If you've had trouble connecting Visual Basic to SQL Server, let expert Andrew Novick offer some basic advice to get you started. Continue Reading
Development expert Andrew Novick gives you the resources to improve your stored procedure writing skills. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.