Restoring a SQL Server Database in Docker

Published On: 2020-06-19By:

Last month I blogged about using Docker to run SQL Server as a quick and easy way to get SQL Server up and running.  While it continues to be immensely useful, there aren’t any user databases  running on it.  The only databases present are the system databases. Yes, I could manually create a database, but it would be a lot easier to have a sample database available.

How do we do restore a sample database, in a container, that is running on a Mac laptop?  Let’s check it out!

Disclaimer: Not a container expert so there might be various ways of doing it.  This is just how I’ve figured out how to make the magic happen.

Also, if you have not read the first blog on how to get SQL Server running in Docker on your Mac, take a few minutes to read through it.

Here are the steps that we will take to make this work:

  1. Download one of the sample databases from I have a “mssql” directory in my local profile to make things easier
  2. Make sure the container is started.  You can issue a “docker ps” command terminal to see which containers are running
  3. Create a directory within the container
  4. Copy the sample database backup file into the directory you just created
  5. Restore the database onto the SQL instance that is running within the container

Sounds simple, right? Let’s see!

Create a directory

To create a directory within the container (remember that the container is running Linux), we can issue a command to the container that will create the directory.  Using Terminal (you can go to Spotlight to find the Terminal program or it is under Utilities in the Applications folder), execute the following command,

Docker exec -it sql2019 mkdir /var/opt/mssql/backups

Let us break that command down:

  1. Docker – this indicates that we are going to be doing something with Docker
  2. Exec – this tells the specified container to exec the command we pass into it
  3. -it – this basically allows for an interactive session with the container
  4. Sql2019 – this is the name of the container. You can specify the container name when you start the container or Docker will name it for you
  5. Mkdir – this is short for “make directory”
  6. /var/opt/mssql/backups – this is the directory path that is to be created.

Copy the Backup File

Now that the directory has been created, we need to get the backup file of the sample database into the container.  In my case, I am using AdventureWorks2017.bak

Docker cp ./mssql/AdventureWorks2017.bak sql2019:/var/opt/mssql/backups

Here is how that command breaks down:

  1. Docker – this indicates that we are going to be doing something with Docker
  2. cp – this is short for “copy”
  3. ./mssql/AdventureWorks2017.bak – this is the path of the source file that is being copied into the container. The “.” Indicates start with whatever working directory I am in, which is my profile directory as indicated by the “jmorehouse$”
  4. Sql2019 – this is the name of the container.
  5. :/var/opt/mssql/backups – this is the destination directory that is within the container.

Once the command is complete, we can check to make sure that the file was copied successfully.

Docker exec -it sql2019 ls /var/opt/mssql/backups

The “ls” refers to “list”.  This is equivalent to executing a “dir” command in DOS.

Restore the Database

The backup file now resides within the container and we just need to tell SQL Server to restore it.  In this section, I will be using Azure Data Studio and native T-SQL commands.

Let us first check that SQL Server can see the file.

RESTORE FILELISTONLY FROM DISK=N’/var/opt/mssql/backups/AdventureWorks2017.bak’
GO

Excellent!  SQL Server can see the backup file which means that we can restore it.  Notice on the left-hand side, there are no user databases, just system databases. Also notice that the physical names of the database shown above are from the Windows Operating System.  Since SQL Server is running on Linux within the container, we will have to move the physical files to a different location.

RESTORE DATABASE AdventureWorks2017 FROM DISK=N’/var/opt/mssql/backups/AdventureWorks2017.bak’ WITH
MOVE ‘AdventureWorks2017’ to ‘/var/opt/mssql/data/AdventureWorks2017.mdf’,
MOVE ‘AdventureWorks2017_log’ to ‘/var/opt/mssql/data/AdventureWorks2017_log.ldf’
GO

Above we can see that the database was restored and then subsequently upgraded to the SQL Server 2019 database version.  If you refresh the Databases branch on the left-hand side, the AdventureWorks2017 database is now present!

Summary

Docker continues to be my current choice of “go to” when I need a quick and easy SQL Server solution to play around.  While I absolutely recommend Azure and its offerings, utilizing Docker on my local laptop is just faster, and frankly, fun to play around it.   Now that I can easily restore databases, it just makes it that much better of a solution for non-production uses.

Like these posts?  Make sure to subscribe to get alerted when new posts arrive!

© 2020, John Morehouse. All rights reserved.

Contact the Author | Contact DCAC

Quickly Blocking Inbound Connections to SQL Server

Published On: 2020-05-22By:

Sometimes the brilliance of a solution is lost in its simplicity.  Full disclaimer, this was Joey D ’Antoni’s (B|T) idea, however, he has given me permission to blog about it.

Recently, Joey and I were helping a client to reconfigure their production SQL Server instances to have a new and improved TempDB configuration.  We knew that this would require an outage to ensure the changes had correctly taken affect.

Of course, we ran into a snag.   The current configuration had 30 plus tempdb data files and any attempts to delete the files were blocked because they were actively being used.  Nothing we tried would give us the ability to delete the excess files.

We even tried to restart the instance into single user mode, however, every time that happened something else would take the connection before we could get into the instance.  We eventually restarted the SQL Server instance to normal operation so that we could investigate why we could not get a connection when in single user mode.

Turns out that with the production nature of the instance, the clients large farm of application servers was connecting to it faster than we could.   This was discovered by using sp_who2, however, you could use the DMV sys.dm_exec_connections to see what is connecting to the instance if you desired.  So, we needed a way to block incoming connections while not being evasive like shutting down the application servers or a large network change.

This is where the brilliance comes in.

Disable TCP/IP as a network protocol to SQL Server.  BOOM.  Instant firewall.

How To

If you open SQL Server Configuration Manager, you will probably see something like this.  If you have more facets installed or multiple named instances installed, your list on the right-hand side might look a bit larger but for the most part it should be similar.

Expand SQL Server Network Configuration in the left-hand menu and select Protocols for the instance you want wish to block connections to.  In this case, I just have the default instance installed on my laptop.

 

On the right hand side, notice that the TCP/IP protocol is correctly enabled.

You can disable that by double clicking on TCP/IP and then changing the Enabled option to reflect No.

An alternative method is that you can also simply right click on the protocol and choose to enable or disable it.

With either method, you will acknowledge that the changes will not take effect until the service has been stopped and restarted.

Go ahead and restart the service.  Once back online, the TCP/IP protocol will be disabled and any servers wishing to connect will be effectively denied.  Keep in mind that we had local access to the server itself and retained connectivity to it even though SQL Server no longer allowed TCP/IP connections.  Utilizing the shared memory protocol (it was the only thing enabled), we were able to restart into single user mode, get connected, and successfully remove the extraneous tempdb data files.

Once the tempdb work was completed, we re-enabled the TCPIP protocol, and then restarted the instance back into normal operations.  Double checking sp_who2, we also verified that all the application servers had resumed connectivity to the SQL Server instance

Summary

This is an example of a quick and effortless way to block all inbound connections to the SQL Server instance.  This method required zero intervention from any engineers to stop application servers or implement firewall changes.  A few simple clicks of the mouse and we were back in business.

Sometimes it is the simple things that are astonishing and brilliant.

© 2020, John Morehouse. All rights reserved.

Contact the Author | Contact DCAC

Running SQL Server on my Mac with Docker

Published On: 2020-05-01By:

My day to day work machine is a Windows 10 HP laptop. It remains docked at my desk where it happily helps me to go about my daily business.  However, my backup machine, is an Apple MacBook Pro.  It’s small footprint and ease of use allows me to easily shift working locations if needed.  Of course, these days I’m maintaining social distancing so getting away from my desk to work elsewhere means working from my kitchen table for a change of scenery.

When I shift working locations I work on my Macbook, and I still need access to a SQL Server instance.  Yes, I could install SQL Server on the OS but it’s my personal machine and I’m not that familiar with SQL on Linux (yet).  Docker, however, it a great solution to allow me to run SQL Server containers on my Macbook without having to set up anything.  Let’s look to see how easy it is to setup Docker.

Download Docker

Go out to docker.com, download, and install the Docker Desktop application.  This is what will allow you to run container images.  The images are what define the container which then in turn runs the application, which in our case is SQL Server. Once you have it installed, you’ll have a dashboard like the one shown below.

At this point, we don’t have any containers running so we need to first get a container image and then create a container. I got my image from  Docker Hub, a library of container images that you can browse and subsequently download if needed.   Searching for SQL Server images, we can see that Microsoft has their own registry that has a few available container images.  The Feature Tags shown below show some often used images.  If you scroll further down the page, you can browse the full is list of available tags that can be used to download an image.

For my purposes, using the latest and greatest for testing and validating things is perfect so I’m only interested in the “2019-latest” container image.  To obtain the image, you can issue a pull command from within a terminal session.  You’ll notice that Docker Hub provides you with the command in the above image.

docker pull mcr.microsoft.com/mssql/server:2019-latest.

We can check to make sure that the container image was downloaded as well as view any other images we might have.  As shown below, you can see that I have three images available.

docker images

Starting the SQL Server Container

Now that we have a container image downloaded, we can now initiate setting up the container itself.   Since SQL Server has already been installed within the container, it must now be configured for it to successfully start.  Essentially, we need to tell Docker to run the image and pass in some parameters.

Here’s what they mean:

-e represents environment variables.  Here we are accepting the end user license agreement (EULA) as well as supplying a SA password (it’s required).

-p represents the ports.  The value on the left side of the colon is the port on the host and the value on the right side is the port within the container.  In this example, both sides will be listening on port 1433, which is the default port for SQL.

-d represents which container image we want to run.  In this example I want to run the 2019-latest image pulled from Microsoft.com.

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

Running this command, if all is correct, will return a GUID looking value.

We can also see that the Docker dashboard now has a running instance.

Using Azure Data Studio, I can now connect to the localhost on port 1433.  We can see that the version is 2019 CU4 which is the latest release for SQL Server 2019.

 

It’s that easy!

Getting Help

If you are stuck on a command, simply run docker + command + “–help” to get a list of available parameters.   For example, if I need to see what parameters are available for the run command, I would type in “docker run –help”.  The output would display all of the available parameters that you can supply with the “run” command.

Summary

Using Docker allows me to quickly and easily stand up an instance of SQL Server.  Often times I just need an instance to test a feature or try to figure out how something works.   All in all, this process takes about 5-10 minutes to finish and it significantly faster than standing up a VM.  If you are new to Docker, try it out.  It works on Windows too!

Watch for future blog posts on how to use Docker with SQL Server! Subscribe for email notifications when new content is posted!

© 2020, John Morehouse. All rights reserved.

Contact the Author | Contact DCAC

Working from Home Effectively

Published On: 2020-04-24By:

Some of us have been lucky enough to have  already been working from home (WFH) prior to the world events.  This has allowed most of us the ability to easily ready  our homes fit our needs of family members whom now must also work from home  For those that have suddenly been thrusted over the past weeks into the working from home lifestyle, here are some tips that I’ve learned over the last 2.5 years being home 100% of the time.   I blogged about this a few years ago when I started with Denny Cherry & Associates Consulting so some of these are updates.

Keep a Schedule & Routine

This is still probably one of the most important aspects of working from home.  Prior to coming into the WFH realm I worked a normal 8-5 job. It required me to be in the office before 8, so I decided when I switched I would keep to the same schedule. I continue to wake up at the same time and report to my desk at the same time ready to tackle the day every day.  Now, depending on what I have going on, it’s nice to be able  to wake up earlier  (like 5:30AM) so that I have some extra time to focus on things.   If I have my sons with me (for those that might be co-parenting), that also allows me to focus without having to tend to their needs. My advice is to keep whatever schedule best works for you and your employer.  Thankfully, my employer allows me to set my own schedule and manage my own time which is helpful.  Having that flexibility makes things easier on me as a work from home employee.

Beyond keeping a consistent schedule, keep a consistent routine as well.  As I mentioned, I get up about the same time.  While I *could* wear pajamas or whatever I wanted to work, I choose not to.  I get up, get showered, shave, brush teeth, etc and get fully dressed so that I’m ready for the day.  I find this makes me more productive. Once ready, I go downstairs for my coffee and usually for some breakfast.  Sometimes I’ll take food to my desk but usually I sit at my kitchen table and watch the world wake up while I enjoy my first cup of coffee.  Then it’s time to go to the “office”.  Warning the commute can be horrible so watch out for traffic jams (kids or pets) in the hallways and stairwell.  13 seconds later, I’m at my desk ready to get started.  Once there, I’ll start on any daily tasks that I need to get done and then continue on any project work I might have on my plate.

Fast forward to lunch. It’s a 50/50 chance that I’ll eat lunch at my desk.  Some days I do, others I don’t.  It’s important to take breaks away from your desk as it’s really easy to get sucked into working all the time.  My advice is to make sure to take a break for lunch and try to disconnect as much as possible.  Some days, for me, I’ll make lunch and play Xbox (Titan Fall 2), or  go for a walk, or just read a book while eating.

Come evening time, I wrap things up somewhere between 4-5PM.   Some days it’s later and some days it’s earlier usually because of kid responsibilities.  Some Due to the nature of my job some evenings I must work later due to time zones and sometimes even on the weekends due to system availability.  I recognize that it is all part of the job, however, I’m able to find a balance that works for me while maintain any responsibilities I might have.

Workspace

When I joined with Denny Cherry & Associates Consulting (DCAC), I invested some cash into my workspace.  I tend to like a clean and clutter free desk, so I invested in a good quality standing desk from Uplift Desk.  I started out with 2-27” monitors and operated with that for quite a long time.  Within the past 6 months I further invested in an additional 27” monitor so now I have three.  I have found that having three monitors (actually 4 if I used the laptop itself) allows me to have a better workflow in my daily tasks.  You might have to play with configurations or ideas, but don’t be afraid to invest (if you can) in things that help you work from home.

Of course, you can invest a lot of money into your workspace.  I know people have to walls of monitors, expensive desk chairs, extreme web camera setups, and the list goes on.  Do what you can to make it your space.  At times I’ll even work on my secondary machine standing at my kitchen bar because it works.  You don’t have to drop a ton of money and there are ways to make it work with minimal amounts of money.

Here I am just using a laptop tray that I bought off of Amazon to allow me to have a “mobile standing point” on just about any flat surface.  I find that mixing up my location from time to time help to get my mind focused on the task at hand.

Communications

Maintaining communication with your colleagues as well as your family is important, especially during this time of chaos. Without it the self-isolation feeling can be overwhelming. For me this means I check up on my family (they all live at least 6 hours away) on a semi-weekly basis to ensure that they are healthy and are doing as best as possible. Having peace of mind that they are doing well helps me better focus on work, and keeps me from getting distracted by worrying about them. Thankfully with my work I am able to take breaks and have a phone conversation, or I’ll even have a video call with them via Skype or FaceTime.

Beyond just family, make sure to have open dialogs with your managers on your status.  If you are struggling with things, let them know.  They cannot help resolve any issues if they don’t know they even exist.  I have a check-in with Denny once or twice a month just to see how things are going.  Now, at DCAC, we chat constantly via Teams.  During the day, evenings, weekends, or even holidays usually someone on the team is talking.  This makes is much easier to have some interaction and feel connected to the team.

Distractions

Working from home comes with a whole new set of distractions, namely the television.  I have found that, thankfully, the television remains off during my working time (I have a TV in my office) and I can count on one hand the number of times it’s been on while I’m working.  When I first started working from home, I had a real feeling that TV would be a huge distraction for me.  Turns out that wasn’t my distraction.  My distraction normally is the internet.  Twitter, social media, blogs, news, etc.  For me, it can be a quicksand place to get lost in and the next thing you know 3 hours have gone by.

Here are some things I use to help keep those in check:

  • Focus Assist – For Windows users, try the Focus assist. You can adjust the focus to only show certain alerts or none.  This helps me to block out any unwanted alerts for a period.  I don’t know if Apple products have something similar.
  • Internet Traffic – For Chrome (or Edge) users – StayFocusd will provide allotment of time to browse certain websites, like YouTube for example. Once you have burned through you allotted amount of time, it blocks that site until the next day.  Of course, there are way around this, but it helps to keep you in check.
  • Set Do Not Disturb (DND) – These days we are inundated with communication tools. Slack, Teams, Skype, Hangouts, pick one.   If you need time to focus, don’t be afraid to set your status on all these tools to “Do Not Disturb”.  This will let your teammates know to not bug you or just send you an email.  Respect that DND and don’t answer things unless they are critical.  I’ll even set my iPhone to DND so that I won’t get text messages or phone calls during that time.
  • Close Your Email –Really, it is ok to close your email client and not have it open. There have been studies done about the amount of time that gets wasted when you address an email and try to resume what you were focused on.  It’ll be Okay to close your email, trust me.  The messages will be there for when you get back to it.  If it’s an emergency most likely the person will call you and then you can handle it.

Summary

The way in which you work has probably shifted.  It takes time to adapt and get used to the new “normal.  I know that was the case when I shifted to working from home 100% of the time.  Now that you’ve been working from home for a bit, what has been your experience so far?  What distractions do you have to contend with and how do you handle them?  Let me know by leaving comments!

© 2020, John Morehouse. All rights reserved.

Contact the Author | Contact DCAC
1 2 3 24

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
American Business Awards Gold Award    American Business Awards Gold Award    FT Americas’ Fastest Growing Companies 2020       Best Full-Service Cloud Technology Consulting Company
   Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers