SQL Server Performance

Quick way to roll back from SQL 2k5 to SQL 2k

Discussion in 'SQL Server 2005 General DBA Questions' started by WingSzeto, Apr 13, 2007.

  1. WingSzeto Member

    I am preparing an upgrade from SQL 2K to SQL 2K5 and like to know if there is quick way to bring the user databases back to the SQL 2K version if SQL 2K5 doesn't work efficently after go live. As far as I know, once the SQL server version is upgraded to the SQL 2K5 version, the user databases can't be restored back to a SQL 2K version. Since the backup and restore is usually the quickest way but it won't work in this case, DTS (or integration service) may be too slow, what other options do I have? Or Am I correct in the above statements?

    wingman
  2. ndinakar Member

    You could try replication from SQL 2005 box to a SQL 2000 box.


    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  3. WingSzeto Member

    But is it true that the indentity value can't be replicated?


    quote:Originally posted by ndinakar

    You could try replication from SQL 2005 box to a SQL 2000 box.


    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  4. MohammedU New Member

    Unfortunately, this is not as easy. Backed-up databases from SQL Server 2005 instances cannot be restored in SQL Server 2000 instances. Nor can detached SQL Server 2005 databases be reattached to SQL Server 2000.

    http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149585_tax301536,00.html



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  5. ghemant Moderator

  6. ndinakar Member

    quote:Originally posted by WingSzeto

    But is it true that the indentity value can't be replicated?


    quote:Originally posted by ndinakar

    You could try replication from SQL 2005 box to a SQL 2000 box.


    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/

    No we have successfully replicated a 2005 DB to a 2000 box as a back up for almost a month before we pulled the 2k box out. No issues with Identity columns. Make sure to set the 'Not for Replication' for the identity column property (you can see it in table design window)

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  7. MohammedU New Member

    You can do one way transactional replication but your tables have to have PK to participate in transactional replication otherwise you have to replication procedure executions.

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  8. satya Moderator

    Complete database backups are only the options to backout such changes, if you are really worried about such issues why don't you test the application on full scale to ensure no issues are expected. Without the testing it is really hard for you to make sure it works or not.

    See thishttp://sqlserver-qa.net/blogs/tools...-upgrades-of-your-sql-server-environment.aspx fyi.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  9. WingSzeto Member

    Is there a quick way (a script available) to do the following:

    1. Find out which primary key is not set for 'not for replication'
    2. set the primary key of all the tables to be 'not for replication'


    quote:Originally posted by ndinakar


    quote:Originally posted by WingSzeto

    But is it true that the indentity value can't be replicated?


    quote:Originally posted by ndinakar

    You could try replication from SQL 2005 box to a SQL 2000 box.


    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/

    No we have successfully replicated a 2005 DB to a 2000 box as a back up for almost a month before we pulled the 2k box out. No issues with Identity columns. Make sure to set the 'Not for Replication' for the identity column property (you can see it in table design window)

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  10. ndinakar Member

    yes, you can use the CnstIsNotRepl property to check that. check out BOL on how to use it. And to set the property you can do an ALTER TABLE. You need to have sp4 on SQL 2000 for that.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  11. MohammedU New Member

    select table_name, constraint_name
    from information_schema.table_constraints
    where constraint_type = 'primary key'
    and objectproperty(object_id(constraint_name),'cnstisnotrepl') = 0

    For second question I think you have to use ALTER TABLE...command..

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  12. ndinakar Member

    Mohammed

    I think you should use COLUMNPROPERTY not OBJECTPROPERTY since 'cnstisnotrepl' is for column.



    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  13. WingSzeto Member

    I have the following questions:

    1. Under SQL 2K and SQL 2K5 replication, the primary key and identity features are removed in the subscriber database. Is it that's why we need to set the 'Not for replication' to 'Yes'? By doing so, the primary key and indentity feature retain on each table in the subscriber db during replication?

    2. I should only need to make all the primary keys that are using Indentity 'not for replication'. All other primiary keys I shouldn't need to apply 'not for replication', right?

    3. Say I have done all that and a SQL 2k5 user db is replicating to a SQL 2K db on a seperate server. Say something bad happened and I need to go back to SQL 2K, Can I just shut down the SQL 2K5 replication and maybe even turn off the publisher server and have users use the replicated SQL 2K db as the live db? Obviously I may need to deal with a different db name and need to make sure logins are available, are there any other issues I may need to consider?

    4. I have quite a few check contraints, foreign key contraints and triggers are set for 'Not for replication' in the publisher db and they don't exist in the replicated database. If I use the replicated database as the live server, will I need to recreate them before I turn the db to a live one, right? Or If I want to save that step, I can turn on 'for replication' for them in the publisher db ahead.


    quote:Originally posted by ndinakar

    Mohammed

    I think you should use COLUMNPROPERTY not OBJECTPROPERTY since 'cnstisnotrepl' is for column.



    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  14. MohammedU New Member

    If you don't want to replicate the PK you need to use NOT FOR REPLICATION...
    If you want to replicate constraints like PK you have to enable the option in Article properties...

    Replication is one option but it is very painful when you do it using transactional replication...but you can try snapshot which will take time and its depends on the size of the db...

    After how many days you want to go back? why can't you change the compatibility to 80 and run it...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  15. WingSzeto Member

    Ok, I can see how 'not for replication' works now. Will it be a chance that the same table in the publishing db and subscriber db may not have the same identity value during the insert and subsequently replicated insert to the subscribing db? I need them to be the same in order to make the replicated db as a production db in case of serious problem.


    quote:Originally posted by ndinakar


    quote:Originally posted by WingSzeto

    But is it true that the indentity value can't be replicated?


    quote:Originally posted by ndinakar

    You could try replication from SQL 2005 box to a SQL 2000 box.


    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/

    No we have successfully replicated a 2005 DB to a 2000 box as a back up for almost a month before we pulled the 2k box out. No issues with Identity columns. Make sure to set the 'Not for Replication' for the identity column property (you can see it in table design window)

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  16. ndinakar Member

    If you are using the NOT FOR REPLICATION option, the id generated at the publisher will be propagated to the subscriber DB. Unless you are doing INSERTS at the subscriber end, the Id's should match.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  17. WingSzeto Member

    I see you point and we are not doing any insert into the subscriber db's tables. Just for my clarification, if the table in the subscriber db has its identity field and it is on, the incremented value of the identity field should be based on its own increment not from the publisher, right?

    for example, TableA is in both publisher db and subscribe db and both table should have idenity value at say 1000. For whatever reason, tableA in subscriber db is at 1001. If someone insert a new record in table A in the publisher, the identity field value for table A in publisher would be 1001 and the value in TableA in subscriber db woudl be 1002. Right? Or are you saying the tableA in subscriber db will still be 1001 no matter what identity value it has?





    quote:Originally posted by ndinakar

    If you are using the NOT FOR REPLICATION option, the id generated at the publisher will be propagated to the subscriber DB. Unless you are doing INSERTS at the subscriber end, the Id's should match.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  18. ndinakar Member

    quote:Originally posted by WingSzeto

    I see you point and we are not doing any insert into the subscriber db's tables. Just for my clarification, if the table in the subscriber db has its identity field and it is on, the incremented value of the identity field should be based on its own increment not from the publisher, right?

    for example, TableA is in both publisher db and subscribe db and both table should have idenity value at say 1000. For whatever reason, tableA in subscriber db is at 1001. If someone insert a new record in table A in the publisher, the identity field value for table A in publisher would be 1001 and the value in TableA in subscriber db woudl be 1002. Right? Or are you saying the tableA in subscriber db will still be 1001 no matter what identity value it has?


    No, the ID comes from the publisher like this:


    SET IDENTITY_INSERT <table> ON
    INSERT INTO <table>
    Values <....>
    SET IDENTITY_INSERT <table> OFF

    So whatever ID was generated at the publisher will be the same at the subscriber too.
    Replication simply is like copy/paste. so nothing new happens at subscriber end.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/

Share This Page