locked
SQL 2008 to SQL 2005 issues RRS feed

  • Question

  • Hello,

    I have a rather odd issue that I really need some guidance on. My company has the traditional 3 database server structure (development, staging, and production). One of the managers went ahead and upgraded development to 2008 without upgrading staging and production. So, staging and production are running SQL 2005 while development is running 2008.

    Here are my issues:

    1) I need to copy data from the development environment to the staging environment.

          A. I cannot restore a backup because 2005 will not recognize the 2008 back upset. So restore is not an option.

          B. I tried using a linked server and creating a procedure that loops through sys.tables and refreshes the data, but I am having security issues with  this. Can you create a linked server from 2008 to 2005? I am unable to do this.

          C. I haven't tried creating a SSIS package yet. Could this also be an option if I cannot create a linked server?

    If anyone has run into this issue before and has an alternate was to solve these issue please let me know. Thank you all in advance.

    Dave

     


    Dave SQL Developer
    Tuesday, November 22, 2011 9:57 PM

Answers

  • Dave,

    I am assuming that when your development environment was upgraded that an inplace upgrade was undertaken ?

    If you have the disk resources available I would suggest your easiest way forward would be to do an installation of SQL Server 2005 onto your dev server that way you can get your dev environment back in line with your staging and production. Then a migration process and schedule could be looked at to migrate all of the environments.

    As you have stated and are aware you are not able to perform the restore so you would need to export and import the data across. When you look at doing the export and import i guess you have no requirements to keep any of the data.(only because I am assuming from your statements that you have just been performing restores from production etc on a regular basis.

    You best bet for the import and export on a regular basis if you are not able to install the 2005 instance again would be to use SSIS as you do not need to worry about issues you may be haing with linked servers etc.

    I hope this helps.


    Warwick Rudd
    MCT MCITP SQL Server 2008 Admin
    My Blog
    -------------------------------------------------------
    Please mark as Answered if I have answered your question
    Please vote if this was useful
    -------------------------------------------------------
    Movember is here. Show your support for Mens Health
    by making a donation here - Movember
    • Proposed as answer by Peja Tao Thursday, November 24, 2011 6:47 AM
    • Marked as answer by Kalman Toth Tuesday, November 29, 2011 12:35 PM
    Tuesday, November 22, 2011 10:18 PM
  • What exactly do you need? Do you need to migrate structure only or data as well? In the last case, how big is the database?

    I found that you can use Taks, Generate Scripts wizard from SQL Server 2008 to SQL Server 2005. It has option to script data, although we don't want to use this option on big tables, obviously. For migrating structures only this is a very good option. Make sure to select Advanced options in the Wizard and select script for version 2005. Go through each of the options in that Wizard and set to desired value.

    Also this thread may be of help.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi N Thursday, November 24, 2011 7:02 PM
    • Marked as answer by Kalman Toth Tuesday, November 29, 2011 12:36 PM
    Thursday, November 24, 2011 6:59 PM
  • Hi Dave,

    I think the problem is with staging and development server locations.In most of the cases a staging instance will be outside the 

    reach of development instance.I think your group policies are not allowing you to communicate between staging and development

    instances.

    Have you tried pinging your development server from staging server?


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    • Marked as answer by DaveDB Wednesday, November 30, 2011 4:00 PM
    Wednesday, November 30, 2011 3:31 PM

All replies

  • Dave,

    I am assuming that when your development environment was upgraded that an inplace upgrade was undertaken ?

    If you have the disk resources available I would suggest your easiest way forward would be to do an installation of SQL Server 2005 onto your dev server that way you can get your dev environment back in line with your staging and production. Then a migration process and schedule could be looked at to migrate all of the environments.

    As you have stated and are aware you are not able to perform the restore so you would need to export and import the data across. When you look at doing the export and import i guess you have no requirements to keep any of the data.(only because I am assuming from your statements that you have just been performing restores from production etc on a regular basis.

    You best bet for the import and export on a regular basis if you are not able to install the 2005 instance again would be to use SSIS as you do not need to worry about issues you may be haing with linked servers etc.

    I hope this helps.


    Warwick Rudd
    MCT MCITP SQL Server 2008 Admin
    My Blog
    -------------------------------------------------------
    Please mark as Answered if I have answered your question
    Please vote if this was useful
    -------------------------------------------------------
    Movember is here. Show your support for Mens Health
    by making a donation here - Movember
    • Proposed as answer by Peja Tao Thursday, November 24, 2011 6:47 AM
    • Marked as answer by Kalman Toth Tuesday, November 29, 2011 12:35 PM
    Tuesday, November 22, 2011 10:18 PM
  • We have the same setup.

    I think SSIS package will have the same problem in terms of connectivity, Unless you can get SSIS Package running on a machine  that can communicate to both Staging and Development environments

    The best option is to have a Dev environment setup on SQL 2005 and have databases refresh from Staging or production copy.

     


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    Tuesday, November 22, 2011 11:13 PM
  • Hi Dave,

    Both option B and C should work. There are no problems setting up a Linked Server from a SQL 2008 to a SQL 2005 server. You don't specify with errors you get when you try it, so either you'll have to investigate this a bit deeper or let us know the exact error message you get.

    Also, there should be no problems having a SSIS package that connects to both a SQL 2005 and 2008 engine.


    Steen Schlüter Persson (DK)
    Wednesday, November 23, 2011 12:57 PM
  • Firstly, I would like to thank you all for you replies. Secondly, if I reverted my development server back to SQL 2005 wouldn't that cause issues? In my experience it is usually not a good idea to downgrade once you have upgraded. Please let me know if I am missing something, or if downgrading does not cause any issues.


    Dave SQL Developer
    Wednesday, November 23, 2011 4:33 PM
  • For migrating data , it could be done through multiple options like :

    1.       DTS of import + export data ( Through SQL Management studio)

    2.       Specific SSIS package (Through BI Development studio)

    3.       or you could use also common third party tools ( like Red gate data compare )

    4.        Using T-SQL Statements but it could be through :

    ·          linked server directly or indirectly OPENQUERY commands

    ·          Openrowset (doesn’t need linked server) like :

     

    SELECT * FROM OPENROWSET ('SQLOLEDB',Servername\instance';’username';'password',DBname.dbo.table)

     


    Think more deeply of performance terms
    • Proposed as answer by Peja Tao Thursday, November 24, 2011 6:48 AM
    Wednesday, November 23, 2011 6:38 PM
  • I don't think you can directly "downgrade" your server to SQL 2005, so there will be some issues...:-). You'll have to install SQL 2008 and then install SQL 2005 from scratch again. That also means that you'll have to transfer your databases back to SQL 2005 - just like the issue you have right now.

    The best option would of course we if you could upgrade your test and prod server to SQL 2008. That will solve the problem and you'll get up on a newer platform.

    If that's not an option, then you'll either have to do the "downgrade" of your dev server or setup up a procedure for transferrring data from your SQL 2008 dev server to your SQL2005 test server.

     


    Steen Schlüter Persson (DK)
    • Marked as answer by Kalman Toth Tuesday, November 29, 2011 12:35 PM
    • Unmarked as answer by DaveDB Tuesday, November 29, 2011 7:09 PM
    Thursday, November 24, 2011 7:57 AM
  • What exactly do you need? Do you need to migrate structure only or data as well? In the last case, how big is the database?

    I found that you can use Taks, Generate Scripts wizard from SQL Server 2008 to SQL Server 2005. It has option to script data, although we don't want to use this option on big tables, obviously. For migrating structures only this is a very good option. Make sure to select Advanced options in the Wizard and select script for version 2005. Go through each of the options in that Wizard and set to desired value.

    Also this thread may be of help.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi N Thursday, November 24, 2011 7:02 PM
    • Marked as answer by Kalman Toth Tuesday, November 29, 2011 12:36 PM
    Thursday, November 24, 2011 6:59 PM
  • Ok, I did some research and you CANNOT create a linked server from/to SQL 2008/2005. You can however create the linked server from 2005 to 2008, but 2005 will not recognize the tables in 2008.

    Dave SQL Developer
    Tuesday, November 29, 2011 3:40 PM
  • This does not work. Not sure if you are thinking SQL 2000, but I am using 2008/2005.

     

    SELECT * FROM OPENROWSET ('SQLOLEDB',Servername\instance';’username';'password',DBname.dbo.table)


    Dave SQL Developer
    • Edited by DaveDB Tuesday, November 29, 2011 3:44 PM
    Tuesday, November 29, 2011 3:43 PM
  • How have you researched this? There are no problems in setting up Linked Servers between SQL 2005/2008 - and it works in both "directions".

    If needed, you can also browse the catalog and see the tables from SSMS.

    There might be certain things that doesn't work, but I've worked with Linked Servers between many 2005 and 2008 instances for a long time and I don't recall that I've come across things that doesn't work. 


    Steen Schlüter Persson (DK)
    • Proposed as answer by Naomi N Wednesday, November 30, 2011 1:25 PM
    Wednesday, November 30, 2011 7:11 AM
  • Thanks for you response.

    I can created a linked server from 2005 to 2008 and was unable to select from the tables in 2008.

     

    SELECT * FROM [SERVERNAME].[DATABASE].[SCHEMA].[TABLE]


    Dave SQL Developer

    • Edited by DaveDB Wednesday, November 30, 2011 3:27 PM
    Wednesday, November 30, 2011 3:19 PM
  • Hi Dave,

    I think the problem is with staging and development server locations.In most of the cases a staging instance will be outside the 

    reach of development instance.I think your group policies are not allowing you to communicate between staging and development

    instances.

    Have you tried pinging your development server from staging server?


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    • Marked as answer by DaveDB Wednesday, November 30, 2011 4:00 PM
    Wednesday, November 30, 2011 3:31 PM
  • That works fine for me. What error message do you get when you try it?
    Steen Schlüter Persson (DK)
    Wednesday, November 30, 2011 3:33 PM
  • Linked server between 2005 to 2008 works fine for me too.
    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    Wednesday, November 30, 2011 3:38 PM
  • Table does not exist, when I know it does.

    Dave SQL Developer
    Wednesday, November 30, 2011 3:50 PM
  • But could you give us the eaxct errormessage you get? I'd bet that it also says that maybe you don't have permissions to access the table and not only that it doesn't exists...:-).

    Which security settings have you used when you created the Linked Server? If possible, you can create a SQL Login on the target server and then use this for the Linked Server - that's normally the easiest to get working.

     If you want to use Windows Security you have to make sure that both the server(s) and your account(s) have the proper settings in AD (trusted for delegation etc.).

    If this could be your issue, then try to take a look at this link -

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

    This tells you what you need to do in order to set up delegation when creating Linked servers.

     

     


    Steen Schlüter Persson (DK)
    • Proposed as answer by Naomi N Wednesday, November 30, 2011 4:17 PM
    Wednesday, November 30, 2011 4:00 PM