How to change microsoft sql edition

From Hostek.com Wiki
Revision as of 20:22, 26 November 2016 by Briana (Talk | contribs) (Created page with "In case you need to upgrade or downgrade a Microsoft SQL edition, here are the steps to successfully complete this process. # Install new instance # Connect to old instance #...")

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

In case you need to upgrade or downgrade a Microsoft SQL edition, here are the steps to successfully complete this process.

  1. Install new instance
  2. Connect to old instance
    1. backup databases
    2. set databases offline
  3. Restore databases to new instance
  4. On old instance, use sp_help_revlogin to generate a script to import sql logins into the new instance (https://support.microsoft.com/en-us/kb/918992)
  5. On new instance, Run "EXEC sp_change_users_login 'Auto_Fix', 'USERNAME'" on each user (replacing USERNAME with the user's actual name)
  6. At this point, all databases have been restored to the new sql instance and all logins should be imported and working
  7. Stop both (old and new) instances of SQL Server
  8. Use the 'SQL Server Configuration Manager' to edit the 'TCP/IP' settings for the new instance of SQL.
    1. Under the 'IP ALL' section, change 'Dynamic Port' from '0' to (blank)
    2. Change the Static Port to '1433'
  9. Start the new instance of SQL Server (With the TCP/IP changes, this is now the default instance)
  10. Test any sites or connections on the server to verify they are working
  11. Set the old instance to 'Disabled' in the service manager