How to migrate SSISDB to Azure VM. Issues faced during SSISDB upgrade.

In this article I will share the multiple issues faced during the migration of SSISDB from the on-prem SQL Server 2016 to SQL Server 2017 version on an Azure VM. The moving of SSISDB is not a straight forward way as the regular databases migration to Azure VM. So, if it is not planned and executed properly there will be multiple issues we need to deal with. So, I am trying to collate all the issues I have faced during SSISDB migration during different scenarios into this single article.

 

After taking backup of the on-prem SSISDB database and restored it in the Azure VM having SQL Server 2017 we need to upgrade the SSISDB catalog. To upgrade we need to right click on the SSISDB catalog and click on the upgrade option. While doing this upgrade we started receiving below error.

 

 

“The system cannot find the file specified (System)”

 

 

As the upgrade through GUI is failed another way of upgrading is by directly executing the "ISDBUpgradeWizard.exe". This exe will be in the location : “C:\Program Files\Microsoft SQL Server\150\DTS\Binn\ISDBUpgradeWizard.exe”. Once you double click on the exe it will start upgrading the SSISDB.

 

In my scenario this also failed with below error:

 

“The SELECT permission was denied on the object ‘object_permissions’, database ‘SSISDB’, schema ‘internal’. Drop the certificate the user the user module signing (Microsoft.SqlServer.IntegrationServices.ISServerDBUpgrade)”

 

There could be many reasons why this fails

 

Issue : 1 SSISDB was not restored correctly.

 

Resolution : Please follow the steps mentioned in this article which clearly mentioned how to restore SSISDB.

 

 

Issue : 2 The user having any DENY privileges assigned.

 

Resolution : Verify the account and make sure there is no DENY READER, DENY WRITER permissions by mistake assigned to the user. Grant all the required permissions.

 

Issue : 3 Registry not having the correct path of the DTSPath

 

Resolution: Many online articles suggest a missing ‘\’ in the registry path also leads to this issue. So make sure you are not having the same issue.

 

In the registry : “Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\140\SSIS\Setup\DTSPath”

 

Correct path should be “C:\Program Files\Microsoft SQL Server\140\DTS\” and not “C:\Program Files\Microsoft SQL Server\140\DTS”

 

Issue : 4 After correcting the registry path and above-mentioned other changes some SQL jobs started executing fine but while deploying new SSIS packages received below error:

 

“The required components for the 64 bit edition of Integration Services cannot be found. Run SQL Server setup to install the required components.”

 

To double check if the issue is recurring, we can run below command:

 

EXECUTE [catalog].[check_schema_version] @uesr32bitruntime=1

 

The above command also will throw the same error.

Resolution:

Make sure the account ‘##MS_SQLEnableSystemAssemblyLoadingUser##’ is created and has Unsafe Assembly permissions granted.

 

Create Login ##MS_SQLEnableSystemAssemblyLoadingUser## FROM Asymmetric Key MS_SQLEnableSystemAssemblyLoadingKey  

Grant Unsafe Assembly to ##MS_SQLEnableSystemAssemblyLoadingUser##

 

After granting permission to above login the 64 bit *** error was not appearing but the SSISDB upgrade was still not happening.

 

Issue : 5

 

To fix the SSISDB upgrade issue go to the stored procedure in SSISDB and script the stored procedure [catalog].[create_environment] here if you notice the script of the stored procedure it is created with ‘WITH EXECUTE AS “accountname”.

 

Make sure the account mentioned in place of “accountname” has full permission on the SSISDB. In my case the account was “ALLSchemaOwner”. Once I granted the permissions to the account “ALLSchemaOwner” and tried upgrading the SSISDB, the upgrade happened successfully this time.

 

Now the SSISDB got upgraded successfully, all the SSIS packages are running successfully and we are able to deploy new packages without any errors.

Please let me know in the comments have you faced any issues while migrating or upgrading the SSISDB and how you fixed those issues.

Note : Please keep in mind you don’t have to apply all the fixes mentioned in the article. As per your issue you can apply the fix.

 

Thanks VV!!

 

Other Articles:

What to do when no one have sysadmin permission.

PowerShell script that displays all SQL Server folder locations from the registry.

SQL Server services are missing in SQL Server Configuration manager.

#MSSQL #sql #sqlserver #script #sqlblog #SSISDB

Creation of AlwaysOn group failing as already another AlwaysOn Group exists with the same name.

When you uninstall SQL Server without manually dropping the existing AAG group and trying to create an AAG group with the same name after reinstalling the SQL Server. or if the existing AlwaysOn group was not dropped correctly and if we try to create a new AAG group with the same name. We sometimes receive the below error: 

 
 

"Create failed for Availability Group 'DBAG'. 

 
 

The availability group 'DBAG' already exists. This error could be caused by a previous failed CREATE AVAILABILITY GROUP or DROP AVAILABILITY GROUP operation. If the availability group name you specified is correct, try dropping the availability group and then retry CREATE AVAILABILITY GROUP operation. 

Failed to create availability group 'DBAG'." 

 
 

One of the reasons this error pops up is there will be a registry entry for the AAG name.  

 
 

To check if there is an entry in the registry for the AAG group with the same name already we can check in two ways: 

 
 

1. Query the "sys.dm_hadr_name_id_map". 

2. Check the registry location "HKEY_LOCAL_MACHINE\Cluster\HadrAgNameToldMap". 

3.  
    

When you query "sys.dm_hadr_name_id_map" and if you see an entry with the same AAG group name in my example if there is an entry with the name "DBAG" then we need to drop it before creating a new AG group with the same name. 

 
 

Select * from "sys.dm_hadr_name_id_map" 

Go 

 
 

Use the below command to drop the AAG group: 

 
 

DROP AVAILABILITY GROUP DBAG; 

 
 

After dropping, we can verify the registry location "HKEY_LOCAL_MACHINE\Cluster\HadrAgNameToldMap" to make sure there is no entry with the same AAG name.  

 
 

Then if we try to create the AG with the name "DBAG" it will allow us. 

 
 

Please share if there is any easy way to fix this error in the comments section. 

 
 

 
 

Thanks VV!!

Script to verify Lock Pages in Memory and Instant File Initialization.

  

There are several situations where we need to verify if Lock Pages in Memory and Instant File Initialization are enabled or not. Like while setting up new servers, sometimes during performance tuning and sometimes company policy verification and so on.

The general method is to go to run and dig deep in secpol.msc for Instant File Initialization and gpedit.msc for verifying Lock Pages in Memory.

Below SQL queries help in verifying Lock Pages in Memory and Instant File Initialization are enabled or not directly from SSMS:

 

-- Query to check Lock Pages In Memory

-- If sql_memory_model_desc column output is LOCK_PAGES then it means Lock Pages in Memory is enabled.

Select sqlserver_start_time, sql_memory_model_desc from sys.dm_os_sys_info

Go

-- Query to check Instant File Initialization

-- If instant_file_initialization_enabled column output is Y, it means  Instant File Initialization is enabled for that particular service.

 Select servicename,instant_file_initialization_enabled from sys.dm_server_services

Sample Output:

 

 

Note: These DMVs works from SQL Server 2012 SP4 & above versions.

 

Let me know in the comments section below if any other easier way to get these details, it will help me and readers.

 

Thanks VV!!

#LockPagesInMemory, #InstantFileInitialization #MSSQL #sql #sqlserver #script #sqlblog