|
Sensory Computer Systems Forums Welcome to the Sensory Computer Systems Online Forums!
|
View previous topic :: View next topic |
Author |
Message |
John
Joined: Jun 13 2005 Posts: 23
|
Posted: Fri May 11, 2007 11:18 am Post subject: New SQL Server Migration Recommendations |
|
|
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 |
|
|
ForumAdmin Site Admin
Joined: May 20 2005 Posts: 120 Location: New Jersey
|
Posted: Thu Nov 13, 2008 11:19 am Post subject: |
|
|
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 |
|
|
ForumAdmin Site Admin
Joined: May 20 2005 Posts: 120 Location: New Jersey
|
Posted: Wed Mar 25, 2009 8:45 am Post subject: |
|
|
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 |
|
|
|
|
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
|