Name already in use
sql-docs / docs / ssms / register-servers / create-a-new-registered-server-sql-server-management-studio.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
Create a New Registered Server (SQL Server Management Studio)
This topic describes how to save the connection information for servers that you access frequently, by registering the server in the Registered Servers component of SQL Server Management Studio in [!INCLUDEssnoversion]. A server can be registered before connecting, or when connecting from Object Explorer. There is a special menu option to register the server instances on the local computer.
There are two kinds of registered servers:
Local server groups
Use local server groups to easily connect to servers that you frequently manage. Both local and non-local servers are registered into local server groups. Local server groups are unique to each user. For information about how to share registered server information, see Export Registered Server Information (SQL Server Management Studio) and Import Registered Server Information (SQL Server Management Studio).
[!NOTE]
We recommend that you use Windows Authentication whenever possible.
Central Management Servers
Central Management Servers store server registrations in the Central Management Server instead of on the file system. Central Management Servers and subordinate registered servers can be registered only by using Windows Authentication. After a Central Management Server has been registered, its associated registered servers will be automatically displayed. For more information about Central Management Servers, see Administer Multiple Servers Using Central Management Servers. Versions of [!INCLUDEssNoVersion] that are earlier than [!INCLUDEsql2008-md] cannot be designated as a Central Management Server.
Using SQL Server Management Studio
To create a new registered server
If Registered Servers is not visible in SQL Server Management Studio, on the View menu, click Registered Servers.
Server type
When a server is registered from Registered Servers, the Server type box is read-only, and matches the type of server displayed in the Registered Servers pane. To register a different type of server, click Database Engine, Analysis Server, Reporting Services, or Integration Services on the Registered Servers toolbar before starting to register a new server.
Server name
Select the server instance to register in the format: <servername>[\<instancename>].
Authentication
Two authentication modes are available when connecting to an instance of [!INCLUDEssNoVersion].
Windows Authentication
Windows Authentication mode allows a user to connect through a [!INCLUDEmsCoName] Windows user account.
SQL Server Authentication
When a user connects with a specified login name and password from a nontrusted connection, [!INCLUDEssNoVersion] performs the authentication itself by checking whether a [!INCLUDEssNoVersion] login account has been set up and whether the specified password matches the one previously recorded. If [!INCLUDEssNoVersion] does not have a login account set, authentication fails, and the user receives an error message.
User name
Shows the current user name you are connecting with. This read-only option is only available if you have selected to connect using Windows Authentication. To change User names, log in to the computer as a different user.
Login
Enter the login to connect with. This option is available only if you have selected to connect using [!INCLUDEssNoVersion] Authentication.
Password
Enter the password for the login. This option can be edited only if you have selected to connect by using [!INCLUDEssNoVersion] Authentication.
Remember password
Select to have [!INCLUDEssNoVersion] encrypt and store the password you have entered. This option is displayed only if you have selected to connect using [!INCLUDEssNoVersion] Authentication.
[!NOTE]
If you have stored the password and want to stop storing it, clear this check box, and then click Save.
Registered server name
The name you want to appear in Registered Servers. This name does not have to match the Server name box.
Registered server description
Enter an optional description of the server.
Test
Click to test the connection to the server selected in Server name.
Save
Click to save the registered server settings.
The Query Editor window in SQL Server Management Studio can connect to and query multiple instances of [!INCLUDEssNoVersion] at the same time. The results that are returned by the query can be merged into a single results pane, or they can be returned in separate results panes. As an option, Query Editor can include columns that provide the name of the server that produced each row, and also the login that was used to connect to the server that provided each row. For more information about how to execute multiserver queries, see Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio).
To execute queries against all the servers in a local server group, right-click the server group, point to click Connect, and then click New Query. When queries are executed in the new Query Editor window, they will execute against all servers in the group, using the stored connection information including the user authentication context. Servers registered by using [!INCLUDEssNoVersion] Authentication but not saving the password will fail to connect.
To execute queries against all the servers that are registered with a Central Management Server, expand the Central Management Server, right-click the server group, point to click Connect, and then click New Query. When queries are executed in the new Query Editor window, they will execute against all of the servers in the server group, using the stored connection information and using the Windows Authentication context of the user.
Setting up a local SQL Server database
In this guide, we'll talk about how to install and configure a SQL Server instance and the sqlcmd command line client. We will go over how to install and set up these components on your computer for local access.
This guide will cover the following platforms:
Navigate to the sections that match the platforms you will be working with.
Setting up SQL Server on Windows
Microsoft provides native Windows installers for SQL Server on their site and offers various versions of SQL Server suitable for different purposes. For the purposes of this guide, we will download and install the free Developer edition. You can easily upgrade to a paid version from the Developer edition if you want to use it for production.
To begin, visit Microsoft's page for SQL Server. Find the section related to the Developer edition and click Download now:
Once the download completes, double click on the file to run the installer (you may have to confirm that you wish to allow the program to make changes to your computer).
On the initial screen of the installer, you will be asked to choose what type of installation you want to perform:
Choose Basic to continue on with a conventional installation using the most common options.
Next, you'll be asked to agree to the Developer Edition licensing terms:
When you have read the license and agree to the terms, click Accept to continue.
Next, confirm or change the installation location:
When you are ready, click Install to begin the installation process.
The installer will begin to download and install components to set up SQL Server on your computer:
When the installation is finished, a screen will appear noting the current installation properties:
To connect to the new SQL Server instance right away, click Connect Now at the bottom.
A new window will Cmd window will appear and automatically log you into the SQL Server instance using the sqlcmd client:
As shown in a comment at the top of the window, you can connect to SQL Server manually at any time with the sqlcmd client by typing:
To exit the current SQL session, type:
If you are using Prisma Client with SQL Server, you can use the SQL Server connector to connect, map your models, and manage your data.
You can also check out our guides to see how to use Prisma with Microsoft SQL Server on a new project or in an existing project.
Prisma is an open-source database toolkit for Typescript and Node.js that aims to make app developers more productive and confident when working with databases.
Setting up SQL Server on macOS
While Microsoft does not provide a native installer for macOS, they do support running SQL Server on macOS through Docker. The main SQL Server Docker container is built using a Linux container, allowing any host capable of running Docker containers to run the database server.
You'll need at least 2 GB of memory (probably at least a little more) to successfully run the image, however Docker itself requires at least 4 GB of memory.
To begin, make sure you have the Docker on your system. Docker Desktop for Mac includes Docker Engine and other related applications. If you don't already have Docker installed, follow the instructions included in the above link.
Once you have Docker up and running, you can pull the SQL Server Docker image from Microsoft Container Registry by typing:
This will download all of the required image layers to your local system, allowing a faster startup.
When you're ready to start the container, type the following command.
Remember to replace <password> with the value of your intended password and choose a value that conforms to the image's password policy. At the time of this writing, the policy is defined as: "The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols.":
The SQL Server container will be started up in the background. The string of characters displayed is the new container's ID.
You can verify that the container is up and running by typing:
You should see the mssql container among the list. If the container is not running or you have trouble, you can try viewing its logs to see if there are any helpful messages:
The SQL Server container not only has the database server installed, it also has some of the common tooling available, including the sqlcmd command line client. To use this client to connect to the database instance, you can use docker exec to access the command and authenticate against the database:
You will be authenticated to the SQL Server inside the container and dropped into a SQL shell. You can verify that everything is up and running by typing:
To exit the SQL session and get back to your normal shell, type:
To shut down the SQL Server container when you're done, you can stop it by typing:
To remove the container instance (including all data inside!), type:
If you are using Prisma Client with SQL Server, you can use the SQL Server connector to connect, map your models, and manage your data.
You can also check out our guides to see how to use Prisma with Microsoft SQL Server on a new project or in an existing project.
Prisma is an open-source database toolkit for Typescript and Node.js that aims to make app developers more productive and confident when working with databases.
Setting up SQL Server on Linux
Installation methods differ depending on the Linux distribution you are using. Follow the section below that matches your Linux distribution. There are also instructions using Docker if you prefer that configuration or want to use a distribution not listed.
The easiest way to install SQL Server on Ubuntu 20.04 is to install from the dedicated repositories provided by Microsoft. Your machine must have at least 2 GB of memory to successfully install and run the necessary software.
To begin, add a new repository definition to your system by typing:
You also need to add a separate repository to get access to the sqlcmd binary and other tools:
Next, add the Microsoft package signing key to apt so that it trusts the packages in the new repository:
With the repository set up, you can install SQL Server and the sqlcmd command line client by typing:
Once the installation is complete, you need to configure your new database instance. To do so, run the included mssql-conf setup script to set some of the basic properties of your new system:
You will be asked a series of questions in order to configure the database server.
First, it will ask you what edition of SQL server you want to use:
If you have a paid license, you can choose the appropriate version. If you are using the server in a non-production environment, it is safe to choose the developer edition.
Next, you'll have to accept the license terms again:
Finally, you'll have to set and confirm a password for the SQL Server system administrator account (called the SA account in many places):
To use the sqlcmd client to connect to your SQL Server instance, it's easiest to add the mssql-tools binary directory to your PATH . To configure this, type:
Afterwards, re-source one of the two files above to evaluate the new PATH for your current session:
You can now connect to your database instance by typing:
You'll be prompted for the password you set up earlier. After successfully authenticating, you will be dropped into an SQL shell. From here, you can verify that everything is working by printing the server's version:
To exit the SQL shell and get back to the command line, you can type:
If you are using Prisma Client with SQL Server, you can use the SQL Server connector to connect, map your models, and manage your data.
You can also check out our guides to see how to use Prisma with Microsoft SQL Server on a new project or in an existing project.
Prisma is an open-source database toolkit for Typescript and Node.js that aims to make app developers more productive and confident when working with databases.
CentOS and Red Hat
The easiest way to get SQL Server installed on CentOS or Red Hat is to use the repositories provided by Microsoft. Linux hosts must have at least 2 GB of memory to install and run SQL Server.
Before installing SQL Server, you need to install and configure its dependencies. We need both Python 2 and OpenSSL 10 to continue:
After Python 2 is installed, configure the system to use it as the default Python instance:
From the list that follows, select the number associated with the Python 2 installation. In the example below, this will be option 2:
With the dependencies in place, you can now configure the SQL Server YUM repository:
Afterwards, you need to configure an additional repository to get access to the sqlcmd and other tools:
Once the repositories are configured, install SQL Server by typing:
Once the installation is complete, you need to configure your new database instance. To do so, run the included mssql-conf setup script to set some of the basic properties of your new system:
You will be asked a series of questions in order to configure the database server.
First, it will ask you what edition of SQL server you want to use:
If you have a paid license, you can choose the appropriate version. If you are using the server in a non-production environment, it is safe to choose the developer edition.
Next, you'll have to accept the license terms again:
Finally, you'll have to set and confirm a password for the SQL Server system administrator account (called the SA account in many places):
To use the sqlcmd client to connect to your SQL Server instance, it's easiest to add the mssql-tools binary directory to your PATH . To configure this, type:
Afterwards, re-source one of the two files above to evaluate the new PATH for your current session:
You can now connect to your database instance by typing:
You'll be prompted for the password you set up earlier. After successfully authenticating, you will be dropped into an SQL shell. From here, you can verify that everything is working by printing the server's version:
To exit the SQL shell and get back to the command line, you can type:
If you are using Prisma Client with SQL Server, you can use the SQL Server connector to connect, map your models, and manage your data.
You can also check out our guides to see how to use Prisma with Microsoft SQL Server on a new project or in an existing project.
Prisma is an open-source database toolkit for Typescript and Node.js that aims to make app developers more productive and confident when working with databases.
If you are using a Linux distribution that Microsoft does not provide packages for or if you simply prefer, another option is to run SQL Server with Docker. You'll need at least 2 GB of memory (probably at least a little more) to successfully run the image.
To begin, make sure you have the Docker Engine on your system. You can find detailed instructions for various platforms in the Docker Engine documentation.
Once you have Docker up and running, you can pull the SQL Server Docker image from Microsoft Container Registry by typing:
This will download all of the required image layers to your local system, allowing a faster startup.
When you're ready to start the container, type the following command.
Remember to replace <password> with the value of your intended password and choose a value that conforms to the image's password policy. At the time of this writing, the policy is defined as: "The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols.":
The SQL Server container will be started up in the background. The string of characters displayed is the new container's ID.
You can verify that the container is up and running by typing:
You should see the mssql container among the list. If the container is not running or you have trouble, you can try viewing its logs to see if there are any helpful messages:
The SQL Server container not only has the database server installed, it also has some of the common tooling available, including the sqlcmd command line client. To use this client to connect to the database instance, you can use docker exec to access the command and authenticate against the database:
You will be authenticated to the SQL Server inside the container and dropped into a SQL shell. You can verify that everything is up and running by typing:
To exit the SQL session and get back to your normal shell, type:
To shut down the SQL Server container when you're done, you can stop it by typing:
To remove the container instance (including all data inside!), type:
If you are using Prisma Client with SQL Server, you can use the SQL Server connector to connect, map your models, and manage your data.
You can also check out our guides to see how to use Prisma with Microsoft SQL Server on a new project or in an existing project.
Prisma is an open-source database toolkit for Typescript and Node.js that aims to make app developers more productive and confident when working with databases.
How do you check your SQL Server version?
Several versions of Microsoft's SQL Server are supported, and there are several methods for determining which version you are running.
Any of the listed methods from Microsoft will return the version and edition of the SQL Server Database Engine you are running.
How can you download SQL Server for free?
There are two free, specialized editions of SQL Server available for download. The Developer and Express version are available for download at Microsoft's page for SQL Server.
The Developer version is a full-featured free edition, licensed for use as a development and test database in a non-production environment.
The Express version is ideal for development and production for desktop, web, and small server applications.
What is the SQL Server Developer edition?
SQL Server 2019 Developer is a full-featured edition, licensed for use as a development and test database in a non-production environment.
Is Azure SQL the same as SQL Server?
Azure SQL is based on SQL Server, so they share many similarities in functionality and compatibility. However, this does not mean they are the same.
Azure SQL is a family of managed products that use the SQL Server database engine in the Azure cloud.
What is the SQL Server Configuration Manager?
SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers.
The configuration manager is installed with your SQL Server installation and is available from the Start menu or can be added to any other Microsoft Management Console display.
4.4.4. Соединение с сервером SQL и создание базы данных
Создание нового зарегистрированного сервера (среда SQL Server Management Studio)
В этой статье описывается, как сохранить сведения о подключении для серверов, к которым часто выполняется обращение, путем регистрации сервера в компоненте «Зарегистрированные серверы» среды SQL Server Management Studio в SQL Server. Сервер может быть зарегистрирован в обозревателе объектов до или во время подключения. Для регистрации экземпляра сервера на локальном компьютере существует специальный пункт меню.
Существует два вида зарегистрированных серверов.
Группы локальных серверов
Группы локальных серверов можно использовать для простого подключения к серверам, которыми пользователь часто управляет. И локальные, и нелокальные серверы регистрируются в группах локальных серверов. Группы локальных серверов уникальны для каждого пользователя. Сведения о том, как обмениваться сведениями о зарегистрированном сервере, см. в разделах Экспорт сведений компонента «Зарегистрированные серверы» (среда SQL Server Management Studio) и Импорт сведений компонента «Зарегистрированные серверы» (среда SQL Server Management Studio).
Рекомендуется использовать проверку подлинности Windows.
Центральные серверы управления
Центральные серверы управления сохраняют регистрации серверов на центральном сервере управления, а не в файловой системе. Центральные серверы управления и подчиненные зарегистрированные серверы могут быть зарегистрированы только с помощью проверки подлинности Windows. После регистрации центральных серверов управления связанные с ним зарегистрированные серверы будут отображены автоматически. Дополнительные сведения о центральных серверах управления в разделе Администрирование нескольких серверов с помощью центральных серверов управления. Версии SQL Server ранее SQL Server 2008 нельзя назначить в качестве сервера централизованного управления.
4.4.4. Соединение с сервером SQL и создание базы данных
1) Запустить задачу: «Пуск» → «Все программы» → «Microsoft SQL Server 2008» → «Среда SQL Server Management Studio».
2) В окне «Соединение с сервером» в выпадающем списке «Имя сервера» выбрать строку « ». (при этом «Тип сервера» должен быть – «Компонент Database Engine», а «Проверка подлинности» -«Проверка подлинности Windows»)
3) В окне «Выбор серверов» на вкладке «Локальные серверы» в разделе «Компонент Database Engine» должно отобразиться имя Вашего локального сервера (совпадает с именем компьютера). Выбрать данный сервер и нажать кнопку «ОК».
В случае если среда «SQL Server Management Studio» устанавливалась на другой компьютер, сервер следует выбирать на вкладке «Сетевые серверы».
4) В окне «Соединение с сервером» (см. Рис. 4.35) нажать кнопку «Соединить». После удачного соединения в «Обозревателе объектов» отобразится структура объектов Вашего сервера.
1) В «Обозревателе объектов» кликнуть правой клавишей мыши по строке «Базы данных» и выбрать пункт меню «Создать базу данных…».
2) В окне «Создание базы данных» задать имя базы данных и нажать кнопку «ОК». Выйти из задачи «Среда SQL Server Management Studio».
Как создать локальную базу данных внутри Microsoft SQL Server 2014?
Я только что установил Microsoft SQL Server Management Studio 2014. Я хочу создать базу данных, чтобы я мог начать создавать некоторые таблицы с нуля. Все, что я получаю-это возможность подключения к базе данных. Не создавать новую базу данных.
согласно комментариям, сначала вам нужно установить экземпляр SQL Server, если у вас его еще нет -https://msdn.microsoft.com/en-us/library/ms143219.aspx
после установки вы должны подключиться к этому экземпляру (серверу), а затем вы можете создать базу данных здесь -https://msdn.microsoft.com/en-US/library/ms186312.aspx
предупреждение! SQL Server 14 Express, SQL Server Management Studio и SQL 2014 LocalDB являются отдельными загрузками, убедитесь, что вы действительно установили SQL Server, а не только Management Studio! SQL Server 14 express с LocalDB ссылка для скачивания
на YouTube видео обо всем процессе.
рецензия С фотографиями об установке SQL Server
как выбрать локальный сервер:
когда вас попросят подключиться к «серверу баз данных» прямо при открытии SQL Server Management Studio, сделайте следующее:
1) убедитесь, что у вас есть тип сервера: Database
2) убедитесь, что у вас есть аутентификация: аутентификация Windows (без имени пользователя и пароля)
3) в поле «имя сервера» справа и выберите стрелку раскрывающегося списка, нажмите кнопку ‘Обзор’
4) появляется новое окно «Поиск серверов», обязательно выберите вкладку «локальные серверы» и в разделе «компонент Database Engine» у вас будет локальный сервер, настроенный во время установки SQL Server 14
как создать локальную базу данных внутри Microsoft SQL Server 2014?
1) после подключения к серверу откройте Панель инструментов обозревателя объектов в разделе » вид » (должна открыться по умолчанию)
2) теперь просто щелкните правой кнопкой мыши на «базы данных», а затем «создать новую базу данных», чтобы принять через инструменты создания базы данных!
Настройка удаленного подключения
При использовании сервера БД на отдельном сервере необходимо сконфигурировать сетевые протоколы, по которым будет подключаться Oktell. Настройка производится при помощи SQL Server Configuration Manager (Диспетчер конфигурации SQL Server).
1. Перейдите в раздел «Сетевая конфигурация SQL Server» для 64-битной версии SQL Server. Если у вас 32-битная версия откройте вкладку «Сетевая конфигурация SQL Server (32-разрядная версия)».
2. Откройте «Протоколы для Oktell». Выберите протокол TCP/IP.
3. На первой вкладке «Протокол» установите следующие опции:
- Включено — Да
- Прослушивать все — Нет.
4. На второй вкладке «IP-адреса» выберите необходимый интерфейс и установите для него следующие опции:
- TCP-порт — как правило, используется 1433.
ВНИМАНИЕ: Проверьте не занят ли порт другой программой. Для этого выполните в командной строке netstat -anopb tcp. Если порт уже занят, назначьте другой порт или выключите занимаемый процесс.
5. Перезагрузите службу SQl-сервера. Сделать это можно через раздел «Службы SQL Server«, далее в контекстном меню службы «SQL Server (OKTELL)» выберите «Перезапустить«
Как создать новый локальный сервер MS SQL?
Здравствуйте!
У меня MS SQL 2014. Подскажите пожалуйста, как создать новый локальный сервер. Хотя бы ссылку киньте, ато ничего не найду. Есть инфа про регистрацию, управление серверами через главные и т. д. и т. п., а мне тупо просто надо сервер создать новый. Как ничего не найду. Спасибо!
Добавлено через 34 минуты
может новый сервер это новый экзмепляр MSSQL?
Добавлено через 7 минут
и что такое лицензионный сервер.
Добавлено через 26 минут
В общем, у меня установлен mssql, но там имя сервака как у компа, а мне для программульки нужна база данных и чтоб эту базу вместе с программкой запускать на других компах и имени моего сервера не было. как это сделать? я представляю это в виде сервера с ролями пользователей — админами, пользователями и т. п. не правильно?
Основы администрирования SQL Server
Перед тем, как запустить MS SQL Server , нужно кратко ознакомиться с основными возможностями его настройки и администрирования. Начнем с более детального обзора нескольких утилит из состава СУБД :
- SQL Server Surface Area Configuration – сюда следует обращаться, если нужно включить или отключить какую-либо возможность сервера баз данных. Внизу окна находятся два пункта: первый отвечает за сетевые параметры, а во втором можно активировать выключенную по умолчанию службу или функцию. Например, включить интеграцию с платформой .NET через запросы T-SQL :
- SQL Server Management Studio – является основным средством администрирования. В этой среде реализована возможность настройки сервера и баз данных, как через интерфейс приложения, так и с помощью запросов на языке T-SQL .
Основные настройки можно осуществить через « Обозреватель объектов », отображающий слева в окне приложения все основные элементы сервера в виде древовидного списка. Самой важной является вкладка « Безопасность ». Через нее можно настроить права и роли пользователей и администраторов для основного сервера, или отдельно для каждой базы данных:
Основная часть настроек сервера баз данных доступна в окне « Свойства сервера »:
Как видите, Microsoft SQL Server является настолько мощным средством для структуризации, хранения и модификации данных, что на его изучение потребуется много времени. А в статье мы лишь слегка углубились в основы сервера SQL .
Local MS-SQL Server 2019 with Docker, Microsoft SQL Server Management Studio for Data Base Setup
I needed to create my own local DB(data base) to test my application on before running it with the production server. Because I don’t have a Windows Server OS (operating system) to create a DB, I needed to create a virtual one to test to on my Windows 10 OS. That is where Docker and Microsoft SQL Server Management Studio come in.
This is a step by step instruction on how I was able to create the local Windows Server with Docker and how I was able to interface and setup the DB using Microsoft SQL Server Management Studio. The instructions provided will help you start if you are new to all of this.
Setup Virtual Windows 2019 Server with Docker
First we need to install Docker if not done so already. Follow the Docker installation instructions for Windows. Hyper-V Setup needs enabled for Docker to work and you may need to enable virtualization through BIOS if its disabled. After Docker installs and if it is running properly you should see a Docker Icon in the bottom right of the tool bar. If the little square blocks are moving, means that something is still loading.
Next we need to download the Windows 2019 Server Image for Docker to create the app container. Open your CMD, you may need to run it in Admin, and run:
You should see several downloads working. The specific command is the latest 2019 Windows server image that is used to create the app container. You can download all sorts of images that can be used for containers but for windows server you can find the list of Microsoft SQL Server images here.
When the download is complete, we can create a new app container to run the Windows 2019 Server. In the CMD, run the command below, renaming “nameDB” and “Password” to your own. “Password” will not work and will through an error on required password requirements.
This will create an initial container with the Windows 2019 server image installed. This setup will automatically create the super user “sa” and set the “SA_PASSWORD”. Port 1433 must be used for the local server setup. When the container is created, you should see a string at the end:
Starting Container CMD:
There are different ways to start the container but I will use the CMD for this setup. If you don’t remember your container name you can look up all the containers you have create by running:
Then you can run the container by running:
Enabling Agent XP for Remote Admin Connections:
Before we can connect to the server using Microsoft SQL Server Management Studio, we need to enable Agent XP configuration for remote admin access. I will show the CMD way but note you can Docker Desktop.
Run the following command enable commands to the container and you should see mssql@xxxxxxxxxxxx appear.
Next run the command below to configure the sqlcmd file. You will be prompt for the password that was used creating the container. If you entered the password correctly, you should see 1> in the CMD window.
Now you have enter the following lines one at a time, hitting enter after each line of text. The first command allows the display of advanced options. Otherwise we wont be able to update the two options we want to change. I will start for the first line then include the rest of the lines to enter:
Now for the rest, again typing or pasting and hitting enter after each line:
If done correctly, you will see a confirmation of the change:
Now we need to do this two more times to configure Agent XPs and remote admin connections:
Committing and Saving Modified Image:
Now we don’t what to have to repeat these steps every time we run the container so we are going to save the changes to the image itself.
Hit ctrl-c, twice to exit out of the “bash” shell.
Type exit and enter to exit out of the mssql shell.
We will need to stop our container before we can save so enter into the CMD:
Lets check the status of the container to make sure we have stopped it.
Then we want to commit the changes we made to the image. Run the command below and if completed, will show a random string at the end.
To see the committed image we want to reference use the command which will show all the images:
Now we have to update the new image with a name and tag to use since the new image is created without them. Make sure to use the new image “IMAGE ID” to update the TAG from the committed change. Example:
docker tag 12lettersOfShaCreated user/repositoryname:tagName. I used the command below to tag the new committed image. I used sa for user since I wanted to create the remote login for the sa user. The “repositoryname” must be in all lower case. The command I used:
The downloaded image is taking up 1.5 GB of hard drive space and so is our modified image so if you want to reduce the amount of drive space taken up, you can remove the downloaded image. I also want to remove the container created with the downloaded image so we can create one with the image was updated. Run the following commands to remove both.
Now create a new container with the modified image:
Since the updated image already was created with a user name and password, we don’t need to set up whole new app container with those criteria. Lets start the new app container so we can connect and create a DB!
Connecting Microsoft SQL Server Management Studio to Docker Windows Server
Open Microsoft SQL Server Management Studio so we can connect to the Docker container running Windows Server 2019 and create a DB. Once it is loaded, you should see “Connect To Server” pop up in the middle of the app. Set the following settings and click on Connect:
Server type: Database Engine
Server name: 127.0.0.1,1433
Authentication: SQL Server Authentication
Login: sa
Password: passwordYouCreated
The server name is the local IP address that is used to create the server along with the local port id. We are using the SQL Server Authentication since we don’t have the ability to use Windows Authentication yet. The user is the user name and password is the the same that was created on the initial app container.
If connected successfully, you should see the server up and running in the Object Explorer window.
Creating a Data Base
Now there isn’t a DB(data base) created yet. To do that, right-click the Databases folder and click New Database…
A New Database window will pop up and you can enter the name of the DB you want to create. I am not going to go through the different options setup so click OK after entering the DB name.
Adding Table and Rows
Now that we have a new DB, we can add a table with some rows. Expand the Databases folder, then expand the yourDB folder. My DB I created is called MyNewDB. In yourDB folder, right-click the Tables folder and click on New, then Table…
Creating Table ID Row
Before we can generate the table name and save it to the server a table ID will need to be setup. Add “id” to the Column Name and set the Data Type to “bigint” since this number could get really big depending on the amount of row data could be added.
We need to set the Table Row id as the Primary Key. To do this right-click the arrow icon to the left of the id row then click on “Set Primary Key”.
Now you should see a key Icon along with the arrow to the left of the id row.
Finally to make the “id” row auto-increment we need to setup the table Column Properties.
Scroll to you find Identity Specification and click the arrow to the left of it to expand the options.
Then click the down arrow on (Is Identity) and change to Yes.
Now the table hasn’t been saved or named yet. To do that click on the double-disk icon to save all.
This will bring up the option to name your table. I am naming mine FormData for a different instruction set. After entering the your table name, click ok.
Now you may need to click the refresh icon on the Object Explorer to see the added table.
Conclusion
As a new user, you should have the basic setup to create a local docker container with a modified Windows server setup. When the container is running in docker, Microsoft SQL Server Management Studio is used to connect to the windows server. Then DB is created with a table that has an auto-incrementing primary key id row.
There is obviously a lot more setup that could be done but I wanted to get someone new to these specific tools, like me, the basic setup to be done. Then most should be able to figure out how to look up and change things to what they would need.