Accessing SQL Server Using Docker
Accessing a SQL Server as part of a traditional Sitecore install may be obvious, but as we shift to the world of XM Cloud and Docker-based development environments, we’ll have to learn some new tricks.
In this case, I needed to upgrade a very large database to migrate its content into our XM Cloud instance. Here we’ll look at how to access SQL Server using Docker & SQL Management Studio.
Getting the SQL Server IP
First get the name of the container, usually projectname_msssql_1 and run this command
docker inspect projectname_mssql_1
It will produce an output like this.
The IP address of 172.20.58.96 will be used to connect to SQL Server. Save that.
Getting Your Username And Password
Next we’re going to use your sa account to login into SQL Server. You can find it’s credentials .env file for docker. Using Sitecore’s getting.started next.js template my file is in .\run\sitecore-xm1\.env.
Look for this area of the file for your credentials:
# The sitecore\admin and SQL 'sa' account passwords for this environment are configurable here.
SITECORE_ADMIN_PASSWORD=almostdryisthealbumoftheyear
SQL_SERVER=mssql
SQL_SA_LOGIN=sa
SQL_SA_PASSWORD=dTNo4b1e9uFENEALTza
SQL_DATABASE_PREFIX=Sitecore
SQL_CUSTOM_DATABASE_PREFIX_UPDATE_FROM=
We’ll have a username and password pair of sa and dTNo4b1e9uFENEALTza in this instance.
Logging Into SQL Server
Now this part is easy once we have the above information.
Use the IPAddress as the server name, SQL Server Authentication and your sa and docker .env password.
Accessing The Data
All databases found in your inside the docker container are mapped from its internal c:\data folder to the path of on your local machine of .\project\docker\data\sql. You can use the knowledge to help get your bearings when using SQL Server.
Paths inside MS SQL app reference paths inside of the container. So you may upload a file from local system that is saved to a directory that is accessible from from your local system. Once you do it, it’ll make sense.
What’s Next?
From here, the world is yours to conquer. I originally sorted this out as I was wanted to restore a back-up from an Azure SQL Database into my container as part of an XM Cloud Sitecore upgrade. You can follow those step in other blog Restoring and Upgrading a Sitecore Database with Docker.
Thanks for reading.
Related Links
Meet Dan Cruickshank
President | Sitecore MVP x 11
Dan is the founder of Fishtank. He's a multi-time Sitecore MVP and Coveo MVP award winner. Outside of technology, he is widely considered to be a top 3 father (routinely receiving "Father of the Year" accolades from his family) and past his prime on the basketball court.