Configuring SQL Server LocalDB
SQL Server LocalDB is a uselful lightweight version of the SQL Server database system that allows for easy delevopment of SQL Server related applications.
There are a number of versions which can be used, and they can all be installed and used on the same system at the same time. This feature makes it easy to develop applications on the same computer that target different versions of SQL Server.
Version downloads
For localdb 2012 and 2014, select the correct version of SQLLocalDB.msi for your operating system (x32 or x64).
For localdb 2016, download the SQL Server Express install, run it, select the Download Media buton, select the SQLLocaldb option and save it.
Instance default database locations
The default placement of instances of SQL Local DB are in the AppData folder of the user:
Command line
SQL LocalDB has a command line tool for managing database instances.
Versions installed
To see the versions currently installed:
Instances and names
The installed instances can be seen asking for info. Providing an instance name will print detailed info about the instance.
Creating instances
If more than one version of SQLLocalDB is installed and it is desired to use them all, you can create specific instances of each version with names that indicate what version the instance is — instead of just having the default “MSSQLLocalDB” instance.
Here is a sample for creating named instances of all three. The “-s” parameter causes the instance to be started as well.
Starting instances
An instance can be started or stop witht he command line.
Removing instances
Instances can also be removed.
Connecting to an instance in Visual Studio
To connect to the LocalDB instance in Visual Studio, open the Server Explorer tool window, add a connection, specfify the following connection string (altered for your instance name), select the database from the dropdown, “Test” the connection, and select Ok.
Connecting to an instance in SQL Server Management Studio
To connect to an instance of LocalDB using SSMS, use the following line (altered for your instance name) in the connection name:
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.
SQL-Ex blog
Как создать и сконфигурировать связанный сервер для подключения к MySQL в SQL Server Management Studio
В этой статье дается инструкция, которая по шагам приведет вас к созданию связанного сервера в SSMS для подключения к базе данных MySQL.
- Установка драйвера ODBC для MySQL
- Конфигурирование драйвера ODBC для подключения к базе данных MySQL
- Создание и конфигурирование связанного сервера с использованием драйвера ODBC
Установка ODBC-драйвера для MySQL
ODBC — это аббревиатура Open Database Connectivity (Connector). Программа разработана Microsoft в 1990-е. В целом она представляет собой API (программный интерфейс приложения) для доступа к системам баз данных.
Для ОС, которые не являются Windows, используется JDBC (Java Database Connectivity).
Перед установкой ODBC-драйвера для MySQL на Windows проверьте, что вы имеете последнюю версию Microsoft Data Access Components (MDAC), и в вашей системе установлен пакет Microsoft Visual C++ 2013 Redistributable Package.
Драйверы ODBC для MySQL можно загрузить и установить по этой ссылке. Имеется две версии драйверов ODBC для Windows, которые могут быть установлены в зависимости от приложения, с которым будут использоваться:
- mysql-connector-odbc-8.0.17-win32.msi для 32-битных приложений
- mysql-connector-odbc-8.0.17-winx64.msi для 64-бтных приложений
После нажатия кнопки Next появится диалог лицензионного соглашения. Если вы согласны с ним, нажмите переключатель «I accept the terms in the license agreement», затем кнопку Next:
В диалоговом окне Setup Type выберите опцию Typical и нажмите кнопку Next:
В диалоге Ready to Install the Program показано что и где будет установлено. Нажмите кнопку Install для установки ODBC-драйвера.
Спустя пару секунд установка драйвера ODBC для MySQL завершится:
То, что драйвер ODBC для MySQL установлен на машине, можно проверить в панели управления:
Другой способ проверки — окно диалога ODBC Data Source Administrator:
Проверьте на вкладке Drivers диалогового окна ODBC Data Source Administrator, имеются ли драйверы MySQL ODBC:
Сконфигурируйте драйвер ODBC для подключения к базе данных MySQL
Для подключения к базе данных MySQL с использованием драйверов ODBC в диалоговом окне ODBC Data Source Administrator на вкладке System DSN нажмите кнопку Add:
В диалоге Create New Data Source выберите MySQL ODBC Driver и нажмите кнопку Finish:
В диалоге MySQL Connector/ODBC Data Source Configuration:
в области Data Source Name введите желаемое имя источника данных. В области Description дайте описание источника данных, если необходимо.
Используйте метод соединения TCP/IP Server или Named Pipe для подключения к MySQL, выбрав подходящий переключатель.
В данном примере выбран переключатель TCP/IP Server. В текстовой области напечатайте имя хоста или IP-адрес сервера MySQL. По умолчанию именем хоста служит localhost, а IP-адрес — 127.0.0.1. В области Port введите порт TCP/IP, который слушает сервер MySQL. Порт по умолчанию — 3306.
В области User напечатайте имя пользователя, под которым выполняется подключение к базе данных MySQL, а в области Password введите пароль пользователя. В выпадающем списке Database выберите базу данных, для которой устанавливается соединение:
Для тестирования корректности конфигурирования подключения к базе данных MySQL нажмите кнопку Test. Если соединение было установлено, появится следующее сообщение:
Кроме того, имя источника данных появится на вкладке System DSN диалога ODBC Data Source Administrator:
Создание и конфигурирование связанного сервера при использовании ODBC-драйвера
Теперь, когда ODBC-драйвер для MySQL установлен и сконфигурирован для подключения к базе данных MySQL, можно начать конфигурирование связанного сервера в SSMS для подключения к MySQL.
Перейдите в SSMS, в браузере объектов в ветке Server Objects (объекты сервера) выполните щелчок правой кнопкой на папке Linked Servers (связанные серверы) и выберите в меню вариант New Linked Server (новый связанный сервер):
Откроется диалоговое окно New Linked Server. Здесь будет вводиться конфигурационная информация для подключения к MySQL:
В текстовой области Linked server на вкладке General введите имя, которое будет использоваться для связанного сервера (например, MYSQL_SERVER).
Выберите переключатель Other data source, и из списка Provider выберите пункт Microsoft OLE DB Provider for ODBC Drivers:
В текстовую область Product name введите любое подходящее имя. Для Data source следует ввести имя источника данных ODBC:
На вкладке Security щелкните переключатель Be made using this security context, а в областях Remote login и With password введите имя пользователя, который есть в экземпляре сервера MySQL (выбранном в качестве источника данных), и его пароль:
На вкладке Server Options установите поля RPC и RPC Out в значение True:
В случае, когда обе эти опции не установлены в true, то при выполнении подобного кода:
может возникнуть следующая ошибка:
Подробно об опциях на вкладках Security и Server Options можно прочитать на странице How to create and configure a linked server in SQL Server Management Studio.
После установки всех опций в диалоге New Linked Server нажмите кнопку OK. Вновь созданный связанный сервер должен появиться в папке Linked Servers:
Прежде чем начать запрашивать данные из базы данных MySQL, перейдите в папку Providers в папке Linked Servers, выполните щелчок правой кнопкой на провайдере MSDASQL и выберите из контекстного меню команду Properties:
В диалоге Provider Options установите флажки Nested queries, Level zero only, Allow in process, Support ‘Like’ operator:
Например, если не установить флажок Allow in process, то выполнение подобного кода: