Skip to main content

SQL Server Express database exceeds 10GB limit

  • Updated

Problem

Using default installation options, CloudM Migrate installs a local copy of SQL Server Express 2017. This version of SQL Server has a maximum database size of 10GB. For migrations with Record Document Mappings enabled (and specifically in Large Migrations), it is possible to exceed this database size limit.

 

Solution

To fix this issue a full version of SQL Server 2017 Standard or higher must be used with CloudM Migrate. The following will outline the steps required to reconfigure your existing CloudM Migrate installation to use a new SQL Server 2017 Standard or higher server.

  1. Provision a server with SQL Server 2017 Standard or higher and make sure it is available to your existing migration farm.

  2. Create a “PoweredBy” SQL user on your new SQL Server installation and assign the user the “dbcreator” role. See this article for instructions.

  3. Backup the existing databases from the default CloudM Migrate installation e.g. YourServer\PoweredBy. These databases will be named as follows: PoweredBy, NServiceBus, YourDomainHere, YourDomainHere-statistics etc. Additional databases may have Guid based database names.

  4. Move the database backups from step 3 onto your new SQL Server installation and restore the databases using SQL Server Management Studio.

  5. Ensure that the SQL user “PoweredBy” is a “db_owner” on the databases. This can be performed using SQL Server Management Studio. Right-click on the user → Properties. Select “User Mappings” and then go through each database ensuring “db_owner” is selected from the Database role membership UI.

  6. On all Primary and Secondary servers in your migration farm, download the following Powershell script and save as ChangeCloudMigratorConnectionStrings.ps1 into a location of your choosing. Open a Windows Powershell session as an Administrator and navigate to the directory containing the ChangeCloudMigratorConnectionStrings.ps1 file. Replacing the values where necessary run the following command .\ChangeCloudMigratorConnectionStrings.ps1 “NewSQLServer” “PoweredBy” “UserPasswordHere” “true”

    #Requires -RunAsAdministrator

function Help {
    "Updates the ConnectionStrings for the CloudMigrator suite of products`n"
    ".\Update-CloudMigrator-ConnectionStrings.ps1 [TargetSQLServer] [TargetUsername] [TargetPassword] [ReEncrypt]`n"
    "   [TargetSQLServer]             The SQL Server you are changing to.  This can be ServerName or ServerName\Instance.`n"
    "   [TargetUsername]              Username of the SQL Server Login.`n"
    "   [TargetPassword]              Password of the SQL Server Login.`n"
    "   [ReEncrypt]                   Optional Boolean Flag. Should the decrypted ConnectionStrings be re-encrypted after modification`n"
}

function Edit-Registry([string] $RegPath, [string] $RegKey, [string] $TargetSQLServer, [string] $TargetUsername, [string] $TargetPassword)
{
    Write-Host("Updating Registry")

    $currentDir = (Get-Location)
    $RegValue = 'Server=' + $TargetSQLServer + ';Database=PoweredBy;User Id=' + $TargetUsername + ';Password=' + $TargetPassword
    Cd HKLM:\$RegPath
    Set-ItemProperty -Path . -Name $RegKey -Value $RegValue    
    Cd $currentDir

    Write-Host("Updated Registry")
}

function Decrypt-ConfigurationSection([string] $aspnetRegiisPath, [string] $section, [string] $path)
{
    $currentDirectory = (Get-Location)
    Cd $aspnetRegiisPath
    .\aspnet_regiis.exe -pdf $section $path
    Cd $currentDirectory
}

function Encrypt-ConfigurationSection([string] $aspnetRegiisPath, [string] $encryptionProvider, [string] $section, [string] $path)
{
    $currentDirectory = (Get-Location)
    Cd $aspnetRegiisPath
    .\aspnet_regiis.exe -pef $section $path -prov $encryptionProvider
    Cd $currentDirectory
}

function Decrypt-Edit-Encrypt([bool] $reencrypt, [string] $aspnetRegiisPath, [string] $encryptionProvider, [string] $section, [string] $configPath, [string] $webConfigPath)
{
    Write-Host("Decrypting File: $webConfigPath")

    # Decrypt
    Decrypt-ConfigurationSection $aspnetRegiisPath $section $configPath

    Write-Host("Decrypted File: $webConfigPath")
        
    Write-Host("Editing File: $webConfigPath")
            
    # Edit
    $configurationDocument = (gc $webConfigPath) -as [xml]

    $connectionStringNode = $configurationDocument.SelectSingleNode("//$section")

    if ($connectionStringNode -ne $null) 
    {
        $connectionStringNode.InnerXml = $NewConnectionStrings
    }

    $configurationDocument.Save($webConfigPath)

    Write-Host("Edited File: $webConfigPath")

    if($reencrypt)
    {
        Write-Host("Encrypting File: $webConfigPath")

        # Encrypt
        Encrypt-ConfigurationSection $aspnetRegiisPath $encryptionProvider $section $configPath

        Write-Host("Encrypted File: $webConfigPath")
    }
}

function Process-Config-Files([string] $TargetSQLServer, [string] $TargetUsername, [string] $TargetPassword, [bool] $ReEncrypt)
{   
    $IIS_SITE = "cloudmigrator.local"
    $WEB_CONFIG_FILE_NAME = "Web.config"
    $DOT_NET_VERSION = "v4.0.30319"
    $ASPNET_REGIIS_PATH = "C:\windows\Microsoft.Net\Framework\$DOT_NET_VERSION\"
    $ENCRYPTION_PROVIDER = "DataProtectionConfigurationProvider"
    $CONFIGURATION_SECTION = "connectionStrings"
    $REGISTRY_PATH ="SOFTWARE\WOW6432Node\Cloud Technology Solutions\Installation"
    $REGISTRY_KEY = "DB"

    $IISService = Get-Service -ComputerName "localhost" w3svc -ErrorAction SilentlyContinue
        
    $NewConnectionStrings = '<add name="sql" connectionString="Server=' + $TargetSQLServer + ';Database=PoweredBy;User Id=' + $TargetUsername + ';Password=' + $TargetPassword + '" />' +
        '<add name="NLog" connectionString="Server=' + $TargetSQLServer + ';Database=PoweredBy;User Id=' + $TargetUsername + ';Password=' + $TargetPassword + '" providerName="System.Data.SqlClient" />' +
        '<add name="NServiceBus/Persistence" connectionString="Data Source=' + $TargetSQLServer + ';Initial Catalog=NServiceBus;User ID=' + $TargetUsername + ';Password=' + $TargetPassword + '" />' +
        '<add name="NServiceBus/Transport" connectionString="deadLetter=true;journal=false;useTransactionalQueues=true;cacheSendConnection=true;timeToReachQueue=00:01:00" />'
    

    # Installation Directories to check
    $ConfigPaths = @("C:\Program Files\CloudM\Migrate\Web", "C:\Program Files\CloudM\Migrate Migration Service", "C:\Program Files\CloudM\Migrate")

    # Config file names to check
    $ConfigFileNames = @("CloudMigrator.exe.config", "CloudMigratorServiceManager.exe.config", "PoweredBy.MigrationService.exe.config", "PoweredBy.MigrationStats.Processor.exe.config")

    if($IISService -ne $null)
    {
        $site = Get-IISSite $IIS_SITE
        if($site -ne $null)
        {
            Write-Host("Stopping IIS...")
            Stop-IISSite $IIS_SITE -Confirm:$false
            Write-Host("Stopped IIS")
        }
    }

    Write-Host("Stopping Service...")
    Stop-Service CloudMigratorService
    Write-Host("Stopped Service")


    foreach ($configPath in $ConfigPaths) 
    {
        Write-Host("Processing: $configPath")

        if (Test-Path -Path $configPath)
        {
            $webConfigPath = "$configPath\$WEB_CONFIG_FILE_NAME"

            # Found Web.Config already
            if (Test-Path -Path $webConfigPath)
            {
                Write-Host("Processing File: $webConfigPath")

                Decrypt-Edit-Encrypt $ReEncrypt $ASPNET_REGIIS_PATH $ENCRYPTION_PROVIDER $CONFIGURATION_SECTION $configPath $webConfigPath

                Write-Host("Processed File: $webConfigPath")
            }
            else
            {
                foreach ($configFileName in $ConfigFileNames)             
                {
                    $fullConfigPath = "$configPath\$configFileName"

                    if (Test-Path -Path $fullConfigPath)
                    {
                        Write-Host("Processing File: $fullConfigPath")

                        Rename-Item -Path $fullConfigPath -NewName $WEB_CONFIG_FILE_NAME
                    
                        Decrypt-Edit-Encrypt $ReEncrypt $ASPNET_REGIIS_PATH $ENCRYPTION_PROVIDER $CONFIGURATION_SECTION $configPath $webConfigPath
                    
                        Rename-Item -Path $webConfigPath -NewName $configFileName

                        Write-Host("Processed File: $fullConfigPath")
                    }
                }
            }    
        }

        Write-Host("Processed: $configPath")

    }
    
    Edit-Registry $REGISTRY_PATH $REGISTRY_KEY $TargetSQLServer $TargetUsername $TargetPassword
    
    Write-Host("Set Service Start")

    Start-Service CloudMigratorService

    Write-Host("Set Service End")

    if($IISService -ne $null)
    {
        $site = Get-IISSite $IIS_SITE
        if($site -ne $null)
        {
            Write-Host("Starting IIS...")
            Start-IISSite $IIS_SITE
            Write-Host("Started IIS")
        }
    }
}

# validate arguments 
if ($args -ne $null -and ($args.Length -ge 3 -or $args.Length -le 4)) 
{
    $_TargetSQLServer = $args[0]
    $_TargetUsername = $args[1]
    $_TargetPassword = $args[2]
    $_ReEncrypt = $false
    
    if($args.Length -eq 4)
    {
        try 
        {
          $_ReEncrypt = [System.Convert]::ToBoolean($args[3]) 
        } 
        catch [FormatException] 
        {
          $_ReEncrypt = $false
        }
    }
    
    if ($_TargetSQLServer -eq '/?') {
        Help
        exit 1;
    }
} 
else 
{
    Help
    exit 1;
}


Process-Config-Files $_TargetSQLServer $_TargetUsername $_TargetPassword $_ReEncrypt

  7. Using CloudM Migrate Service Manager on the Primary server, edit the Redis Server Location field and enter the server name or IP address of your Redis Installation. If using a default CloudM Migrate installation, this will be the name of your Primary Server. Click the Update Configuration button and restart the CloudM Migrate Service.

  8. From an Administrator Command Line window, reset IIS using the command “iisreset”

Comments

0 comments

Please sign in to leave a comment.