After performing SQL migration I had this top level list of things I had to think about, so this is just a private list put here of things that you might need to think about when migrating from one SQL server to another. This is for my environment, if you use clustering and other features of SQL server, then you will have some more to add to the list. However hopefully this will help me next time and may provoke some thoughts to others working on a similar project.
In this case reporting services is co-hosted on the server as is eConnect service and some IIS API end points.
They are in random order, there are many interdependencies.
Dynamics GP SQL server migration checklist
- Linked Servers, Script out any linked SQL servers and re-create on the new server.
- SQL Jobs needs scripting out and re-creating on new server. Any reporting services jobs need removing and let reporting services rebuild the schedules when it the service starts. References to server names needs checking within the scripts.
- Maintenance plans need moving to the new server.
- SQL Logins needs scripting out and moving to the new server.
- TempDB files need creating with appropriate number of data files for the server and run on appropriate disks.
- Replication needs scripting, removing, then rebuilding on the new server. Change server names in replication as has to use the actual server names not aliases.
- The user databases needs need backup from old server and restore to new server.
- Reporting services, if installed needs keys applying and configuring with new host names in .config file, restore the encryption keys from the old server.
- Back up devices needs scripting out and recreating on the new server.
- eConnect Service needs installing on the new server.
- eConnect Config for service needs updating for changes to port numbers and transport protocol used (if not using defaults).
- Any IIS web services needs migrating to new server (if any installed).
- Script out extended events and recreate on new server.
- SSIS packages need exporting and importing to new sever.
- SQL Operators need scripting out and moving to new server.
- After migration switch DNS aliases to point at new server from old (including reporting services and any iis site alias if applicable).
- Check Database Collations as set correctly for new sever vs old.
- Compare side by side new and old SQL server settings pages for all settings.
- Start SQL server Jobs after migration and once old server is out.
- Check virtual machine settings after migration to ensure full resources have been restored to the virtual machine.
- Ensure Full text index is installed (if required).
- Ensure SSIS is installed (if required).
- Ensure Reporting Services installed (if required).
- Install VMWare SCSI drivers for performance reasons.
- Check firewall settings on machine are the same between old and new, including application settings.
- Duplicate any windows file shares on the new server to be the same as the old server, including share and NTFS permissions.
- Double check any local users and user permissions.
- Install Dynamics GP workflow server CLR objects on the server using stored procedure DYNAMICS..wfDeployClrAssemblies
- Migrate mail profiles from old to new server.