July 31, 2009 at 4:01 pm
I have a 100GB database on SQL 2000 that we have been log shipping to another SQL 2000 database host for reporting purposes. Users are accustomed to having the data be read only on the secondary server and updated twice a day. The parent database schema is not owned by us; it is part of a third-party application and we may not modify the schema in any way, including adding primary keys. The secondary host is now being upgraded to SQL 2008, shortly.
There are about 1400 total tables, about 150 of which are actually used in a meaningful way. About 146 of those have primary keys. We have no idea when the parent company that owns this application is going to begin to support SQL 2005 or SQL 2008--an upgrade is not on the 6-month horizon.
I would prefer to have a full copy of the database on the secondary server, but what I will take is all 150 of the tables that are used.
I have explored log shipping, which is what we currently use. It seems that with the full deprecation of maintenance plans, there may be no way to use log shipping between SQL 2000 and SQL 2008, although both have this feature. I would strongly prefer to keep doing this as I think it useful to have a full copy of the database on hand. But I so far have not seen anything in my research that would really lead me to believe this is possible.
I have not full explored snapshot replication--my guess is that with the database being 100GB, this is going to be prohibitive to do twice a day in speed. The downtime associated presently with log shipping is about 30m to 60m each time. It can't become worse than that.
I think our best option may be to do transactional replication, and the best explanation for this I have seen explained is in this link: http://www.vinodunny.com/blog/post/Mixed-Mode-Bi-Directional-Transactional-Replication-between-SQL-2000-and-SQL-2008.aspx
I don't think I can use the wizards in either SQL 2000 tools or SQL 2008 tools to set this up--I think it would have to be done completely by hand in the manner described in the link.
Is there something I have overlooked? Anyone doing something like this already and can offer some useful advice?
July 31, 2009 at 4:22 pm
Using SQL Server 2000 as the source you pretty much only have one option, Log shipping. Using replication under SQL 2000 will add a column to all the tables which you said you can't modify.
A problem that you will run into is that your users aren't going to be able to read the database on the SQL 2008 system. When you restore logs from a SQL 2000 server to a SQL 2008 server the database isn't readable until you put it into read/write mode because the database has to be upgraded.
The only way to keep your system working as is will be to bring up another SQL 2000 server for the reporting, or keep the existing reporting server as SQL 2000.
I tried doing this exact same thing from SQL 2000 to SQL 2005 and it didn't go well. I ended up not having a reporting database for the few days between the reporting server upgrade and the production server upgrade.
July 31, 2009 at 9:03 pm
shani.gentry-cincotti (7/31/2009)
I would prefer to have a full copy of the database on the secondary server, but what I will take is all 150 of the tables that are used.
If both servers are on a SAN, this may be a simple cake walk. Many SANS have a snapshot capability that will allow you to completely "copy" a very large server almost as often as you wish in scant seconds. The target server will be out of service when you do that but, like I said, that will last only seconds. It's become a time honored method for keeping a reporting server up to date by many DBA's. The system the DBA's set up at my old company had a Tera-Byte of data that got "copied". It took something like 17 seconds to complete.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2009 at 9:34 pm
If you have a SAN, and you have the required software to handle it (some vendors charge an extra fee to do this automatically, or you can usually do it it through scripts).
What sort of storage do the servers have?
August 2, 2009 at 5:30 pm
Both servers do have their storage on a SAN, though I have very little interaction with it and don't know any of its features.
Since there's a lot of data exchange between the other databases on the host and the two that are shipped from the other server, there's been a lot of effort spent to keep them on the same host/instance. It would also require a huge amount of code rewrite if any database in this configuration changed instances.
Let's imagine for a moment that the reporting server was only being upgraded to SQL 2005. Do things get any easier? It's possible that we could upgrade to SQL 2005 now and then to SQL 2008 in a few months once the upgrade path for that application was more completely spelled out. I don't think they can stay on SQL 2000 indefinitely...though they certainly seem to be trying to.
August 2, 2009 at 5:58 pm
No, things don't get any easier if the database is SQL 2005 instead of SQL 2008.
As you have SAN storage, work with your SAN Admins and see what snapshot features your SAN supports. Assuming that it supports some sort of Snapshotting you can do something like this.
On the production server if the database is not on its own hard drive (or set of hard drives) then present new drives to the host from the SAN and move this database to these hard drive. After you have move the database to this drive or drives have your SAN admin setup and schedule Snapshots from that drive or drives and configure the snapshot to be mounted to the target machine.
You'll need to be able to either run a SQL Script on the target server before the database snap is mounted to detach the reporting database, then attach it at the end of the process. Depending on the replication technology used it may handle this for you (for example Replication Manager used with EMC SANs will handle all this by telling the SQL Server that the database has been restored). If you have to detach the database, you'll then need to attach the database to the instance.
Now the upside do doing it this way, is that you have the option of upgrading the reporting database, because when you attach the snapshot you'll be attaching the full database each time so the versions can probably be different (you'll need to check with your SAN vendor to be sure what they support here) as if you are doing the detach, then attach on the reporting server when you attach the database it'll simply upgrade it when it attaches it, and the database will be placed into read/write mode.
August 3, 2009 at 3:37 am
Using SAN snapshotting may be your best way, but there is another approach that would work.
You could keep your reporting server reasonably up to date by shipping database backups. Start each day with a full backup, then ship a differential backup every few hours to meet your data concurrency SLA.
Your reporting database would not be available while the restore operation was running, but afterwards users would have full access.
If you can get the tables you absolutely must have for reporting into a dedicated filegroup or filegroups, then you can use third-party backup tools to do a filegroup backup and restore. This would reduce the time needed for shipping and for restore.
The biggest problem with shipping full backups is that the bigger the database, the more time you will spend in shipping the backup and doing the restore. There will come a point where downtime for the restore exceeds your SLA for database availability.
But if I was given a choice, I would use a SAN snapshot.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 3, 2009 at 12:20 pm
We killed the idea of shipping database backups early on in this thread as shipping diffs or transaction logs requires that the database servers both be the same version.
August 3, 2009 at 12:22 pm
Thanks to everyone who offered an opinion. I'm working with our services partner to see if we can script a solution that will get us through until we know where the primary server is going.
August 14, 2009 at 9:15 pm
What we ended up doing was planning to do a combination of transactional replication for tables with primary keys, and snapshot replication for tables without them, but the final list got pared down far enough to only include tables with primary keys--so transactional it is.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply