Tuesday, April 21, 2020

Automate Reverse Azure Database Migrations using PowerShell



Working with Production-level content in lower environments (eg. DEV or UAT) is important for ongoing development and testing.  Depending on your item serialization/source control approach, keeping content in sync can be a challenge.

Using Unicorn or TDS for templates and layouts is common, but source-controlling all content (specifically media items) can bring a lot of weight to the project.  In lieu of utilizing serialization technologies or a synchronization tool such as Razl to synchronize content (which I've seen take hours to complete depending on the content load), a common approach is to periodically restore the Master/Web Databases from a Production environment down to lower environments.

In an Azure PaaS setup, without any automation or scripting, this manual process may look like this:

1) Log in to Azure Portal

2) Navigate to the source (production) SQL server instance's Master/Web database

3) Click the copy button and set up the database copy operation configuration (target database name, target server, and pricing tier)

5) Execute the copy operation and wait for the copied database to become available.

6) Log into the target SQL Server instance using SQL Server Management Studio or use the  SQL Database Query Editor built into Azure Portal, and execute an ALTER USER query to reset the login password to match the original database passwords

7) Rename the currently connected Master/Web database to include a suffix in the name (eg. _OLD)

8) Rename the copied Master/Web database to use the original Master/Web database name

9) Restart the server

Obviously, this process can vary and is generally a tedious and time-consuming process.

Luckily, Azure resources can be managed using the suite of PowerShell commands without ever needing to access the UI.  With the right script, the strain of manually executing these steps can be alleviated.

To use these commands, the Azure PowerShell Module must be installed.

For our scenario, let's assume the following:
1) PROD environment is in a separate Resource Group than the NON-PROD environment

2) While the Master database should suffice, we'll also copy down the Web database to avoid requiring a publishing operation after the script has completed.

3) ConnectionString.config value should not require modification.

4) A short "outage" of the NON-PROD environment will occur during the process since the connected database will be renamed to make room for the copy.

Let's Script It

Step 1 - Define Target and Source Variables

We need to define our target and source variables including source/target Resource Group Names, SQL server names, database names, and NON-PROD environment SQL Admin Credentials.


Step 2 -  Invoke Azure Login Process

This command will invoke the login process to a specific subscription ID. The user will be prompted to log in.


Step 3 -  Rename the currently connected database to make room for the copied database

Since the name of the database on the NON-PROD environment should remain constant, this command will rename the existing NON-PROD database to include a unique dated suffix. Note that this database will not be removed automatically and can be used as a backup in the case that the NON-PROD environment contained content that was not accounted for or backed-up prior to the migration.  Removing it will be up to you.


Step 4 -  Initialize the database copy operation

Once the name of the database is available on the target SQL server, the following command will execute the database copy process.

Step 5 - Execute the ALTER USER query

Since the database login from the source database comes with the process of a direct copy, an ALTER LOGIN query must be executed against the database to reset the [masteruser] or [webuser] passwords to match what's in the NON-PROD ConnectionStrings.config.


Step 6 - Restart the App Service

When the copy operation is completed, restarting both App Services will ensure a fresh connection to the databases is established.


Final Script




Thursday, April 9, 2020

RIP Sitecore App Center: Remove and Re-purpose Sitecore App Center Shortcuts with PowerShell

Last week, while we were all preoccupied with COVID-19, Sitecore did something nobody could have predicted. Between the chaos, our beloved Sitecore App Center was discontinued.

Not many people seemed to care about my PSA, though:

This probably would have generally slipped past me until months after the update, but this happens to come to my attention because a client wanted to start using GeoIP functionality in Sitecore in the middle of their transition to retiring the aged feature. The stars aligned, and I was fortunate enough to watch it all unfold before my very eyes. 


Until now, Geo IP Service activation had always been a two-part process:

     1) Guide the client to sign up for the Geo IP Service in the Sitecore App Center - which happened to be baked into the CMS, sourced out to an external endpoint, and required customer login (partners typically didn't have access to log in and activate anything).

     2) Make the necessary configuration and firewall updates.

The options were endless. 


The client immediately reported an issue loading the Sitecore App Center.



Lo and behold; a broken App Center (all environments, including local):


Sitecore's Cloud Status page happened to indicate that the App Center UI was "under maintenance" at the time.

This was on March 30th, a couple days after their maintenance window had closed.
Wow - month-long maintenance windows. 
Must be nice.  


Two days later - after much anticipation - an update was posted:


In addition to this status page update, the Sitecore documentation was updated to reflect the updated Sitecore Support Portal approach for activating the Geo IP Service:
Gotta love that freshly updated documentation scent.

Okay, cool.  I think that makes things a bit easier going forward.


But what about the Sitecore App Center Applications shortcuts in the Launchpad and Start Menu?  



A quick search targeting the Core DB revealed those item definitions.


The specific items that control the shortcuts are:

LaunchPad
Name: AppCenter 
ID: {F7F3379C-A034-4CE4-B2F7-9BDFC3F05A2B} 
Template: LaunchPad-Button 
Path: /sitecore/client/Applications/Launchpad/PageSettings/Buttons/Tools/AppCenter

For this shortcut, you have a few options:
   1) Delete it
   2) Move it outside of the Tools folder and into the PageSettings folder
   3) Edit the Link field to point to the permanent Sitecore Cloud Status Page related to this event

I prefer option 3 because it could at least potentially help guide future devs or marketers in the right direction when activating the GeoIP service - specifically if it had always remained off.




Start Menu
Name: Sitecore App Center 
ID: {A59E3738-08E1-49EA-9199-E0140F56C67D} 
Template: Application shortcut 
Path: /sitecore/content/Documents and settings/All users/Start menu/Left/Sitecore App Center

This Application Shortcut item is better off removed or disabled, rather than repurposed.  Attempting to load an external link won't work with this template type (or any of the other applicable template types for this location).

To disable the shortcut, clear out the Application field.


👍

Script

If you don't want to do this manually - or you're strapped for time - you can, you know..just run this PowerShell script 😉:

The script disables the Start Menu shortcut, then gives the option to repurpose or disable the Launchpad shortcut:


⚰ RIP Sitecore App Center