0

I am pretty green to the administration end of databases, but I was hoping you guys could direct me on the right path.

I have Server 1, 2, and 3. They both identical versions of SQL Server, with an application. Within each instance of MS SQL Server, I have roughly 10 databases in each server. My goal is to replicate/mirror the data to a Server 4 so that reporting and alerts can be run off of the 'live' data without effective performance of the application running on Servers 1,2,3.

To be honest, I don't even know where to start:

Is this at all possible?

Is this a common practice?

Is there any type of recommended reading material?

1

What you are looking for is called Transactional Replication, which is very commonly used and a good solution for avoiding contention on a transactional database. There are three databases in a transactional replication system, which are the publisher, the distributor and the subscriber. The publisher is the source database, the distributor is where all changes to the publisher are sent for distribution to one or more subscribers. The distribution database can be on the same server as the publisher or a different server. When you create a publication, you choose the articles to be included, such as tables, views, procedures, etc, and whenever a change is made to any of the objects and gets recorded in the transaction log, an agent, which is an executable, reads the transaction log, packages the change and sends it to the distribution database. Then a separate agent, or executable, takes the packages from the distribution database and applies them to the subscriber.

You will be setting up a publication and subscriber on servers 1,2 and 3 and have all subscriptions point to a database on server 4. There are several articles on MSDN that have good descriptions of transactional replication.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.