Jump to content
Welcome to our new Citrix community!
  • How to Migrate Citrix Database from SQL Express Server to SQL Server 2019 – Part 1


    cugcblogs

    raydavis22rnd.jpg by Ray Davis, CTA

    Summary

    I wanted to go through migrating a SQL Express database to a Standard SQL Server from a CVAD perspective. When I set up my lab, I installed the SQL Express portion( On purpose). I set it up on SQL Express as I wanted to see the painful Delivery Controller experience and to give me a chance to perform this operation. Citrix has documented this, and in this blog post, I will take you through moving it from a SQL Express instance to a SQL Single Instance. I understand this is not a typical setup, which is why I chose this setup back then.

    In Part 2 of the blog post (to come), I will share how to move the new SQL Single Instance into a SQL AG Setup. It will allow me to document and share the steps with the community for those running natively on-premises. It will clarify how to move a SQL DB to another server.

    The links I will be following:

    Let's get started!

    1. Install SQL Express Management Studio on Delivery Controller where you have your Citrix Database Installed using the link below:

    Download SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) | Microsoft Learn

    2. Take snapshots of the Delivery Controllers and Citrix Database Full Backups before making changes. In my case, it’s DDC01 and DDC02.

    Snapshots completed:

    image-39.png.e044b0beba89b3ec5c956b5bd9f044ae.pngimage-40.png.447bc9834e40ed74937317788017de20.png

    3. Disable Logging to avoid issues on changing connection string for logging connection.

    4. When you null out the connection later in this blog and set the new connection, you will get errors if this part is not completed first:

    image-41.png.4cc49ec99d4e140bec2dde0f2efb6a56.png

    5. At this point, change the DB recovering Model to “Full” on all three databases if not already set.

    image-42-1024x608.png

    6. Now we need to back up each Database.

    image-43-1024x527.png

    7. I tend to put it on a share that the source and destination SQL servers will be able to access. Please follow your change requirements within your organization.

    CitrixLABLogging

    image-44-1024x507.pngimage-45-1024x469.png

    CitrixLABMonitoring

    image-46-1024x542.pngimage-47-1024x386.png

    CitrixLABSite

    image-48-1024x643.pngimage-49-1024x481.png

    Restore the Databases

    • Now we need to remote into the new SQL Server and Restore the Databases.
    • In this case, it’s LABSQL02. Do this for each Database independently.
    image-50-1024x565.pngimage-51-1024x524.pngimage-52-1024x685.pngimage-53.png.e525909762d50f80b658f12d974a592d.png

    Restoring the databases:

    image-54-1024x422.png

    Now repeat the process for Logging and Monitoring the Database. The three Databases have been successfully restored to the destination SQL Server.

    image-55-1024x378.png

    Enable DDC logins on SQL

    Now we need to enable the SQL Login for the Delivery Controllers.

    image-56.png.e1bbfe6a08120b02bf882a70ed22f15e.png
    1. Map the SQL Logins for each delivery controller to the correct Database with permission. Do this for each one. It is a must to do this for each Delivery Controller.
      • create login [lab\DDC01$] from windows
      • create login [lab\DDC02$] from windows
    1. Once you complete step 10, You will need to set the permissions on each Database. I went and checked the roles on the Express DB first and made notes on what each one needed.
    2. Logging DB
    image-57.png.300bcbf6229460256b259d4f6236cba1.png

    Monitoring DB

    image-58.png.03dcac6235d1aebb70d27c2afce1dc39.png

    Site:

    image-59.png.24f52b1c481552cdf941dfb37470a3e6.png

    NULL out the DB connection strings on both(all) DDCs

    Now we need to log on to our Delivery controllers and NULL out the DB connection strings. Run the commands below. Do this on both Delivery Controllers. In my case, its DDC01 and DDC02

    ## Clear the current Delivery Controller database connections

    ## Note: AdminDBConnection must be the last command

    Set-ConfigDBConnection -DBConnection $null

    Set-AppLibDBConnection -DBConnection $null    #7.8 and newer

    Set-OrchDBConnection -DBConnection $null      #7.11 and newer

    Set-TrustDBConnection -DBConnection $null     #7.11 and newer

    Set-AcctDBConnection -DBConnection $null

    Set-AnalyticsDBConnection -DBConnection $null # 7.6 and newer

    Set-HypDBConnection -DBConnection $null

    Set-ProvDBConnection -DBConnection $null

    Set-BrokerDBConnection -DBConnection $null

    Set-EnvTestDBConnection -DBConnection $null

    Set-SfDBConnection -DBConnection $null

    Set-MonitorDBConnection -DataStore Monitor -DBConnection $null   #Monitoring Database

    Set-MonitorDBConnection -DBConnection $null                      #Site Database

    Set-LogDBConnection -DataStore Logging -DBConnection $null       #Logging Database

    Set-LogDBConnection -DBConnection $null                          #Site Database

    Set-AdminDBConnection -DBConnection $null -force

    Example

    image-60.png.4673c78db395425bd3e83fb99ca30a70.png

    To check, run this:

    get-command get-*DBConnection | select-object -property name | ForEach-Object { $_.name; invoke-expression $_.Name } | fl

    image-61.png.5cfa241014a766287ccc97be3f25d5bf.png

    Set the new Database Strings

    Note: MultiSubnetFailover=True  (is only needed for SQL DAG)

    $ServerName = "LABSQL02"

    $SiteDBName = "CitrixLABSite"

    $LogDBName = "CitrixLABLogging”

    $MonitorDBName = "CitrixLABMonitoring"

    $csSite = "Server=$ServerName;Initial Catalog=$SiteDBName;Integrated Security=True;MultiSubnetFailover=True"

    $csLogging = "Server=$ServerName;Initial Catalog=$LogDBName;Integrated Security=True;MultiSubnetFailover=True"

    $csMonitoring = "Server=$ServerName;Initial Catalog=$MonitorDBName;Integrated Security=True;MultiSubnetFailover=True"

    Set-AdminDBConnection -DBConnection $csSite

    Set-ConfigDBConnection -DBConnection $csSite

    Set-AcctDBConnection -DBConnection $csSite

    Set-AnalyticsDBConnection -DBConnection $csSite # 7.6 and newer

    Set-HypDBConnection -DBConnection $csSite

    Set-ProvDBConnection -DBConnection $csSite

    Set-AppLibDBConnection –DBConnection $csSite # 7.8 and newer

    Set-OrchDBConnection –DBConnection $csSite # 7.11 and newer

    Set-TrustDBConnection –DBConnection $csSite # 7.11 and newer

    Set-BrokerDBConnection -DBConnection $csSite

    Set-EnvTestDBConnection -DBConnection $csSite

    Set-SfDBConnection -DBConnection $csSite

    Set-LogDBConnection -DBConnection $csSite

    Set-LogDBConnection -DataStore Logging -DBConnection $null

    Set-LogDBConnection -DBConnection $null

    Set-LogDBConnection -DBConnection $csSite

    Set-LogDBConnection -DataStore Logging -DBConnection $csLogging

    Set-MonitorDBConnection -DBConnection $csSite

    Set-MonitorDBConnection -DataStore Monitor -DBConnection $null

    Set-MonitorDBConnection -DBConnection $null

    Set-MonitorDBConnection -DBConnection $csSite

    Set-MonitorDBConnection -DataStore Monitor -DBConnection $csMonitoring

    image-62.png.c20e81a52d2f12dce573db95c13a0828.png

    Double-check with this command:

    get-command get-*DBConnection | select-object -property name | ForEach-Object { $_.name; invoke-expression $_.Name } | fl

    image-63.png.15e6f8ad2feff9a4f1f46552a5ab9bab.png

    Double Check Services:

    Get-Command *servicestatus | Select-object -property name | foreach-object { $_.Name; invoke-expression $_.Name | Select-object -property servicestatus} | FL

    Note: There are more services than in the screenshot. Here is an example:

    image-64-1024x449.png

    Now open Citrix Studio to verify the SQL connection.

    image-65-1024x275.png

    Run some tests in Citrix Studio to make sure things look ok.

    image-66-1024x499.png

    Cleanup (SQL isn’t my expertise):

    • You can clean up the SQL Express data points now that things are on a native SQL server.
    • You still have the Full backups you created, if needed. Although if restored, the data may be off, and you are returning to an express database. Not really ideal.
    • Detach the SQL Express Database.
    • Wait some time, and remove them.

    And there you have it: migrating a SQL Express database. It seems easy enough, right? Many probably have done this and have scripts and all sorts of tricks. But for those who are not sure. The goal here was to share it and help those in need.

    See more posts from Ray Davis.

    Are you a member of CUGC? Join today!

    image-42.png.ba3936ebac256305368d956af723c4f2.png

    image-43.png.d818582a0080cff2806fc3d493c98b64.png

    image-44.png.2b8faad4ae85d5eaf64893fb0ea49125.png

    image-45.png.65616d01c9b2af7831c0cc3d9d6ee598.png

    image-46.png.43ffcc66bf91c3714897f39b279e93d7.png

    image-47.png.e9a651f3e7ffbbc27436adac3265cbbc.png

    image-48.png.bde530d3f1c4a4b92ad9e0d2ff144ca3.png

    image-49.png.c3a7441bea03a0c29d8e7b35f643a283.png

    image-50.png.67c243195aa22106db03052c42804d12.png

    image-51.png.2350b4b6f514387bf8b67efe732d0a34.png

    image-52.png.beebe30ac8f33a267dd4463dceaa31a6.png

    image-54.png.a7dbc75dd09f2b0f8a33dbdfe26a1159.png

    image-55.png.7ab601a0fe752b64c9d5e8395ea1ef0d.png

    image-64.png.7426925487f8ea6de7411b209b4623f1.png

    image-65.png.2b84984d271b86908169ab446660d44e.png

    image-66.png.0bf7759e58f849c9271cbb865ea5c776.png


    User Feedback

    Recommended Comments

    There are no comments to display.



    Create an account or sign in to comment

    You need to be a member in order to leave a comment

    Create an account

    Sign up for a new account in our community. It's easy!

    Register a new account

    Sign in

    Already have an account? Sign in here.

    Sign In Now

×
×
  • Create New...