SQL DBA Blog

Category: SQL Server

SQL Server Blog Archives, by Peter Whyte (Database Administrator).
Includes: Database Admin, Management Studio (SSMS), Windows Admin, PowerShell, WSL, MSSQL Backups, Restores & more…

Installing SQL Server on Linux
A post on installing SQL Server on Linux, following the Microsoft SQL Server 2019 Guide (Red Hat).

# Install SQL Server.
# Install the SQL Server Command-Line Tools.
# Connect to SQL Server Locally.
# Connect to SQL Server Remotely.

Install SQL Server

1. Get connected & download the Microsoft SQL Server 2019 Red Hat repository configuration file.
```
sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/8/mssql-server-2019.repo
```
2. Install SQL Server.
```
sudo yum install -y mssql-server
```
3. Run mssql-conf setup to choose the SQL Server Edition, SA password and accepting of the Licence Agreement.
```
sudo /opt/mssql/bin/mssql-conf setup
```
4. Check the SQL Server service by running systemctl status.
```
systemctl status mssql-server
```
5. Allow in remote 1433 connections.

I’m using Firewalld as per Microsoft’s guide – here’s my quick install notes.
```
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload
```
Install the SQL Server Command-Line Tools

1. Download the Microsoft Red Hat repo config file.
```
sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/8/prod.repo
```
2. Install ODBC driver for SQL Server (Linux) – allows BCP & SQLCMD.
```
sudo yum install -y mssql-tools unixODBC-devel
```
3. Add mssql-tools to PATH environment variable – allows tools to be run without full path.
```
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
```
Connect to SQL Server Locally

Using the PATH variable above, call sqlcmd:

Connect to SQL Server Remotely

I’m connecting to the EC2 Amazon Linux SQL Server instance via SSMS on my local machine (Windows 10).

For this to work we had to allow in remote 1433 connections (step 5 during install above), and I added my IP into the AWS Security Group.
April 27, 2020
Enabling TCP Connections in SQL Server
Some network protocols are not enabled by default in SQL Server. We need to enable the option and restart the SQL Service to allow TCP connections.

This post is a guide on how this is done:
# On Windows via SQL Server Configuration Manager.
# On Windows using PowerShell.

Enable TCP/IP SQL via Server Configuration Manager

1. Open SQL Server Configuration Manager.

2. Navigate to TCP/IP Protocols as highlighted and open Properties.

3. Enable the highlighted option within the Protocol tab.

4. Enable what you need within the IP Addresses tab.

5. Click Apply.

6. Navigate to SQL Server Services within the Configuration Manager and restart the SQL Server Service.

Enable TCP/IP SQL Server using PowerShell

I’m using a quick script gained from Microsoft Docs, tweaking to run locally as instructed and I added some visibility of the service restart.

This script requires sqlps, just run Import-Module “sqlps” for the script functions to work.
```
## Enable/Disable MSSQL TCP & Named Pipes
$smo = 'Microsoft.SqlServer.Management.Smo.'  
$wmi = new-object ($smo + 'Wmi.ManagedComputer').  

# Enable TCP Protocol for the locl machine  
$uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($uri)  
$Tcp.IsEnabled = $true
$Tcp.Alter() 

# Enable Named Pipes Protocol for the local machine
$uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Np']"  
$Np = $wmi.GetSmoObject($uri)  
$Np.IsEnabled = $true  
$Np.Alter()  

# Restart the SQL Services
$DfltInstance = $Wmi.Services['MSSQLSERVER']  
$DfltInstance.ServiceState
$DfltInstance.Stop(); 
Start-Sleep 1
$DfltInstance.Refresh();
$DfltInstance.ServiceState
Start-Sleep 1
$DfltInstance.Refresh();
$DfltInstance.ServiceState
$DfltInstance.Start();
Start-Sleep 1
$DfltInstance.Refresh();
$DfltInstance.ServiceState
```
I was able to verify by looking at SQL Server Configuration Manager, but we can also do with the script info above.
April 25, 2020
What’s New in the SQL Server 2019 Installation Wizard

The release of SQL Server 2019 happened during the first day of Microsoft Ignite (4^th November). Up until now, I’ve been using the release candidates for tests… so it’s good to have it all officialised!

Here’s some notes during my first official SQL Server 2019 installation, using the wizard on a Windows Server host;

# Features.
# MAXDOP.
# Max Memory.

Features

It’s nice to note down the full feature list for reference…

MAXDOP

This is one of 2 changes I love. More visibility on an important configuration option, CPU parallelism for queries that run on your SQL Server instance.

Max Memory

The out of the box max memory configuration commonly brings chaos… RAM contention is an issue (other things running on the server) and it can be triggering your monitoring alerts (constant 90%+ RAM usage). So it’s amazing to have more visibility on this too!

November 16, 2019
Running Scheduled Tasks in SQL Server Express
Running scheduled tasks in SQL Server Express has to be done differently due to the limitations of the Edition. As well as having a maximum database size of 10GB and 1GB consumable RAM, Express does include the SQL Server Agent. So, If you need a task to run on a time-based schedule, you’ll have to look outside of SQL Server.

We can schedule our jobs using the Windows Task Scheduler, which will run a PowerShell or DOS script that calls the sqlcmd.

The DOS approach is explained within the following steps:
# Install sqlcmd and locate files
# Create .bat sqlcmd script
# Create Scheduled Task SQLCMD (.BAT) Script

The .bat script for this post will execute Ola Hallengren’s Index Maintenance, which I’ll set to run weekly on the MS SQL Express instance. Ola’s index maintenance stored procedure is assumed to be on the SQL Server for this task.

Locate and Install Sqlcmd

The sqlcmd utility is installed with SSMS, or you can download it separately for Windows/Linux environments – MS Docs: Download and install sqlcmd

If you already have SSMS installed, you can find the directory of sqlcmd with the help of this Microsoft Documentation Page – File Locations for Default and Named Instances of SQL Server (search ‘sqlcmd’ after selecting your SQL Version when on the page).

My sqlcmd.exe and bcp.exe clients are in:
-> C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Bin

The only thing we need for now is to have it installed on the same machine we’re running the script from.

I wrote another blog post on sqlcmd if you would like more information and examples of executing SQL commands with sqlcmd.exe.

Create .BAT Script SQLCMD Script

Below you’ll see -Q being used and everything within the command is within quotes. The -o parameter is also being used to forward output messages to a text file.
```
sqlcmd -Q "EXEC master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_updatestats'; EXEC dbo.IndexOptimize @Databases = 'ALL_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 30, @FragmentationLevel2 = 50, @pagecountlevel = 1000" -o "c:\logs\index_maintenance.txt"
```
Copy this into a notepad and save it as a .bat file, in any folder.

example .bat file for sqlcmd (notepad++)

example .bat file for sqlcmd (windows explorer)

I’m going to create a new folder for the output log file. This is specific to the index_maintenance solution I’m applying here.

$c:\logs$

Next, I’m running the index_maintenance.bat file to verify it’s working as expected, and by viewing the output log file.

$c:\logs index maintenance$

Now we have our script ready and tested, we can proceed to the next step and schedule the execution of the script.

Create Scheduled Task SQLCMD (.BAT) Script

We have our script file ready, and we know where SQLCMD is. It is time for us to schedule the script as a Task in Windows Task Scheduler. This should be a more familiar area for Windows Admins.

1. Open Task Scheduler.

2. Right-click and select Create New Task as shown below.

3. Within the General tab of the prompted window, change the following:
–> Name / Description, more information the better for visibility.
–> The user account that the task runs as to a new local service account I created for this. Remember, the chosen account requires Logon As Batch and MSSQL permissions (sysadmin for quickness here).
–> Run whether the user is logged on or not.

4. Click the Triggers tab and hit New to set the schedule.

The schedule below is set to run every Sunday at 02:00. Set this up to your desired days/times and click OK once done.

5. Click on the Actions tab and click New to add our script as the action.

This is where we add our .bat script file location. Browse to the folder you saved your script and click OK when after selecting the file.

6. Click on the Settings tab and review options. The “Stop task if it runs for longer than” setting is of particular note as the default is 3 days.

7. Now that we have reviewed all tabs within the Create Task window, click OK to create the task.

You will be prompted to enter a username and password if the task is set to use an AD service account.

This Scheduled Task has been created and is awaiting its next execution, which is early Sunday morning.

If we run this now we can watch our indexes being rebuilt in SQL Server by running sp_who, sp_who2 or sp_whoisactive from SSMS.

Your script might not be running for long enough to verify this way. This maintenance solution script run time can vary as it depends on the level of index fragmentation on all SQL Server databases. It’s been run 64 times on my test databases and now only taking a second to complete.

We can also verify the SQLCMD script ran successfully by checking the Scheduled Task History and log output.
October 2, 2019
sqlcmd in SQL Server

The sqlcmd utility allows Transact SQL to be run at the command prompt using ODBC to execute SQL batches for MS SQL Server.

This tool comes with SQL Management Studio, so if you have SSMS installed you can locate the sqlcmd exe file and call it via a Windows command terminal.

If you are running Linux, macOS or Docker, you can download sqlcmd and bcp from this Microsoft Documentation page (sqlcmd for linux).

Version notes for sqlcmd:
# Sqlcmd version 13.1 or higher is required for Always Encrypted (-g) and AAD Auth (-G)
# Microsoft ODBC Driver for SQL Server 17.6.1 or higher required for Azure Active Directory (AAD) Authentication and configure a Kerberos environment for macOS/Linux ODBC connectivity

The version information above may drift out of date – always look at Microsoft Docs for more up-to-date information.

In this blog post, I’m showing a demo of finding sqlcmd.exe on my local computer and then showing simple sqlcmd query examples.

– Find (or Download) SQLCMD.exe
– SQLCMD Query Examples
– Exit SQLCMD

Find (or Download) SQLCMD.exe

SQLCMD download (MS Docs) – link

SQLCMD and BCP are installed on your computer when you install SQL Server Management Studio. SSMS is the GUI application that most SQL Servers users use to query MSSQL, so there’s a good chance you might already have sqlcmd.

If you do already have SSMS installed, Microsoft has a documentation page for helping us find SQL file locations for each of the many apps. Search ‘sqlcmd’ after selecting your SQL version when on the page.

My sqlcmd.exe & bcp.exe files are in c:\program files\microsoft sql server\client sdk\odbc\130\tools\binn\

For quickness, we can type cmd.exe in the top address bar:

This will bring up a command prompt and the terminal will be as per the directory we executed this from in Windows Explorer. We don’t actually need CMD to be scoped into the same directory as sqlcmd.exe to use it though.

This command prompt is running as the User account I’m logged into Windows with. You can change which user runs CMD (shift-click app) to use another Windows account, or use -U / -P for SQL Server Authentication.

I’m running sqlcmd on the same machine as a default SQL Server instance, and my local user account has sysadmin permissions. That means I’m able to open sqlcmd without any parameters as shown below;

If connecting to a named instance of SQL Server we need to add the following parameter: -S <server-name\instance-name>

SQLCMD Query Examples

Now that we are in sqlcmd in the terminal window we can run any SQL statement our user permissions allow. This demo user is a member of the sysadmin role so can perform any action on the SQL Server instance.

I’m going to run a few commands to check basic SQL host and server information:
– @@SERVERNAME : Get SQL Server Hostname
– @@SERVICENAME : Get SQL Server Instance Name
– @@VERSION : Get SQL Server Product Version

Next, I will verify which user I’m connected to SQL Server as using SUSER_NAME().

Good, I know which server I’m on and who I am.

I now want to find out which databases are on this SQL Server. We can query sys.databases to list all databases on the SQL Instance.

There are 4x system databases on this SQL host and 1x User Database, Adventureworks. The famous Test database provided by Microsoft.

Lets now look at what tables are inside the Adventureworks database by querying sys.tables.

The above SQL commands we have run through here give us a view of what we take for granted in a GUI application. Now that we know the SQL Server host we are on and the databases/tables we can start on some ETL or admin work!

Exit SQLCMD

When you are done running queries in sqlcmd, you can exit and get back to terminal by typing and running exit

October 1, 2019
Changing Schemas in SQL Server
This is a post on how-to on changing the schema of a table in SQL Server, moving it from one schema to another using the ALTER SCHEMA [tableName] TRANSFER SQL statement.

During my time as a SQL Database Administrator, assisting with a task like this is a rare event. If you are more of a Database Developer, then you’ll probably need to do this more often.

To change the schema of a table in MSSQL, run through the following:
# Create New Schema in SQL Server
# Change Schema for a Table in SQL Server

If you are making this change to a Production SQL Server, you should ensure all SQL queries that use tables included in this change are updated with the new schema name.

Create New Schema SQL Server

When performing changes in SQL Server, we should search for the relevant Microsoft Documentation on the subject. In this case, see Create Database Schema: Limitations and Restrictions

The SQL below will create a new schema within the [Adventureworks] database which will be the new schema we are migrating the test table to.
```
-- Create a new schema in MS SQL Server
CREATE SCHEMA [finance] AUTHORIZATION dbo;
```
Setting dbo as the owner will be fine in most cases. If you have experienced SQL Database Developers around you, they might ask for this schema to be created with an alternative schema owner.

We have our new schema created, now let’s move on to the next part for migrating a table to this schema.

Change Schema for a Table in SQL Server

To change the schema of a table in SQL Server, we use the ALTER SCHEMA <db> TRANSFER statement.

MS Docs has some examples for Transferring the ownership of a table too. They call it ‘transferring a securable‘.
```
-- Change schema of table in SQL Server
ALTER SCHEMA finance TRANSFER [dbo].[DimCurrency]
```
The schema name in the left-most part of the SQL command is the schema we are transferring to (finance).

Now that I’ve created the new Schema, I’ll refresh the tables within the SSMS Object Explorer. I see the table has changed from dbo.DimCurrency to finance.DimCurrency.

(Note: the red squigglies under the table name in the SSMS query window mean that the query hasn’t been refreshed yet. Hit CTRL + SHIFT + R to update SSMS IntelliSense)

Remember to now update all queries, and change the schema name when querying the transferred table.

I hope this guide has helped you amend the schema of a table in SQL today. If you like this post and want more random tips from an MS SQL DBA, check out my latest posts on my DBA Blog Homepage.
September 30, 2019
Adding & Removing SQL Features via Command

This is a quick guide on how to add and remove features from SQL Server via command, following the theme of recent posts.

The following areas will be covered within this post;
# Check Features Installed on a SQL Server.
# Installing a SQL Server Feature (Replication) via Command.
# Uninstalling a SQL Server Feature (Replication) via Command.

Check Features Installed on a SQL Server

A mounted SQL Server ISO is required for us to first RunDiscovery.

That’ll only take a second, and once done we can view the contents of the Summary.txt file to see the discovery result.

Installing a SQL Server Feature (Replication) via Command

The command below should be straight-forward enough to understand. See Microsoft’s documentation for more information on parameters etc.

We can verify the feature installation by running another Discovery.

Uninstalling a SQL Server Feature (Replication) via Command

Uninstalling a feature is as easy as the installation of one. Run the command as shown below.

And finally run another Discovery.

July 18, 2019
Install/Uninstall SQL Server via Command

This is a post on installing SQL Server via command; I have a Windows Server 2016 Core running on a local Hyper-V test environment, and I’m installing SQL Server 2019, then uninstalling it.

There’s 4 parts to this one;
# Download a SQL Server ISO.
# Mount the ISO on a Hyper-V Virtual Machine.
# Install SQL Server via Command.
# Uninstall SQL Server via Command.

Download a SQL Server ISO

As this is on my test environment, I’m installing the most recent SQL Server Edition available, which is SQL Server 2019 CTP 2.4 at the time of writing.

SQL Server 2019 can be downloaded here – Open it up and select to download the media.

ISO’s are good for local virtual environments (using HyperV here).

6Mbps…

We’re now ready to get that ISO mounted.

Mount the ISO on a Hyper-V Virtual Machine

1. Click Media > Insert Disk…

2. Select the ISO file.

Install SQL Server via Command

1. First, lets have a look at our drives using the Get-PSDrive cmdlet.

2. Install using your preferred parameters. I’m adding the Domain Admins group as Sysadmins for quickness.

3. We can look at the Summary.txt file using Get-Content to verify the installation has been successful.

The above shows the result of the installation was successful, and other things like where the Configuration INI file is (used for Unattended Installs).

4. Next, we should check the SQL Server services, using Get-Service with “SQL” wrapped in wildcards.

Uninstall SQL Server via Command

Uninstalling is as simple as the install, funnily enough..

Once it’s done, we can check the summary file status and services to quickly verify the uninstallation has not been unsuccessful.

June 10, 2019
“Windows could not start the SQL Server”

The “Windows could not start the SQL Server on 127.0.0.1” is a generic error message when the SQL Server service cannot start-up.

Windows Event Viewer will give you the actual error, and from there you can troubleshoot the reason for failure.

SQL Server 2019 evaluation period expired in this case. My mistake for not selecting the Developer Edition!

April 17, 2019
“Index was out of range” SQL Server Management Studio

If you try to change the order of steps in an Agent Job within SQL Server Management Studio (SSMS), you may encounter the error “Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index (mscorlib)“.

This error can occur even if you are using the latest version of SSMS. It’s unclear what the root cause of this issue is, but I have been able to resolve it for myself and some others by updating to the latest version of SSMS.

Resolve “Index was out of range” SSMS Issue

As mentioned above the resolution is to update to the latest SSMS version. For demo purposes, I’m going to show the actual error I was encountering here.

I create a new SQL Agent Job with 3 job steps:

When I try move the job step 3 (import_second_table) run order from 3 to 2, the Index was out of range error prompts:

Searching for this error online often led me to a lot of unrelated information about .NET development. If updating SQL Server Management Studio (SSMS) has not resolved this issue for you, feel free to leave a comment and we can troubleshoot here.

April 15, 2019