Sensory Computer Systems Forums Index Sensory Computer Systems Forums
Welcome to the Sensory Computer Systems Online Forums!
 
 FAQFAQ   SearchSearch   RegisterRegister 
 ProfileProfile   Log inLog in   
   Sensory Computer Systems Home   

New SQL Server Migration Recommendations

 
Post new topic   Reply to topic    Sensory Computer Systems Forums Index -> Installation / Setup / Hardware
View previous topic :: View next topic  
Author Message
John



Joined: Jun 13 2005
Posts: 23

PostPosted: Fri May 11, 2007 11:18 am    Post subject: New SQL Server Migration Recommendations Reply with quote



Last updated & reviewed 2022


New SQL Server Migration Recommendations

We will discuss Two methods in this forum topic:
    Method 1: Using SQL Administrative Tools. DBAs often prefer this method
    Method 2: Using SIMS software and SIMSCheck Utility. For everyone else

Your DBA professional may select any other alternate DBA methods for Migration.



New SQL Server Migration options usually start with the IT DBA person (if any) on your end, and their expertise and comfort level with SQL Server DBA administration.
SIMS software systems fully supports all versions of SQL Server including SQL Server 2019, 2017, 2016, 2014, 2012, 2008 R2, and 2008.

Time required: +/- 1 hour.

These are the required steps:
- Set up new Server, contemporary Win O/S and MS SQL Server, Service Packs, etc.
- Backup SIMS 2000 Database from Old SQL Server. (some choices here...). The database object name is SIMS2000.
- Restore SIMS 2000 Database on New SQL Server. (depending on how you did the backup...)
- Point all client PC's ODBC to New SQL server (if Server name changed).



Example Migration Steps and Instructions:


Method 1: Using Microsoft SQL Administrative Tools, such as MS Server Management Studio


    Note: ALL Script Code Examples shown below need to be validated by your DBA.

    1. Backup your SIMS 2000 database from [Old SQL Server name] using SQL Administrative Tools
    Example script:
    Code:
    BACKUP DATABASE SIMS2000 TO DISK='C:\SIMS2000.bak' WITH INIT


    2. Shutdown server [Old SQL Server] (or stop SQL Server Service)
    2a. This ensures no accidently access to Old SQL server from this point on.

    3. Create SIMSDBO Login on [New SQL Server]. Accomplish this manually in SSMS or scripted.
    Example script:
    Code:
    CREATE LOGIN [SIMSDBO] WITH PASSWORD=0x0100B3197C106594BF3F62013E9800EC1BEBF372B3415103C8456594BF3F62013E9800EC1BEBF372B3415103C845 HASHED, CHECK_POLICY=OFF, SID=0xD1B6F6256F0E0541BB3FEA73D1E38EAF
     
    ALTER LOGIN [SIMSDBO] WITH DEFAULT_DATABASE=[SIMS2000]


    4. Restore SIMS 2000 database on [New SQL Server]
    Example script:
    Code:
    RESTORE DATABASE SIMS2000 FROM DISK='C:\SIMS2000.bak' WITH RECOVERY,
       MOVE 'SIMS2000_Data' TO 'C:\MSSQL\DATA\SIMS2000.mdf',
       MOVE 'SIMS2000_Log' TO 'C:\MSSQL\DATA\SIMS2000.ldf'

      Advanced: For existing DB restore overwrites, add " , REPLACE" to end after the Move statements. The REPLACE option overrides several important safety checks that restore normally performs. Use with caution. Example script:
      Code:
      RESTORE DATABASE SIMS2000 FROM DISK='C:\SIMS2000.bak' WITH REPLACE

      Advanced: Another handy query, to see a result set containing a list of the database and log files (usually SIMS2000_Data.mdf & SIMS2000_Log.ldf) contained in the backup set:
      Code:
      RESTORE FILELISTONLY FROM DISK = 'C:\SIMS2000_db_201001011900.bak'



    5. If RESTORE’ing a BAK file to a [New SQL Server], ie. a server different from the original, this commonly leads to the SID (Security Identifier) out of sync between the database and the new SQL Server Instance hosting the restored database. We recommend the following easy fix. Of course your DBA may have an alternative approach.
      On the new SQL Server Instance hosting the restored database:
      5a) In SQL Studio, if you haven't done so already, create the SIMSDBO login, password = SIMSDBO, User Map as DBOwner to database SIMS2000.
      5b) Execute this SQL code:
      Code:
      USE SIMS2000
      GO
      sp_change_users_login 'Auto_Fix', 'SIMSDBO', NULL, 'SIMSDBO'

      General Notes about command sp_change_users_login
      http://msdn.microsoft.com/en-us/library/ms174378.aspx
      http://msdn.microsoft.com/en-us/library/aa259633(SQL.80).aspx

      Compatibility Level: Microsoft recommends against any mismatched configuration unless absolutely necessary.
      Reference: http://msdn.microsoft.com/en-us/library/bb510680.aspx
      Code:
      SQL 2022:   ALTER DATABASE SIMS2000 SET COMPATIBILITY_LEVEL = 160

      Code:
      SQL 2019:   ALTER DATABASE SIMS2000 SET COMPATIBILITY_LEVEL = 150

      Code:
      SQL 2017:   ALTER DATABASE SIMS2000 SET COMPATIBILITY_LEVEL = 140

      Code:
      SQL 2016:   ALTER DATABASE SIMS2000 SET COMPATIBILITY_LEVEL = 130

      Code:
      SQL 2014:   ALTER DATABASE SIMS2000 SET COMPATIBILITY_LEVEL = 120

      Code:
      SQL 2012:   ALTER DATABASE SIMS2000 SET COMPATIBILITY_LEVEL = 110



      SQL 2012 notes: MS changed trigger syntax for raiserrror. Unsupported syntax may come over in your bak file. Solution: Run a SIMSCheck refresh to update the triggers, either before the bak file is created or after on the new 2012 server. Verify via SIMS Main Menu | Administrator | Clear Record Locks. Call us we'll assist and validate.



    6. Final Validation. Please skip down to step 5. in Method #2 below, identical from here on.




Method 2: Using SIMS 2000 and SQL Query Tools


    1. Backup your SIMS 2000 database from [Old SQL Server name] using SIMS 2000 (or SIMSCheck).
    1a. Resulting in new files [yyyymmdd].SQL & .ZIP
    1b. Copy [yyyymmdd] files to [New SQL Server name]
    See print screen below to see how to backup your SIMS 2000 database.


    2. Shutdown server [Old SQL Server] (or stop SQL Server Service)
    2a. This ensures no accidently access to Old SQL server from this point on.


    3. On [New SQL Server], perform a new Client SIMS 2000 Database Installation.
    3a. Skip the last step for populating the new client database tables with starting point example data.


    4. Restore SIMS 2000 database on [New SQL Server]
    4a. Using SIMSCheck, [Connect] to new [New SQL Server] (ignore possible notice about empty database)
    4b. Restore SIMS 2000 database from file [yyyymmdd].sql. SIMSChek Main Screen, [Run Update], [db Backup], [Restore]
    4c. Be sure to change Database text box to say SIMS2000, not SIMSCLIE.


    5. Validation. Optional, but recommended.
    5a. Using SIMSCheck, [Connect] to new [New SQL Server]
    5b. You may get a warning about SIMS 2000 database version 3.3 vs 6.0, this is ok, see next step.
      Database version 6 was first released in year 2006. See Help+About to see your version.
    5c. Press buttom [Run Update] [Begin Database Update]
      It’s aok to say Yes to the question 'Do Preliminary DB DBCC ?"
      It’s aok to say "Automatic"
      You may be asked "IMPORTANT QUESTION" about updating to Version 6.
      Say Yes. And if you know our Password logic.... or call us.


    6. Time to go to all client PCs, Panelist PCs and Technician PCs.
    6b. ODBC setting change Server pointer to [New SQL Server].
      32-bit ODBC on 64-bit PC -- %windir%\sysWOW64\ODBCAD32.exe, the 32-bit version
    6c. If you haven't done it already, all client PCs will need update to newest SIMS 2000 client EXE, ie. \\[New SQL Server]\SIMSCD\Step3\Setup.exe


    7. Please verify and test that the SIMS 2000 client application works ok.



Most of these steps can be performed ahead of time for practice to verify a smooth migration. Successful practices may allow for some skipping of steps when the real migration occurs, such as creation of the SQL database on the new server.


Call us for assistance. Sensory Computer Systems frequently assists/advises/consults/etc clients on this exact topic. Many of our client sites every month upgrades/updates their SQL Servers, we frequently get the call to assist. It's easy to migrate to new equipment and/or update VMs. Call us when you're ready.


Supportively yours,

John Ream
Sensory Computer Systems
144 Summit Avenue
Berkeley Heights, New Jersey 07922 USA
908-665-6464

www.SIMS2000.com
www.SensorySIMS.com
www.SensoryTest.com
Back to top
View user's profile
ForumAdmin
Site Admin


Joined: May 20 2005
Posts: 120
Location: New Jersey

PostPosted: Thu Nov 13, 2008 11:19 am    Post subject: Reply with quote



Method 2 - First Step: Backup your SIMS2000 Database.
Creates an SQL Script file size likely a few hundred MBs. Final file will be a ZIP file named [yyyymmdd].zip.



Sensory Computer Systems



Last edited by ForumAdmin on Tue Mar 16, 2021 9:38 am; edited 1 time in total
Back to top
View user's profile
ForumAdmin
Site Admin


Joined: May 20 2005
Posts: 120
Location: New Jersey

PostPosted: Wed Mar 25, 2009 8:45 am    Post subject: Reply with quote



Method 2 - Step 6: ODBC Settings.
Point all client PC's ODBC to the New SQL server (if Server name changed).

It should be a fairly easy task to POINT your desktop SIMS 2000 application from your old server to the new server and back again if needed. In your SIMS 2000 application, look at SIMS 2000 Main Menu top menu | Utilities | Database Connection. More than likely you’re using general MS ODBC settings, so next press button [ODBC Administrator], see System tab, the SIMS 2000 entry, change the server name as needed, then go back into SIMS 2000 press [Test Connection]. Note: If SIMS 2000 [Test Connection] fails to make the connection the first time, your DBA person may need to look at the new server's settings, including if a login was created for SIMS 2000, SIMSDBO.



Sensory Computer Systems

Back to top
View user's profile
Display posts from previous:   
Post new topic   Reply to topic    Sensory Computer Systems Forums Index -> Installation / Setup / Hardware All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group