Post reply

SQL 2000 v 2005 replication

  • June 16, 2006 at 4:24 am

    #173065

    Folks first post and am non technical in terms of SQL server, so please be gentle

    I'm involved in a project where we  will have to datacentres running SQL server on server 2003 dual processor box's.

    1 data centre will hold the live external data the other the live internal data. Then the other way around for DR copies.

    My question is this.

    I'm being told that SQL 2000 replication is flakey and difficult to support. and that to achive what I want I'd need enterprise licences on both box's ( both dual processor ) so that cost will be imense. ( £ 30 k plus )

    I'm being recommended to go to SQL 2005 which I'm told has stable easy to manage replication and doesn't need enterprise. ( about £7 k )

    However ( yes there is always one ) the application using the database does not support SQL 2005 until early 07.

    Am I being advised correctly regarding SQL 2000?

     

    Thanking you in advance for you help.

  • June 16, 2006 at 7:17 am

    #644247

    It really depends.  SQL 2005 certainly has some nicer features such as mirroring, etc.

    But in SQL 2000 you could do log shipping.  The wizard for this only comes with 2000 Enterprise but you can easily roll your own - there are many scripts & examples on this site for log shipping.  Essentially you perform transaction log backups of your production DB every X minutes and immediately restore them on the backup server.  This way the DBs are kept reasonably in sync.  This is the easiest and least technical approach - there are also no limitations on what you can do with your DB schemas, etc.

    Pure replication could be done via snapshot replication, transactional replication or merge replication.  In your environment the transactional replication would be best - look in SQL Server books online for an explanation.  Again though, this is not as easy as the log shipping.

    As far as SQL 2005 compatibility goes, you can still run your database on SQL 2005 and set its compatibility level to 80 (SQL 2000 - 90 is SQL 2005).  This may be a good compromise with the app vendor as some of the syntax that worked in 2000 but doesn't in 2005 will work with the compat level set to 80.  A good example of this is that SQL 2005 requires the WITH keyword for table query hints.  <rant>The annoying thing is that 2000 Enterprise manager scripts code using TABLOCKX as query hints without the WITH keyword! Found out that nasty surprise when upgrading a customer's DB when compat level was 90.</rant>  

    So if you can go to 2005, I would suggest it as the whole replication area has been significantly improved upon.  The new database mirroring, enabled with SP1 of 2005, is very useful for situations such as yours.  Also, you get the log shipping wizard in less expensive editions of SQL 2005 (eg Standard edition)

  • June 16, 2006 at 7:33 am

    #644259

    brilliant thanks very much in deed.

  • June 17, 2006 at 4:53 am

    #644450

    No problem!

  • June 20, 2006 at 12:48 am

    #644734

    Update for you.

    We opted to run SQL 2005 managing our SQL 2000 databases using 2005 mirroring to replicate the data as this is the least invasive, in terms of altering tables etc, and we'll forget to mention it to the supplier as they won't support this - shrugs.

  • June 20, 2006 at 7:45 am

    #644813

    Thanks for the update.  Nice to know what others in the real world are doing.  I agree - database mirroring (I haven't had a chance to use it yet) seems great because

    a) it's simple to understand - a single MS whitepaper explains how it works with no mystery

    b) there's no unexpected behaviour

    c) You do not need to change your database...

    Hope it works out for you.  If you run into problems, plz post them here.

    Cheers

  • June 20, 2006 at 11:59 am

    #644901

    Will do and thanks again for your help.

Viewing 7 posts - 1 through 6 (of 6 total)

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