2

I am trying to find a way to run SQL queries in MSSQL that is hosted in a container as part of AKS. I did not perform the deployment, but I have access to the resources within Azure. It uses a mostly standard Sitecore k8s manifest with XP0 and Lighthouse demo.

While I understand it is frowned upon to connect directly to SQL when working with Sitecore, sometimes it is necessary. In my case, I need to review the current contacts in xDB and verify they have (or don't have) the facets that I am expected. These are custom facets and I cannot validate via their profile. If I can connect to MSSQL and run a query I can validate the contacts.

How can I connect/work with MSSQL when it is hosted in this manner?

1 Answer 1

2

I imagine there are multiple ways, but the following worked for me.

  1. Install Azure CLI
  2. Navigate to your Kubernetes resource in Azure, specifically the "Overview" tab
  3. Click "Connect" in the menu bar
  4. Open Powershell as an administrator and run the 2 commands that are listed
    • az account set --subscription YOURSUBID
    • az aks get-credentials --resource-group YOURRESOURCEGROUP --name AKSRESOURCENAME
  5. These commands will populate a file in your user folder locally: C:\Users\YOU\.kube We will need this later
  6. Download Lens (it's free) and install it
  7. Open Lens
    • Because we previously ran the two Azure CLI commands and populated our Users directory, Lens SHOULD prepopulate our cluster. You can verify by selecting the Catalog icon then "Clusters" and view your available clusters Lens Clusters
    • The one with the file=~\.kube\config was automatically picked up. The second one was my attempt to copy and paste the contents of the config file in "File > Add Cluster".
    • Either option should yield the same result
  8. Select Workloads > Pods
    • Be sure to change your namespace off the default. (right side of application) Namespace selection
  9. Select your mssql container/pod and scroll down to the "Ports" section (about midway) pod details
  10. Click "Forward..."
  • You can leave the default settings such as "Open in Browser". It won't work in a browser but we can quickly see the port number this way.
  1. Click "Start"
  2. Identify the port it chose. The browser will open at http://localhost:YOURPORT
  3. Open Sql Management Studio and connect
  • Server name: it MUST include 127.0.0.1 (NOT localhost) and it MUST use a comma , instead of a colon :. An example, valid server name is 127.0.0.1,59324
  • Login: Your SQL username (probably 'sa')
  • Password: Your SQL password

Valid login

Note: you can view your environment variables right below the Port forwarding setting, just click the icon of an eye Environment variables

You should now be connected to your MSSQL instance running in AKS. It's definitely slow, but is good enough to execute a few queries.

1
  • The same method can be used for connecting directly to Solr. In that case the "Open in Browser" setting works great and immediately opens the Solr admin. I am not sure if this warrants a separate question/answer since you can also do the same for other roles.
    – jrap
    Feb 9, 2022 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.