Mirroring a Database
-
I'll start off by saying that I am not a DBA, so if this is a really basic question, then I apologize. But, here is the issue we're having:
We have a very large SQL 2000 database that runs on an internal server (it's behind a firewall, and IT does not want to open it to any outside incoming connections). This presents a bit of a challenge because our new web reporting application needs access to this database, and our production servers are on a completely different network. So, we need a way to mirror this database between an internal server and our production database server (which is running SQL 2005). If possible, we do not want to copy the entire database, as just a fraction of the tables are needed for our reports.
At present, we have a DTS package that runs nightly to copy the data between servers. The problem is the reports are always a day behind, and it takes nearly an hour and a half to copy all of the data (and the amount of time increases by the day). We're trying to find some tool/utility that is capable of mirroring specific tables from a database across to two servers, and one that does so incrementally rather than doing a complete restore of all the data any time it gets updated (which is what our DTS package currently does). I'm just wondering if anyone had any recommendations for such a utility?
-
How are you expecting to mirror the data if you aren't allowed to access it in the first place? This makes no sense. If you can do nightly dumps and do replication then why can't you just query the DB directly?
-
Can't IT open the firewall to the one IP address (the reports server) for the one kind of connection? Also, given that the amount of time increases each day for the data upload indicates to me that this "replication/mirroring" process is one-way. The source database has no way to know what data the other database has, so it sends all of it, which would explain how it is able to get through the firewall.
-
We'll short of setting up trans log shipping (which may not work for SQL 2000 and SQL 2005 combined), or upgrading the SQL 2000 box to 2005 so you can setup database mirroring; I'd look at using SSIS on your SQL 2005 box to pull the data incrementally instead a full reload.
It takes a little bit to learn all the ins and outs and get good performance, but the SCD (Slowly Changing Dimension) would be a good place to start. Essentially you can set it up to do a Merge (Insert on new, update on existing). If you have any modified/created timestamps on the records on the source system, that will help the pulls significantly.
The other option is to get RedGate's DataCompare which can detect and synchronize the databases. It can be used in a command line mode, but I haven't done it yet.
-
You could setup a replication between the Databases.
You could satisfy the IT by placing the publisher behind your firewall. You can then "publish no changes" to the outside db, and recieve all altered records from your live db.
It sounds odd, since the publisher would be the client, but if the IT wont budge it works perfectly...
-
@morbiuswilters said:
How are you expecting to mirror the data if you aren't allowed to access it in the first place? This makes no sense. If you can do nightly dumps and do replication then why can't you just query the DB directly?
Mirroring could be done from the other side. Push instead of Pull.
-
@ammoQ said:
@morbiuswilters said:
How are you expecting to mirror the data if you aren't allowed to access it in the first place? This makes no sense. If you can do nightly dumps and do replication then why can't you just query the DB directly?
Mirroring could be done from the other side. Push instead of Pull.
If IT would allow that they should just allow the web server to connect to the DB, which was the entire point of my reply.
