0

I have Windows servers that are running SQL Server 200(5/8) in many remote locations on the east coast. At our corporate office, we are trying to set up a reporting server that can get as real-time as possible.

I am looking for some guidance on how to accomplish a set up to where all of my remote machines are replicated to one master server that software can query.

Is this possible?

EDIT: My master SQL Server is running SQL Server 2008 R2 standard.

4
  • Is this SQL Server Standard or Enterprise? Your available options will depend a lot on that.
    – kenchilada
    May 1, 2013 at 20:03
  • I am using Standard.
    – etm124
    May 1, 2013 at 20:05
  • What do you mean "a set up to where all of my remote machines are replicated to one master server"? Do you mean that you want to replicate the remote databases so that your reporting server can report against those databases?
    – joeqwerty
    May 2, 2013 at 0:57
  • @joeqwerty, yes. I want those remote databases to replicate to one reporting database.
    – etm124
    May 2, 2013 at 11:36

1 Answer 1

0

There are a few ways that you can accomplish this. The most straightforward options would be as follows:

Replication - Consists of one or more servers in a publisher-subscriber model. Transactional replication can copy and distribute data from one database to one or more other databases. In your situation you could setup a publication for each of the remote servers which will replicated particular data that you need for reporting to the specified database at your corporate server. You could also use merge replication if you wanted to merge all of the data into a single database (but this would require that the databases all have the same schema).

Database mirroring - Consists of a principal and mirrored server which would apply every transaction from the principal server (i.e. the remote server) to the mirrored server (i.e. the corporate server). The mirror database is inaccessible to clients; however; you can create a database snapshot at the mirror server which can be used for reporting. This snapshot will not contain real-time data as it will only have data as recent as the snapshot.

Both of these options are configured per database - this means that if you have 10 different databases at the remote locations that you want to replicate/mirror you will need to create 10 corresponding databases at the corporate office.

Replication lets you customize which data you will replicate while database mirroring moves all of the date. Since you're wanting near real-time data I have a feeling that the best option is going to be transactional replication.

These pros/cons are outlined further in the following articles from Books Online:

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .