Migrating SQL Server Databases to Containers On Pure Storage


Introduction

 In this bog I will demonstrate how we can migrate an existing SQL Server database running on either a Bare Metal or Virtualised environment to a container platform running on Pure Storage and the Pure Storage Plugin.

Overview of Environment

Source environment
  • Virtual machine on ESX 7
  • Windows 2016
  • SQL Server 2016
  • SSMS 18.4
  • Database - Demo 
Target Environment
  • Docker 19.03
  • Oracle Linux 7.6
  • Pure Storage Plugin 3.10


Step 1 - Backup Source DB


I am using SSMS UI to backup the database for this example, you can just as easily backup the
database using T-SQL.

Lets Select some data in the table before we backup, so we can compare it to after we migrate to the container





















  • Backup up the Database














Step 2 - Copy DB backup    


Once we have completed the database backup we need to copy it over the docker host, I'm not covering the copy process in this blog, however if you have any questions please feel free to contact me at cbannayan@purestorage.com

Step 3 - Create a new SQL Server Container

  • Create a new persistent volume called sqldemo of size 100G for the SQL container




  • Confirm the volume is created on the FlashArray


  • Let's create the container using the persistent volume






    • Container name: sqldemo
    • volume:              sqldemo
    • port:                   1427
    • SQL Server:       2019

  • let's log onto the container and confirm our volume is mounted


As we can see, the volume is mounted on /var/opt/mssql with the same serial number "484e" as the volume listing on the array

  • Let's log into the container and confirm it's up and running, as you can see we have only the default databases




Step 4 - Copy the backup to the container and restore







  • Lets log into the container and restore the database
RESTORE DATABASE demo FROM DISK = N'/var/opt/mssql/backup/demo.bak' \ 
WITH RECOVERY, MOVE  'demo' TO N'/var/opt/mssql/data/demo.mdf', MOVE \ 
'demo_log' TO N'/var/opt/mssql/backup/demo_log.ldf'



  • Confirm the database has been restore, as you can see the demo database has been restored

  • Lets confirm the data in the database is the same as the source DB


from the table output we can confirm out data matches the table in source database, lets do
a row count to double check


Container:



Source Database:



Step 5 - Now lets Clone the Container


So now that we have migrated the database to the container, this container can now become our GOLD image, from here we can create copies/clones for developers and testers. Using the Pure Storage plugin we will now create a clone of the production container.

  • Let's clone the production sqldemo volume








You can see from the above output that we have create a new clone volume called sqldemo-clone which has it's source as sqldemo

  • Now lets create a new clone container called sqldemo-clone using the clone volume sqldemo-clone








  • Lets log into the new container and confirm our production database exists, notice we have used he new port number 1425 which we used to create the new clone container













Summary

In this blog we have seen how we can migrate an existing production or Source SQL Server Database to a container, whilst production configurations may vary and some may not be fit for containerisation, the ones that are can be done quite easily as shown. Using the Pure Storage Container plugin we have demonstrated how quickly and easily we can clone and create multiple copies of the source database.

More and More organisations are adopting or planning a Container/Kubernetes strategy, Using Pure Storage provides the foundation for the strategy.

Note: Please see my other blogs on configuring SQL Server with containers.

http://bannaych.blogspot.com/2020/04/mssql-server-on-docker-with-pure.html

http://bannaych.blogspot.com/2020/05/cloning-sql-server-containers-with-pure.html









Comments

Post a Comment

Popular posts from this blog

Oracle Containers on Kubernetes and Pure Service Orchestrator

Image
Pure Service Orchestrator allows for smart provisioning of storage to one or many arrays, this provides administrators and developers the ability to  provision storage on demand with cloud like functionality, ease and flexibility. In this blog I will use Pure Service Orchestrator ( PSO ) to automate the provisioning of volumes to Kubernetes which will then be used to create Oracle containers using persistent storage.   My next blog will demonstrate the  power of using  Pure Storage snapshots and cloning to rapidly create instant copies of the source Oracle database. Environment NOTE:  In this blog I will not be going through the Kubernetes cluster install Role FQDN IP OS RAM CPU K8 Ver Master master.localdomain.com 192.168.111.231 Ubuntu 18.04 5G 3 1.18 Worker1 worker1.localdomain.com 192.168.111.232 Ubuntu 18.04 8G 4 1.18 # kubectl get nodes -o wide NAME STATUS ROLES AGE VERSION INTERNAL-IP EXTERNAL-IP OS-IMAGE KERNEL-VERSION CONTAINER-RUNTIME m
Read more

SQL Server container with Pure Storage Docker volumes

Image
SQL Server on Docker with Pure Storage Persistent Volumes Docker and containers have become an integral part of virtualisation. Having the ability to spin up containers has almost instantly transformed the way we build, develop and manage applications. Many of us have been using some sort of virtualisation for decades as this is not a new concept. Due to their speed, simplicity and automation, container usage has garnered substantial popularity and are becoming ubiquitous in the field of virtualisations. So why use containers ? Decouple application runtime from infrastructure. Runs instantly. Process and user isolation for application multi-tenancy. Key building blocks for microservices. But what happens when you delete the container ? The main issue in deleting a container is that you delete the writeable layer and any data that was there, hence no data persistence. Docker volumes provide us a way to decouple your application from its state, to the point where you c
Read more

Using Portainer to Manage and Deploy Oracle Containers with Pure Storage

Image
  In this blog I will show you how can we can install Portainer ( A  platform-agnostic tool for managing containerized applications ) to manage Oracle database containers and persistent volumes running Pure Storage FlashArray   Install Portainer Portainer runs as container, so installing it is just like installing any other container on Docker. Note: Docker and the Oracle container image is already installed - if you want more detail on that check out - http://bannaych.blogspot.com/2021/02/oracle-19c-database-clones-using-pure.html Lets create a persistent volume to run Portainer on, using the Pure Docker plugin # docker volume create port_vol port_vol Lets create the Portainer container # docker run -d -p 8000:8000 -p 9000:9000 --name=portainer --restart=always \ -v /var/run/docker.sock:/var/run/docker.sock -v port_vol:/data \ portainer/portainer-ce Lets confirm the new Portainer container is running # docker ps CONTAINER ID IMAGE
Read more