|How to Migrate to SQL 2016 for CRM and BBIS |
- Upgrade to at least SP15 + Hotfix 61
- After upgrading to SP15 HF61+, but prior to moving to SQL2016, convert existing SQL jobs to PowerShell. We have built a business process that does this automatically for you with the click of a button (see PNG files inside attached '116755.zip', for reference)
- Upgrade/migrate SQL to SQL2016
- Upgrade compatibility level*
- If current database is on CL110 (SQL2012), we must change the compatibility level to CL130 by running 'CL130.sql' script**
- If current database is on CL100 (SQL2008), we must run the 'CL130.sql' script AND the 'SQL2008Script.sql' script**
*not applicable to SQL 2014. If upgrading from SQL 2014, you do not need to upgrade compatibility level and may skip step 4
**(found in the zip file attached to this article)
1. Do we have to convert my SQL Jobs with the Blakbaud conversion business before upgrading to SQL 2016?
No. The conversion of jobs is suggested as means to preserve existing scheduled jobs. It was created as a convenience, but is not required. If you do not convert existing jobs, you can simply create new Jobs in the UI.
2. What is the impact of upgrading to SP15, Hotfix 61+ without taking these steps?
These steps are only suggested if you are upgrading to SQL 2016. They are not required for the upgrade to SP15 Hotfix 61+.
3. What is the suggested upgrade path for those who plan to go to SQL 2016 at a later date?
The same steps 2-4 outlined here would still apply. The conversion task will be available in any version of the product released after Hotfix 61.
4. It is possible to convert existing jobs manually to powershell?
You can consult with your DBA about manual conversions of SQL jobs. While it is possible, it is beyond Blackbaud's Scope of Support. Our suggested method is to convert Jobs using the Job Schedule Conversion Business Process.
5. Are there any pre-requisites for the SQL 2016 Upgrade?
Yes, you must have PowerShell 5.0 installed on the SQL Server. This will appear in an updated version of our system requirements that will be released with the Hotfix.
6. When these jobs are migrated, where do they go?
They will be in the same place, the SQL Server Agent.
7. How long does it take to convert the jobs?
A number of variables will impact the conversion time, such as the number of jobs and available system resources. The upward limits of our testing included converting 1,000 jobs, which always completed in < 5 seconds.
8. How do we check to be sure that all jobs migrated?
The business process results will identify the number of jobs it converts, and will report exceptions for any that fail conversion.
9. Should we expect Enqueue jobs to be converted, or do they use a different mechanism?
Any job that can be scheduled from the front end (via the corresponding business process status page) will be converted, including Enqueue.
10. Is BBIS impacted with this change?
None of the BBIS processes fall in this category since they use a different scheduling mechanism. They are not affected by these changes.
11. If there is an exception with the conversion business process, where is it logged so we can get more details?
The exception is logged in the DB (table BUSINESSPROCESSJOBSCHEDULECONVERSIONEXCEPTION) and they are also shown on the UI tool status page in a datalist along with the job name and corresponding business process name. NOTE: re-running the UI tool clears out the previous run’s errors.
12. Other than seeing that this BP completed successfully, how else can we test that it worked?
You can always check any of the jobs directly on the SQL Server Agent before and after the conversion to confirm.
13. Do we need to stop all Job Schedules when this process runs, or can it be run concurrent to other schedules?
We recommend this tool be run when there are no scheduled processes running.
14. Do I need to upgrade Compatibility level if I am upgrading from SQL 2014?
15. I am hosted by Blackbaud. Do I need to do anything special when upgrading to SP15 HF61+?
No, you do not need to take any action when upgrading. Our hosting team will handle SQL upgrades as part of regularly scheduled infrastructure maintenance, and will complete job conversions at the time of upgrade.