There are several ways to manage SQL Server vNext CTP1 on Linux. This guide will walk you through the steps to configure and manage SQL Server on Red Hat and Ubuntu Linux. The following section of the article provide a quick overview of different management tools and techniques with pointers to more resources.
We have already covered how to install SQL Server on Red Hat and Ubuntu Linux in earlier article.
mssql-conf
The mssql-conf tool configures SQL Server on Linux.
Transact-SQL
Almost everything you can do in a client tool can also be accomplished with Transact-SQL statements. SQL Server provides Dynamic Management Views (DMVs) that query the status and configuration of SQL Server. There are also Transact-SQL commands for database management tasks. You can run these commands in any client tool that supports connecting to SQL Server and running Transact-SQL queries. Examples include sqlcmd, Visual Studio Code, and SQL Server Management Studio.
SQL Server Management Studio on Windows
SQL Server Management Studio (SSMS) is a Windows application that provides a graphical user interface for managing SQL Server. Although it currently runs only on Windows, you can use it to remotely connect to your Linux SQL Server instances. For more information on using SSMS to manage SQL Server, see Use SSMS to Manage SQL Server on Linux.
PowerShell
PowerShell provides a rich command-line environment to manage SQL Server on Linux. For more information, see Use PowerShell to Manage SQL Server on Linux.
Configure SQL Server on Linux with mssql-conf
mssql-conf is a configuration script that installs with SQL Server vNext CTP1 for both Red Hat Enterprise Linux and Ubuntu. You can use this utility to set the following parameters:
- TCP port: Change the port where SQL Server will listen for connections.
- Default data directory: Change the directory where the new SQL Server database data files (.mdf).
- Default log directory: Changes the directory where the new SQL Server database log (.ldf) files are created.
- Default dump directory: Change the directory where SQL Server will deposit the memory dumps and other troubleshooting files by default.
- Default backup directory: Change the directory where SQL Server will send the backup files by default.
- Set traceflags: Set the traceflags that the service is going to use.
- Set collation: Set a new collation for SQL Server on Linux.
The following sections show examples of how to use mssql-conf for each of these scenarios.
Change the TCP port
This option will let you change the TCP port where SQL Server will listen for connections. By default, this port is set to 1433. To change the port, run the following commands:
Run the mssql-conf script as root with the "set" command for "tcpport":
sudo /opt/mssql/bin/mssql-conf set tcpport sudo systemctl restart mssql-server
When connecting to SQL Server now, you will need to specify the port with a comma (,) after the hostname or IP address. For example, to connect with SQLCMD, you would use the following command:
sqlcmd -S localhost, -U test -P test Change the default data or log directory location
This option will let you change the location where the new database and log files are created. By default, this location is /var/opt/mssql/data. To achieve this, follow these steps:
Create the directory where the new database's data and log files will reside. For example, we will use /tmp/data:
sudo mkdir /tmp/data
Change the owner and group of the directory to the "mssql" user:
sudo chown mssql /tmp/data
sudo chgrp mssql /tmp/dataUse mssql-conf to change the default data directory with the "set" command:
sudo /opt/mssql/bin/mssql-conf set defaultdatadir /tmp/datasudo systemctl restart mssql-server
Troubleshooting: Move the tempdb data directories to this new location. The service will not start if it can't restore the tempdb database files. Move them to the new folder using the following commands:
sudo mv /var/opt/mssql/data/tempdb.mdf /tmp/data
sudo mv /var/opt/mssql/data/templog.ldf /tmp/data
Restart the SQL Server service again:
sudo systemctl restart mssql-server
Now all the database files for the new databases created will be stored in this new location. If you would like to change the location of the log (.ldf) files of the new databases, you can use the following "set" command:
sudo /opt/mssql/bin/mssql-conf set defaultlogdir /tmp/log
This command also assumes that a /tmp/log directory exists, and that it is under the user and group "mssql".
Change the default dump directory location
This option will let you change the default location where the memory and SQL dumps are generated whenever there is a crash. By default, these files are generated in /var/opt/mssql/log.
To set up this new location, use the following commands:
Create the directory where the dump files will reside. For example, we will use /tmp/dump:
sudo mkdir /tmp/dump
Change the owner and group of the directory to the "mssql" user:
sudo chown mssql /tmp/dump
sudo chgrp mssql /tmp/dump
Use mssql-conf to change the default data directory with the "set" command:
sudo /opt/mssql/bin/mssql-conf set defaultdumpdir /tmp/dump
Restart the SQL Server service as instructed by the configuration utility:
sudo systemctl restart mssql-serverChange the default backup directory location
This option will let you change the default location where the backup files are generaqted. By default, these files are generated in /var/opt/mssql/data.
To set up this new location, use the following commands:
Create the directory where the backup files will reside. For example, we will use /tmp/backup:
sudo mkdir /tmp/backup
Change the owner and group of the directory to the "mssql" user:
sudo chown mssql /tmp/backup
sudo chgrp mssql /tmp/backup
Use mssql-conf to change the default backup directory with the "set" command:
sudo /opt/mssql/bin/mssql-conf set defaultbackupdir /tmp/backup
Restart the SQL Server service as instructed by the configuration utility:
sudo systemctl restart mssql-serverEnable/Disable traceflags
This option will let you enable or disable traceflags for the startup of the SQL Server service. To enable/disable a traceflag use the following commands:
Enable a traceflag using the following command. For example, for Traceflag 1234:
sudo /opt/mssql/bin/mssql-conf traceflag 1234 on
You can enable multiple traceflags by specifying them separately:
sudo /opt/mssql/bin/mssql-conf traceflag 2345 3456 on
In a similar way, you can disable one or more enabled traceflags by specifying them and adding the "off" parameter:
sudo /opt/mssql/bin/mssql-conf traceflag 1234 2345 3456 off
Restart the SQL Server service as instructed by the configuration utility for the changes to apply:
sudo systemctl restart mssql-serverChange the SQL Server collation
This option will let you change the collation value to any of the supported collations:
Run the "set-collation" option and follow the prompts:
sudo /opt/mssql/bin/mssql-conf set-collation
The mssql-conf utility will try to restore the databases using the specified collation and restart the service. If there are any errors, it will roll-back the collation to the previous value.
The following is a list of supported collations:
- Albanian_BIN
- Arabic_BIN
- Chinese_PRC_BIN
- Chinese_PRC_CI_AS
- Chinese_PRC_CS_AS
- Chinese_Taiwan_Stroke_BIN
- Chinese_Taiwan_Stroke_CI_AS
- Chinese_Taiwan_Stroke_CS_AS
- Cyrillic_General_BIN
- Czech_BIN
- Danish_Norwegian_CS_AS
- Finnish_Swedish_CS_AS
- Greek_BIN
- Hebrew_BIN
- Hungarian_BIN
- Icelandic_CS_AS
- Japanese_BIN
- Japanese_CI_AS
- Japanese_CS_AS
- Korean_Wansung_BIN
- Korean_Wansung_CI_AS
- Korean_Wansung_CS_AS
- Latin1_General_BIN
- Latin1_General_CI_AS
- Latin1_General_CS_AS
- Macedonian_FYROM_90_BIN
- SQL_1Xcompat_CP850_CI_AS
- SQL_AltDiction_Cp1253_CS_AS
- SQL_AltDiction_Cp850_CI_AI
- SQL_AltDiction_Cp850_CI_AS
- SQL_AltDiction_Cp850_CS_AS
- SQL_AltDiction_Pref_CP850_CI_AS
- SQL_Croatian_Cp1250_CI_AS
- SQL_Croatian_Cp1250_CS_AS
- SQL_Czech_Cp1250_CI_AS
- SQL_Czech_Cp1250_CS_AS
- SQL_Danish_Pref_Cp1_CI_AS
- SQL_EBCDIC037_CP1_CS_AS
- SQL_EBCDIC273_CP1_CS_AS
- SQL_EBCDIC277_CP1_CS_AS
- SQL_EBCDIC278_CP1_CS_AS
- SQL_EBCDIC280_CP1_CS_AS
- SQL_EBCDIC284_CP1_CS_AS
- SQL_EBCDIC285_CP1_CS_AS
- SQL_EBCDIC297_CP1_CS_AS
- SQL_Estonian_Cp1257_CI_AS
- SQL_Estonian_Cp1257_CS_AS
- SQL_Hungarian_Cp1250_CI_AS
- SQL_Hungarian_Cp1250_CS_AS
- SQL_Icelandic_Pref_Cp1_CI_AS
- SQL_Latin1_General_Cp1250_CI_AS
- SQL_Latin1_General_Cp1250_CS_AS
- SQL_Latin1_General_Cp1251_CI_AS
- SQL_Latin1_General_Cp1251_CS_AS
- SQL_Latin1_General_Cp1253_CI_AI
- SQL_Latin1_General_Cp1253_CI_AS
- SQL_Latin1_General_Cp1253_CS_AS
- SQL_Latin1_General_Cp1254_CI_AS
- SQL_Latin1_General_Cp1254_CS_AS
- SQL_Latin1_General_Cp1255_CI_AS
- SQL_Latin1_General_Cp1255_CS_AS
- SQL_Latin1_General_Cp1256_CI_AS
- SQL_Latin1_General_Cp1256_CS_AS
- SQL_Latin1_General_Cp1257_CI_AS
- SQL_Latin1_General_Cp1257_CS_AS
- SQL_Latin1_General_Cp1_CI_AI
- SQL_Latin1_General_Cp1_CI_AS
- SQL_Latin1_General_Cp1_CS_AS
- SQL_Latin1_General_Cp437_BIN
- SQL_Latin1_General_Cp437_CI_AI
- SQL_Latin1_General_Cp437_CI_AS
- SQL_Latin1_General_Cp437_CS_AS
- SQL_Latin1_General_Cp850_BIN
- SQL_Latin1_General_Cp850_CI_AI
- SQL_Latin1_General_Cp850_CI_AS
- SQL_Latin1_General_Cp850_CS_AS
- SQL_Latin1_General_Pref_CP1_CI_AS
- SQL_Latin1_General_Pref_CP437_CI_AS
- SQL_Latin1_General_Pref_CP850_CI_AS
- SQL_Latvian_Cp1257_CI_AS
- SQL_Latvian_Cp1257_CS_AS
- SQL_Lithuanian_Cp1257_CI_AS
- SQL_Lithuanian_Cp1257_CS_AS
- SQL_MixDiction_Cp1253_CS_AS
- SQL_Polish_Cp1250_CI_AS
- SQL_Polish_Cp1250_CS_AS
- SQL_Romanian_Cp1250_CI_AS
- SQL_Romanian_Cp1250_CS_AS
- SQL_Scandinavian_Cp850_CI_AS
- SQL_Scandinavian_Cp850_CS_AS
- SQL_Scandinavian_Pref_Cp850_CI_AS
- SQL_Slovak_Cp1250_CI_AS
- SQL_Slovak_Cp1250_CS_AS
- SQL_Slovenian_Cp1250_CI_AS
- SQL_Slovenian_Cp1250_CS_AS
- SQL_SwedishPhone_Pref_Cp1_CI_AS
- SQL_SwedishStd_Pref_Cp1_CI_AS
- SQL_Ukrainian_Cp1251_CI_AS
- SQL_Ukrainian_Cp1251_CS_AS
- Thai_BIN
- Thai_CI_AS
- Thai_CS_AS
- Turkish_BIN
- Ukrainian_BIN
Use SSMS to Manage SQL Server on Linux
This topic introduces SQL Server Management Studio (SSMS) and walks you through a couple of common tasks. SSMS is a Windows application, so use SSMS when you have a Windows machine that can connect to a remote SQL Server instance on Linux.
SQL Server Management Studio (SSMS) is part of a suite of SQL tools that Microsoft offers free of charge for your development and management needs. SSMS is an integrated environment to access, configure, manage, administer, and develop all components of SQL Server running on-premises or in the cloud, on Linux, Windows or Docker on macOS and Azure SQL Database and Azure SQL Data Warehouse. SSMS combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels.
SSMS offers a broad set of development and management capabilities for SQL Server, including tools to:
- configure, monitor and administer single or multiple instances of SQL Server
- deploy, monitor, and upgrade data-tier components such as databases and data warehouses
- backup and restore databases
- build and execute T-SQL queries and scripts and see results
- generate T-SQL scripts for database objects
- view and edit data in databases
- visually design T-SQL queries and database objects such as views, tables and stored procedures
Install the newest version of SQL Server Management Studio (SSMS)
When working with SQL Server, you should always use the most recent version of SQL Server Management Studio (SSMS). The latest version of SSMS is continually updated and optimized and currently works with SQL Server vNext on Linux. To download and install the latest version, see . To stay up-to-date, the latest version of SSMS prompts you when there is a new version available to download.
Create and manage databases
While connected to the master database, you can create databases on the server and modify or drop existing databases. The following steps describe how to accomplish several common database management tasks through Management Studio. To perform these tasks, make sure you are connected to the master database with the server-level principal login that you created when you set up SQL Server vNext CTP1 on Linux.
Create a new database
- Start SSMS and connect to your server in SQL Server vNext CTP1 on Linux
- In Object Explorer, right-click on the Databases folder, and then click *New Database..."
- In the New Database dialog, enter a name for your new database, and then click OK
The new database is successfully created in your server.
Drop a database
- Start SSMS and connect to your server in SQL Server vNext CTP1 on Linux
- In Object Explorer, expand the Databases folder to see a list of all the database on the server.
- In Object Explorer, right-click on the database you wish to drop, and then click Delete
- In the Delete Object dialog, check Close existing connections and then click OK
The database is successfully dropped from your server.
Use Activity Monitor to see information about SQL Server activity
The tool is built-in into SQL Server Management Studio (SSMS) and displays information about SQL Server processes and how these processes affect the current instance of SQL Server.
- Start SSMS and connect to your server in SQL Server vNext CTP1 on Linux
- In Object Explorer, right-click the server node, and then click Activity Monitor
Activity Monitor shows expandable and collapsible panes with information about the following:
- Overview
- Processes
- Resource Waits
- Data File I/O
- Recent Expensive Queries
- Active Expensive Queries
When a pane is expanded, Activity Monitor queries the instance for information. When a pane is collapsed, all querying activity stops for that pane. You can expand one or more panes at the same time to view different kinds of activity on the instance.
Use PowerShell on Windows to Manage SQL Server on Linux
This topic introduces SQL Server PowerShell and walks you through a couple of examples on how to use it with SQL Server vNext CTP1 on Linux. PowerShell support for SQL Server is currently available on Windows, so you can use it when you have a Windows machine that can connect to a remote SQL Server instance on Linux.
Install the newest version of SQL PowerShell on Windows
SQL PowerShell on Windows is included with SQL Server Management Studio (SSMS). When working with SQL Server, you should always use the most recent version of SSMS and SQL PowerShell. The latest version of SSMS is continually updated and optimized and currently works with SQL Server vNext CTP1 on Linux. To download and install the latest version, see . To stay up-to-date, the latest version of SSMS prompts you when there is a new version available to download.
Launch PowerShell and import the sqlserver module
Let's start by launching PowerShell on Windows. Open a command prompt on your Windows computer and copy and paste the commands below to launch
powershell.powershell -ExecutionPolicy bypass
SQL Server provides a Windows PowerShell module named sqlserver that you can use to import the SQL Server components (SQL Server provider and cmdlets) into a PowerShell environment or script. Copy and paste the command below at the PowerShell prompt to import the sqlserver module into your current PowerShell session:
Import-Module sqlserver -DisableNameChecking;
Type the command below at the PowerShell prompt to verify that the
sqlserver module was imported correctly:Get-Module -Name "sqlserver"
PowerShell should display information similar to what's below:
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Manifest 20.0 sqlserver {Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupList...
Script 0.0 SqlServerConnect to SQL Server and get server information
Let's use PowerShell on Windows to connect to your SQL Server vNext instance on Linux and display a couple of server properties.
Copy and paste the commands below at the PowerShell prompt. When you run these commands, PowerShell will:
- display the Windows PowerShell credential request dialog that prompts you for the credentials (SQL username and SQL password) to connect to your SQL Server vNext CTP1 instance on Linux
- load the SQL Server Management Objects (SMO) assembly
- create an instance of the object
- connect to the
Serverand display a few properties
Remember to replace
# Prompt for credentials to login into SQL Server
$serverInstance = ""
$credential = Get-Credential
# Load the SMO assembly and create a Server object
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $serverInstance
# Set credentials
$server.ConnectionContext.LoginSecure=$false
$server.ConnectionContext.set_Login($credential.UserName)
$server.ConnectionContext.set_SecurePassword($credential.Password)
# Connect to the Server and get a few properties
$server.Information | Select-Object Edition, HostPlatform, HostDistribution | Format-List
# done
PowerShell should display information similar to what's shown below:
Edition : Developer Edition (64-bit)
HostPlatform : Linux
HostDistribution : UbuntuExamine SQL Server error logs
Let's use PowerShell on Windows to examine error logs connect on your SQL Server vNext instance on Linux. We will also use the
Out-GridView cmdlet to show information from the error logs in a grid view display.
Copy and paste the commands below at the PowerShell prompt. These commands may take a few minutes to run and do the following:
- display the Windows PowerShell credential request dialog that prompts you for the credentials (SQL username and SQL password) to connect to your SQL Server vNext CTP1 instance on Linux
- use the
Get-SqlErrorLogcmdlet to connect to the SQL Server vNext instance on Linux and retrieve error logs sinceyesterday - pipe the output to the
Out-GridViewcmdlet
Remember to replace
# Prompt for credentials to login into SQL Server
$serverInstance = ""
$credential = Get-Credential
# Retrieve error logs since yesterday
Get-SqlErrorLog -ServerInstance $serverInstance -Credential $credential -Since Yesterday | Out-GridView
# done
Conclusion
In the above steps, we have covered how to configure and manage SQL Server on Red Hat and Ubuntu Linux.
No comments: