Friday, March 31, 2017

Linux for SQL DBA: Access like a Pro.

In all my previous posts about SQL Server on Linux I always connected to Linux box locally, but in production environment it is almost never the case.

So, how Linux gurus connect to their remote servers?

They use small utility called "PUTTY".

It is easy to get it. Just google "putty download" and you'l get it on the top:

 On the download page choose edition you prefer:

After you install that utility and run it you have to get something like this:

IMPORTANT: In order to be able to connect to your Linux box you have to have SSH client installed. for Ubuntu Linux you can use following installation command:
sudo apt-get install openssh-server openssh-client

After successful installation of SSH client you almost can connect to your box, you just have to know your Linux machine IP address.
If you do not know it, just type "ifconfig" in your terminal get it:

If you have more than one adapter as I do, use the one you can ping from your machine.
Enter that ip address in Putty, make sure you have SSH connection type selected and then press "Open" button:

Then you are supposed to get connection prompt like this:

After you enter your credentials and successfully connected you can verify that you are really in by reading SQL Server data catalog and running "sqlcmd -S localhost -U sa" command:
sudo ls -l /var/opt/mssql/data

The only difference connecting with "Putty" that in order to get another terminal window you have to run another instance of "Putty" window:

Thursday, March 30, 2017

SQL Server on Linux. Set default Data and Log folders.

Continue to get through SQL Server on Linux Configuration documentation:
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf#datadir

It is pretty clear, but still I want to prove it works and that it works MY way.

First step: You need to create two new folders for your DATA and LOG files.
I'll do it under default SQL Server folder and I'll do it by two simple commands:
sudo -u mssql mkdir /var/opt/mssql/userdata
sudo -u mssql mkdir /var/opt/mssql/userlogs
IMPORTANT:
1. To use command "mkdir" (make directory) you have to use "sudo" account, otherwise system will not allow you to do that.
2. New folders must be under ownership of "mssql" account. In order to do so we are using parameter "-u mssql".

Second step: Setting default data and log directories to the newly created folders:
sudo /opt/mssql/bin/mssql-conf set defaultdatadir /var/opt/mssql/userdata
sudo /opt/mssql/bin/mssql-conf set defaultlogdir /var/opt/mssql/userlogs

Third step: Restart SQL Server service:
sudo systemctl restart mssql-server

If there was no mistyping there shouldn't be any errors.

Creating a New Database!

Lets check how it works. Run "sqlcmd" and create new test database:
sqlcmd -S localhost -U sa
Then enter "sa" password to the prompt and create database:
1> CREATE DATABASE LinuxTest2;
2> GO

Again, if everything was done correctly, there shouldn't be any error.
To prove that during database creation data and log files were placed into correct folders will run two following command:
sudo ls -l /var/opt/mssql/userdata
sudo ls -l /var/opt/mssql/userlogs

It should return newly created database filenames with their properties.

Here is how all of that worked in my virtual box:

Wednesday, March 29, 2017

SQL Server on Linux. Set default backup directory in three steps.

Continue to get through SQL Server on Linux Configuration documentation:
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf#backupdir

It is pretty clear, but still I want to prove it works and that it works MY way.

First step: You need to create a new folder for your backup directory.
I'll do it under default SQL Server folder and I'll do it just in ONE step:
sudo -u mssql mkdir /var/opt/mssql/backup
IMPORTANT:
1. To use command "mkdir" (make directory) you have to use "sudo" account, otherwise system will not allow you to do that.
2. New "backup" folder must be under ownership of "mssql" account. In order to do so we are using parameter "-u mssql".

Second step: Setting default backup directory to the newly created folder:
sudo /opt/mssql/bin/mssql-conf set defaultbackupdir /var/opt/mssql/backup

Third step: Restart SQL Server service:
sudo systemctl restart mssql-server

If there was no mistyping there shouldn't be any errors.

Backuping!

Lets check how it works. Run "sqlcmd" and do a backup of my test database:
sqlcmd -S localhost -U sa
Then enter "sa" password to the prompt and run database backup:
1> BACKUP DATABASE [LinuxTest] TO DISK = N'LinuxTest.bak';
2> GO

Again, if everything was done correctly, there shouldn't be any error.
To prove that backup was done will run the following command:
sudo ls -l /var/opt/mssql/backup

It should return newly created backup filename and its properties.

Here is how all of that worked in my virtual box:

Tuesday, March 28, 2017

SQL Server on Linux. How to change port. Problems and Troubleshooting.

The most of those who use SQL Server don't even know that they are using default port #1433 to access their precious data.
However, to make your data just little bit less vulnerable for external intruder you can change that port within wide range of 65K addresses.

In this blog I will change SQL Server on Linux default port from #1433 to #61433.

To do so, in the Linux box I'll run following command
sudo /opt/mssql/bin/mssql-conf set tcpport 61433
Then I have to restart SQL Server to make changes to take the effect:
sudo systemctl restart mssql-server

Now we will check how it works by running following script:
SELECT local_tcp_port
FROM sys.[dm_exec_connections] 
WHERE [session_id] = @@spid;

Everything looks working. Right?
Will check how can we connect to SQL Server on Linux from outside of the box:

That error can be explained by a Server firewall, blocking our request.
We just have to allow requests on the server to go through our changed port:
(that command is applicable for Linux Ubuntu and might not work in other distributions)
sudo ufw allow 61433


As you can see, immediately after opening the port we can connect to the system from the outside.

Now, will try to perform one basic operation. Will try to read an error log by T-SQL using following:
EXEC sys.xp_readerrorlog

However, we get an execution error:

Via GUI I've got similar error:

When I've tried to read error log via PowerShell and also got error:

I've tried to read errorlog on the server itself, but result was the same and even worse, it brake the connection to the engine with error "Invalid cursor state":

Here is the corresponding message in the Error Log:

All these problems are expected. Prior experience shows that changing SQL Server port makes reading Error Log file impossible.

Besides of inability to read error log all other functions work fine.




Monday, March 27, 2017

Connect to SQLAgent on Linux via new SqlServer PowerShell module.

This post is about two very new features of SQL Server:  SqlServer PowerShell module and SQLAgent for SQL Server on Linux.

I won't cover all details of these features, just will show how we can see SQLAgent on Linux via PowerShell script.

At first we need to install SqlServer module in PowerShell. The full installation descrition you can find here: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-manage-powershell

To use SqlServer module in PowerShell you have to install it.
To run PowerShell, press "Win+R" on your keyboard, type "powershell_ise" and press "Enter".

When PowerShell ISE opens run following commands:
Import-Module SqlServer
Get-Module -Name SqlServer
When you copy-paste these commands and press F5, as the result you have to get something like this:
If you did not get that result, you can't go any further and have to troubleshoot the PowerShell module installation.

If now you run following command, you can get the list of all available cmdlets within SqlServer PowerShell module:
Get-Command -Module SqlServer

From this point we will work directly with SQL Server.
In order to establish connection you have to run following script.
The most important are 2nd and third lines:
- In second line you have to provide your SQL Server Instance address, by replacing "<your_server_instance>" by something like "192.168.58.11" or "192.168.58.11\MSSQLSERVER,1433"
- When second line runs it will ask you for SQL Server credentials !!! So, you have to enter SQL user name and it's password.
# Prompt for credentials to login into SQL Server
$serverInstance = "<your_server_instance>"
$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
As the first result of that script you get prompt window:

IMPORTANT: Passwords IS Case Sensitive!!!

As final result you have to get these three lines:

- Your edition
- Platform
- Distribution (as of today it is available only in Linux)

Now we can play around with SQL Server we've just conncted.
At first will try to see its Errorlog:
Get-SqlErrorLog -ServerInstance $serverInstance -Credential $credential -Since Yesterday | Out-GridView
As the result, you get a new grid window, in which you can filter events, would say, by word "severity"


Then, you can get the list of all your databases on the server:
Get-SqlDatabase -ServerInstance $serverInstance -Credential $credential | Out-GridView

If you have installed SQL Server on Linux version CTP 1.4 or higher, with installed SQLAgent, you can run set of following scripts:
SQL Agent general Info:
Get-SqlAgent -ServerInstance $serverInstance -Credential $credential `
| Select-Object AgentDomainGroup, AgentLogLevel, ErrorLogFile, `
    ServiceStartMode, SqlAgentAutoStart, Name

List of SQL Agent Jobs:
Get-SqlAgent -ServerInstance $serverInstance -Credential $credential `
| Get-SqlAgentJob | Out-GridView 

List of Steps within a SQL Agent job (Please note that you have to specify "-Name" parameter. In this case it is: "Test Linux Job")
Get-SqlAgent -ServerInstance $serverInstance -Credential $credential `
| Get-SqlAgentJob -Name "Test Linux Job" | Get-SqlAgentJobStep `
| Select-Object ID, Name, SubSystem, DatabaseName, Command, `
    OnSuccessAction, OnSuccessStep, OnFailAction, OnFailStep, `
    LastRunDate, LastRunDuration, LastRunOutcome `
| Out-GridView 

Schedule details for a SQL Agent job (Please note that you have to specify "-Name" parameter. In this case it is: "Test Linux Job")
Get-SqlAgent -ServerInstance $serverInstance -Credential $credential `
| Get-SqlAgentJob -Name "Test Linux Job" | Get-SqlAgentJobSchedule `
| Select-Object Name, IsEnabled, Parent, DateCreated, ActiveStartDate, `
    ActiveStartTimeOfDay, ActiveEndDate, ActiveEndTimeOfDay, `
    FrequencyInterval, FrequencyRecurrenceFactor, `
    FrequencyRelativeIntervals, FrequencySubDayInterval, `
    FrequencySubDayTypes, FrequencyTypes 

SQL Agent Job History (Please note that you have to specify "-JobName" parameter. In this case it is: "Test Linux Job")
Get-SqlAgentJobHistory -ServerInstance $serverInstance -Credential $credential `
-JobName "Test Linux Job"`
| Select-Object RunDate, RunDuration, StepID, StepName, RunStatus, SqlSeverity, Message `
| Sort-Object -Property RunDate, StepID | Out-GridView 

You can easily filter that list by word "error" to see any problematic items:

As mentioned in the beginning, that is far not full guide on SqlServer PowerShell module and on SQL Server Agent on Linux. Both areas are very new and not well documented yet.
For more information on SQL Server on Linux see: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-overview

Thursday, March 23, 2017

SQL Server on Linux. How "set-collation" works.

NOTE: This blog post has been done on SQL Server vNext CTP 1.4 on Ubuntu 16.04. The behavior of SQL Server in the later production release might be different.

In this blog post I want to show cool "set-collation" functionality in SQL vNext on Linux.
I'll do it in two parts.
The first part will show conventional T-SQL operations with collation.
In the second part I will change collation of the entire server.

Part 1.

At first will show the version:
SELECT @@Version;
GO
Microsoft SQL Server vNext (CTP1.4) - 14.0.405.198 (X64)
Mar 11 2017 01:54:12
Copyright (C) 2016 Microsoft Corporation. All rights reserved.
on Linux (Ubuntu 16.04.1 LTS)

Now show current collations in my system:
SELECT name, collation_name FROM sys.databases;
GO

Will create a new table in "LinuxTest" DB and try to insert Cyrillic text in Non-Unicode column "DefaultText" with a result:
USE LinuxTest;
GO
DROP TABLE IF EXISTS tbl_Collation_Test;
GO
CREATE TABLE tbl_Collation_Test(
 ID INT IDENTITY(1,1),
 Test_Name VARCHAR(50),
 DefaultText VARCHAR(50),
 UnicodeText NVARCHAR(50)
);
GO
INSERT INTO tbl_Collation_Test(Test_Name, DefaultText, UnicodeText)
VALUES ('Default Collation "SQL_Latin1_General_CP1_CI_AS"', 
 N'Самый простой тестовый текст',
 N'Самый простой тестовый текст');
GO
SELECT * FROM tbl_Collation_Test;
GO

As you can see we totally lost our text in non-Unicode column and text in Unicode column was perfectly preserved.

Now I will change collation of the entire "LinuxTest" DB:
GO
USE master;  
GO  
ALTER DATABASE LinuxTest COLLATE SQL_Ukrainian_Cp1251_CI_AS;  
GO  
SELECT name, collation_name FROM sys.databases;
GO

Collation of "LinuxTest" DB has changed to "SQL_Ukrainian_Cp1251_CI_AS"

Will try to insert Cyrillic text the second time:
USE LinuxTest;
GO
INSERT INTO tbl_Collation_Test(Test_Name, DefaultText, UnicodeText)
VALUES ('New Collation "SQL_Ukrainian_CP1251_CI_AS"', 
 N'Второй по простоте текст',
 N'Второй по простоте текст');
GO
SELECT * FROM tbl_Collation_Test;
GO

Despite of changed collation on the database level we still lost our non-Unicode value. 
Why is that?
Lets check collations on individual columns:
SELECT name, collation_name FROM sys.columns
WHERE object_id = OBJECT_ID('tbl_Collation_Test')
GO
As you can see, changing collation of "LinuxTest" DB does not change collation within existing "tbl_Collation_Test" table.

Will try to fix that:
ALTER TABLE tbl_Collation_Test
ALTER COLUMN DefaultText VARCHAR(50) 
COLLATE SQL_Ukrainian_CP1251_CI_AS;  
GO  

SELECT name, collation_name FROM sys.columns
WHERE object_id = OBJECT_ID('tbl_Collation_Test')
GO
As you can see "DefaultText" Non-Unicode column now is set for "SQL_Ukrainian_CP1251_CI_AS" collation.
Will try to insert Cyrillic text again:
INSERT INTO tbl_Collation_Test(Test_Name, DefaultText, UnicodeText)
VALUES ('Another try of "SQL_Ukrainian_CP1251_CI_AS"', 
 N'Третий самый простой текст',
 N'Третий самый простой текст');
GO
SELECT * FROM tbl_Collation_Test;
GO

And now that works!!!
We can save our non-Latin characters within non-Unicode column, which saves us almost 50% of space for that column!

Part 2.

Now login into Ubuntu to change the entire SQL Server collation.
The entire procedure is described in Microsoft Documentation: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf#collation

1. At first run "set-collation" witin "mssql-conf" configuration module:
sudo /opt/mssql/bin/mssql-conf set-collation
2. It interactively asks for a Collation I want to setup and I enter "SQL_Ukrainian_Cp1251_CS_AS" (please note that in "Part 1" I've used Case Insensitive collation and now for the sake of test I use Case Sensitive one)

3. After entering new collation name configuration utility gave me an error by saying that SQL Server is still running and I must stop it to perform that operation.

4. Interestingly, it uncovers new way to "Start/Stop" SQL Server service by using configuration utility with "stop-service" command.

5. To save on typing I stop SQL Server by conventional Linux command:
sudo systemctl stop mssql-server

After stopping the SQL Server service I repeat steps #1 & #2:

After configuration utility successfully run it asks for starting SQL Server service:

It asks to use configuration utility's "start-service" command, but I start SQL Server by conventional Linux command:
sudo systemctl start mssql-server

Now I get back to Windows and SSMS and check my server collations:
USE master;
GO
SELECT name, collation_name FROM sys.databases;
GO
As you can see, when I changed SQL Server collation, the collation has been changed not only for system databases, but for my "LinuxTest" DB too.  It switched from "SQL_Ukrainian_Cp1251_CI_AS" to "SQL_Ukrainian_Cp1251_CS_AS"!

Look deeper:
USE LinuxTest;
GO
SELECT name, collation_name FROM sys.columns
WHERE object_id = OBJECT_ID('tbl_Collation_Test')
GO
All columns in my test table also change their collations and overwrite the individual column settings!

The final result of the testing is following:
When you change database collation for a database it does not affect existing objects.
However, when you change collation of the whole server it affects ALL existing objects in ALL databases on the server.

NOTE: This blog post has been done on SQL Server vNext CTP 1.4 on Ubuntu 16.04. The behavior of SQL Server in the later production release might be different.