Is there a better way?

Question

Is there a better way?

shaun.stuart

SSCertifiable

Points: 7067
More actions
June 28, 2010 at 12:14 pm

#234429

I have a 9GB database that is housed on one server and replicated to a server in another city via transactional replication. Both the publisher and the subscriber are running SQL 2000. The subscriber recently has had hardware issues and, for a variety of reasons, we are replacing the subscriber hardware and, at the same time, installing SQL 2008. This means we will no longer be able to use replication, as a 2000 publisher cannot have a 2008 subscriber.
What other methods exist for getting a copy of the data from the 2000 machine to the new 2008 machine? The data can be up to a day old. The database on the 2008 needs to be available during the day for reporting purposes, so I believe this rules out both log shipping and database mirroring.
The only thing I can come up with is to automate a backup of the source database on the 2000 server, transfer it to the 2008 server, and restore it there. Because we do not have a super fast connection between the two sites, the backup will need to be compressed. 7-Zip will compress the backup to about 1 GB and I think that size will transfer overnight.
I'm just trying to see if there are other, possibly more elegant, methods I might be missing. Any ideas?
Thanks!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

wouldn't log shipping be an option? a zip of the log file of changes would be much smaller than the 9 gig backup, zipped or not, and your bandwidth would affect it less, i would think.

your reporting server would have to be read only, i think, but that's perfect for a reporting database.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 2

There are a couple things I can think of.

Log shipping will work, but you need an automated method of ensuring the restores are only done at night. Not sure if you can alter the secondary jobs to only run at night, but that might be the easiest.

Apart from that, you could set up a second 2008 instance near the source, log ship to that, and then continue with your replication to the secondary. The replication/log ship restores might collide sometimes, but replication should pick up when the db gets restored as a standby.

shaun.stuart SSCertifiable Points: 7067 More actions · Answer 3

Thanks for the replies.

Lowell - I've not used log shipping before, so maybe I am not clear on the details. I thought log shipping required the WITH STANDBY option of the log restore, which meant the database is not available to any users. If the database is available, but in read only mode, that would work.

Steve - I'm pretty sure we don't have the budget for another SQL server machine and license to set up near the source 🙂

Shaun

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 4

Typically a DR machine, which an in between instance might qualify for, is not charged as a license. Check with MS on your situation.

You can log ship as STANDBY (allow read only access) or NORECOVERY (no access)

shaun.stuart SSCertifiable Points: 7067 More actions · Answer 5

This isn't a DR machine though. It's being used for reporting purposes and exists solely because of our slow data link, so that people at one site can run reports without getting timeouts when querying the source server. The server is also being used for other purposes.

george sibbald SSC Guru Points: 104210 More actions · Answer 6

if you are logshipping from SQL 2000 to SQL2008 and you want the SQL2008 database available you won't be able to do it.

If you use the with standby option to make the database readable SQL will try to upgrade the database and fail.

The 2008 database would have to be in norecovery mode and therefore unavailable.

---------------------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 7

I was thinking

Server A - SQL 2000 (licensed)

Server B - SQL 2008 (unlicensed) - log ship secondary

Server C - SQL 2008 - new server for reporting

However, as George mentioned, the reading part will upgrade the db and fail. I hadn't tested this, but was thinking about it.

Your best bet might be some SSIS/DTS extract if you can pull it off. Otherwise, is it worth leaving the new server as SQL 2000?

george sibbald SSC Guru Points: 104210 More actions · Answer 8

There are plenty of backup compression tools out there you could use, they are quite cheap. hopefully you would just have to license the source server and restore only license is free.

You could then do a compressed full backup nightly and restore it over the network.

As this is a tactical one off solution I would go for hyperbac for its ease of install and integration into your normal backup procedures.

---------------------------------------------------------------------

shaun.stuart SSCertifiable Points: 7067 More actions · Answer 9

Thanks George.. Good point about the 2008 conversion. I was developing a test today and probably would have run into that problem 🙂

I may have to go the third party backup software route. I may also have to go back to my original plan...

happycat59 One Orange Chip Points: 29389 More actions · Answer 10

Are you sure that you can publish data from a SQL 2000 instance to a SQL 2008 instance ?

I haven't got a system with me to test with but the restriction relating to replicating between versions is usually that the distributor must be at the >= the highest version.

How about creating a small test (perhaps a small table or something from pubs or adventureworks) - create a SQL 2008 distributor and publish from SQL 2000 via that distributor to SQL 2008.

shaun.stuart SSCertifiable Points: 7067 More actions · Answer 11

Thank you happycat59!!! I don't know if I read it somewhere or just figured it must be true, but for some reason, I had it in my head that for replication, the publisher must always be at an equal or higher SQL version that the subscriber. Your post made me verify that assumption and it turns out to not be true!!

http://msdn.microsoft.com/en-us/library/ms143241.aspx states:

"For all types of replication, the Distributor version must be no earlier than the Publisher version. (Frequently, the Distributor is the same instance as the Publisher.)

For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example, a SQL Server 2000 Publisher can have SQL Server 2008 Subscribers, and a SQL Server 2008 Publisher can have SQL Server 2000 Subscribers. "

In my case, the publisher and distributor are the same machine, a sql 2000 box. The subscriber is 2008. I just set up a test case and am able to successfully replicate with a 2000 publisher/distributor and a 2008 subscriber!

One thing to note - the publication can be made on the publisher, but the push subscription must be made on the subscriber (though it will get created and reside on the publisher). This is because you cannot use Enterprise Manager to connect to a 2008 instance, so you therefore cannot specify the subscriber machine from the publisher.

Thanks to everyone for your help!

noeld SSC Guru Points: 96590 More actions · Answer 12

This means we will no longer be able to use replication, as a 2000 publisher cannot have a 2008 subscriber.

That's false.

You can!

See : Using Multiple Versions of SQL Server in a Replication Topology

Relevant Part:

SQL Server 2000 and SQL Server 2005 can both participate in replication topologies with SQL Server 2008. For SQL Server 2000 the minimum version is Service Pack 3 (SP3). For SQL Server 2005 the minimum version is Service Pack 2 (SP2).

* Noel

Arie Jones-435881 SSC Journeyman Points: 85 More actions · Answer 13

I am not quite sure that your statement is correct that a SQL Server 2000 publisher cannot have a 2008 subscriber. You may not be able to set it up through the wizard but could certainly do it with code I would have to think. What my recommendation would probably be would be to set up the distributor on the SQL Server 2008 box. It would then just have to read the log file from the 2000 box for the transactions.

What you want is to create a preinitialized subscription. Right now we have set up a replication strategy for a client that replicates across 3 separate servers... in three different locations ....on three different flavors of SQL Server ( Publisher =2000, Distributor =2005, Subscriber=2008). We have this set up to do cached updates to the subscriber twice nightly and it has worked perfectly for over 3 years...through at least 1 server move of the subscriber and then a subsequent upgrade. That's at a clip of about a million transactions per night.

What you have to understand is that once the subscriber is in place with the distributor ....the distributor only really cares about 2 things....

1. Can I read the log file to queue up these transactions

2. Can I run a bunch of stored procedures against the subscriber to bring the data up to date.

I have used this kind of setup in several presentations previously to show people how you can do all kinds of ridiculous things...like not allowing updates/deletes to a specific table or firing off some kind of custom logging on the subscriber end.

Hopefully this helps you out. Hit me up if you need some of the code to set up the preinitialized subscriber.

Cheers,
AJ

Arie Jones
http://www.programmersedge.com
http://www.sqlsherpa.com