Post reply

A couple of questions on backward compatibility in SQL Server

  • March 18, 2021 at 1:08 pm

    #3859502

    Hi,

    We had SQL Server 2012 on a server. We installed SQL Server 2019 on another server, took a back up from the old server and put it on the new one, and turned off the old server. We ran it for about a week now and are not happy with some things and until we work them out we want to go back to the old server.

    Questions:

    1. Can we take the backup from the 2019 server and put it on the 2012, or will there be backwards compatibility issues?

    2. We noticed that the backup on the SQL Server 2019 has almost doubled in size, compared to what it was on the 2012 server. Can someone tell me why  this would be? and is there anything I can do to reduce the size?

    Thank you

     

     

     

     

  • March 18, 2021 at 1:57 pm

    #3859511

    1. No.  You simply cannot restore a backup created in a later version of SQL to an earlier version.  You'd have to use some other method of moving the db back to 2012.
    2. It's likely the old backups were compressed, and the new ones are not.  That's just an educated guess, it's not necessarily true, but, with no other info, it's the most likely thing to check first.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • March 18, 2021 at 2:05 pm

    #3859529

    1. No, you can't restore from a newer version to an older version. You would have to rebuild the database -- scripts, Copy Database wizard in SSMS, Export Data, etc.

    2. Doubled in size:

    • Compression is the most likely/easiest culprit.
    • Same recovery model?
    • Same backup plan/schedule?
    • Have transactions actually increased?

    Out of curiosity, what are you not happy with? If performance, there were some changes to cardinality estimator in 2014 that sometimes results in bad plans/worse performance.  Many find that simply changing the compatibility level back to 110 resolves that issue until they have time to fix queries to take advantage of the query engine changes/improvements.

     

  • March 18, 2021 at 2:15 pm

    #3859531

    Thanks everyone this was a big help.

  • March 18, 2021 at 2:26 pm

    #3859532

    SSIS packages are not running at all. error message saying that the not able to log on.

    and link servers are not connecting either.

  • March 18, 2021 at 9:43 pm

    #3859677

    itmasterw 60042 wrote:

    SSIS packages are not running at all. error message saying that the not able to log on.

    and link servers are not connecting either.

    These are not really issues with SQL Server 2019 - rather they are issues with how the SSIS packages were migrated and how the linked servers were created.

    For the SSIS packages - how were these packages migrated from the old system to the new system?  Are you using the Integration Services Catalog in 2019 or some other method for managing your packages?  What is the error - and is the error related to how you execute the packages?  There could be a lot of reasons for this...but generally it is a setup/configuration issue.

    For linked servers - what doesn't work?  You say they are not connecting - is there an error?  Have you confirmed the linked server configuration is the same - using the same credentials - and the same drivers?  Are the linked servers between SQL instances - or are you attempting to connect to Oracle or some other database system?

    I assume you also upgraded the OS - which would require new drivers to be used.  It is possible that the drivers don't exist (for other database products) or the driver being specified is not correct.  Without knowing what errors are being generated - very hard to determine what is causing the issues.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • March 19, 2021 at 5:55 am

    This was removed by the editor as SPAM

  • March 25, 2021 at 10:41 am

    This was removed by the editor as SPAM

  • March 25, 2021 at 10:43 am

    This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

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