0

enter image description hereenter image description here

Being a noobie to Docker, and thinking about storage with a SQL Server that has a size of several hundred gigabytes or more, it doesn't make sense to me that it would be feasible to store that much in a container. It takes time to load a large file and the sensible location for a file in the terabyte range would be to mount it separately from the container.

After several days attempting to google this information, it seemed more logical to ask the community. Here's hoping a picture is worth 1000 words.

How can a SQL Server container mount an exterior SQL Server source (mdf,ldf,ndf) given these sources are on Fortress (see screen shot) and the docker container is elsewhere on say somewhere in one of the clouds? Similarly, Fortress could also be a cloud location.

Example:

SQL CONTAINER 192.169.20.101
SQL Database Files 192.168.10.101

Currently, as is, the .mdf, .ldf files are located in the container. They should connect to another location that is NOT in the container. It would also be great to know how to move that backup file out of the "/var/opt/mssql/data/xxxx.bak" to a location on my Windows machine. enter image description here

Jamie
  • 437
  • 4
  • 15
  • If you have an application in a container, and an existing non-container database on the network somewhere, you should be able to just connect to it like normal. You don't need to import it into Docker in any particular way. – David Maze Dec 01 '21 at 00:00
  • Assuming the SQL container will hold the msdb, master, and model databases, and for the sake of explanation, let's say tempdb and some other database ("datadb", 1TB) will have an mdf and ldf located not in the container, how is that location created, loaded, and setup so the container can connect to those files? How is that done? – Jamie Dec 02 '21 at 01:39
  • If you're talking about 1TB+ of data, I'd run the database on a dedicated server (and get a professional sysadmin or DBA to do it). Your application would connect to that external database the same way whether it's running in Docker or not. – David Maze Dec 02 '21 at 02:12

2 Answers2

0

the sensible location for a file in the terabyte range would be to mount it separately from the container

Yes. Also when you update a SQL Server you replace the container.

This updates the SQL Server image for any new containers you create, but it does not update SQL Server in any running containers. To do this, you must create a new container with the latest SQL Server container image and migrate your data to that new container.

Upgrade SQL Server in containers

So read about Docker Volumes, and how to use them with SQL Server.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
0

  1. Open a copy of Visual Studio Code and open the terminal

  2. How to access /var/lib/docker in windows 10 docker desktop? - the link explains how to get to the linux bash command from within VSCode

    docker run -it --privileged --pid=host debian nsenter -t 1 -m -u -i sh

  3. cd .. until you reach the root using VSCode and do a find command

    find / -name "*.mdf"

  4. This lists a file name, in my case as: /var/lib/docker/overlay2/merged/var/opt/mssql/data as the storage location

  5. Add a storage location on your Windows machine using the docker-compose.yml file

    version: "3.7"

    services: docs: build: context: . dockerfile: Dockerfile target: dev ports: - 8000:8000 volumes: - ./:/app - shared-drive-1:/your_directory_within_container

    volumes: shared-drive-1: driver_opts: type: cifs o: "username=574677,password=P@sw0rd" device: "//192.168.3.126/..."

  6. Copy the source files to the volume in the shared drive (found here at /var/lib/docker/overlay2/volumes/) I needed to go to VSCode again for root.

  7. Open SSMS to the SQL Instance in docker and change the file locations (you'll detach them and then swap them with commands to the volume where the files were moved) https://mssqlfun.com/2015/05/18/how-to-move-msdb-model-sql-server-system-databases/

  8. Using the VSCode again, go to the root and enable the mssql login to have permission to the data folder under /var/opt/docker/volumes/Fortress (not sure how to do this but working on it and will update here later if it can be done and otherwise I will remove my answer)

  9. Using the SSMS again, and the new permissions, attach the mdf/ldf again to the docker container SQL Server

Finally, there is a great link here explaining how to pass files back and forth between a container and a windows machine hosting the container

Jamie
  • 437
  • 4
  • 15