Monday, April 10, 2017

SQL Server on Linux: Specifying File Path. Possible problems.

NOTE: Please be aware that all scripts work only as of publishing date and with SQL Server on Linux CTP 1.4. In future releases some features/bugs may disappear.

As you might know Linux file system is different from Windows.
Linux does not have drive letters like "A:\","B:\","C:\","D:\","E:\" etc.
It has only root folder: "/" and all devices, services, mapped network resources are linked as underlying sub-folders.

SQL Server files are located under Linux following folder: "/var/opt/mssql/".
Knowing that, you can reference SQL server files in that "Linux way".
However, Microsoft did extremely good job to satisfy current customers, it also translated "Linux path" in "Windows way" just by adding drive "C:\" letter instead of root folder. Now "C:\" is our root!

So, in that manner, SQL Server files will be located in "c:\var\opt\mssql\" directory!
Isn't it simple?!!!
There will be no effort at all to refurbish old SQL SQL Server code for new Linux platforms!

Here I'll give you an example of Database creation using different addressing methods:
1. I use Windows notation for data file and Linux notation for log file.
2. Because Linux systems are case sensitive it is very important to know it when you work with Linux. However, Microsoft allows you to be reluctant. You can use upper or lower case in the order you want.
Use Master
GO
CREATE DATABASE [LinuxFilePathDemo]
ON ( NAME = 'LinuxFilePathDemo_dat', SIZE = 1, MAXSIZE = 1,
 FILENAME = 'c:\var\opt\mssql\USERDATA\LinuxFilePathDemo.mdf')  
LOG ON (NAME = 'LinuxFilePathDemo_log', SIZE = 1, MAXSIZE = 1,
 FILENAME = '/var/opt/mssql/USERLOGS/LinuxFilePathDemo.ldf');  
GO  
SELECT name, type_desc, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('LinuxFilePathDemo');
GO

As you can see SQL Server accepted your request!

At that point Linux Geeks will start laughing at you:
Do you know now WHERE you files really are?

Lets check our folders:

What a surprise! Both folders are completely empty!!!!

Lets check somewhere else:
SQL server placed our file in folders with lower case "userdata" and "userlogs"!

Do you think SQL Server just converted upper case to lower case, but still reported to us that folder names "USERDATA" and "USERLOGS" in Upper case?

It is kind of true, but still wrong. SQL Server does something completely different.
It looks like if file system has two folders with similar names, which differentiate only by case than SQL Server chooses case insensitively the one, but there is no correlation which one will be chosen.

Error Case with "case" different folders.

In this case I'll show huge potential problem you could have:

Step 1. Create new folder in lower case "/var/opt/mssql/testfolder":

sudo -u mssql mkdir /var/opt/mssql/testfolder

Step 2. Create New database "Test":

CREATE DATABASE [Test] ON ( NAME = 'Test', 
FILENAME = '/var/opt/mssql/testfolder/Test.mdf')  
LOG ON (NAME = 'Testlog', SIZE = 1, MAXSIZE = 1, 
FILENAME = '/var/opt/mssql/testfolder/Test.ldf');  
GO

Step 3. Verify that new "Test" database is Online:

SELECT LEFT(name,22) Name, LEFT(state_desc,20) State 
FROM sys.databases WHERE name = 'Test';
GO

Step 4. Verify that database files are in the place where they supposed to be:

sudo ls -l /var/opt/mssql/testfolder

Step 5. Create new folder in upper case "/var/opt/mssql/TESTFOLDER"

sudo -u mssql mkdir /var/opt/mssql/TESTFOLDER

Step 6. Restart SQL Server service

sudo systemctl restart mssql-server

Step 7. Verify that new "Test" database is Online:

SELECT LEFT(name,22) Name, LEFT(state_desc,20) State 
FROM sys.databases WHERE name = 'Test';
GO
At that step we already have an error in the error log: "The system cannot find the file specified"

Step 8. Delete "Test" database:

DROP DATABASE [Test];
GO

Step 9. Check for missing database files where they are supposed to be:

sudo ls -l /var/opt/mssql/testfolder
And database files are still in the folder.

See screen shot of all 9 steps:

I'm assuming that after SQL Server restart it was looking for "Test" database files in new "/var/opt/mssql/TESTFOLDER" and not in "/var/opt/mssql/testfolder", where we actually created them.

Conclusion:

While working with SQL Server on Linux avoid "duplicated" folder and file names differentiated only by case.

Monday, April 3, 2017

SSMS Custom Query shortcuts Update.

Haven't touched that area for a long while.
Here is the link to my original post with all descriptions and implementation process:
http://slavasql.blogspot.com/2016/02/ssms-query-shortcuts.html

In this post I'll only list changes made since a year ago:

The newest version of SSMS Query Shortcuts is available HERE.

Ctrl+4 Changes/Improvements

- Fixed an issue when database collation is different from a server;

Ctrl+5 Changes/Improvements

- Added statistical performance dataset for all mounted drives used by SQL Server;
- Added column: "# of VLFs" for Log files;
- Added column: "% of CPU usage" by database;
- Added column: Database "Access state";
- Added column: Database "Mode";

Ctrl+5: Drives' stats + couple of new columns:

Ctrl+6 Changes/Improvements

- Fixed memory and "Buffer cache hit ratio, %" metrics;
- Added essential SQL Server counters' changes for a time frame capturing (Wx-option);
- Added I/O SQL Server counters' changes for a time frame capturing (Wx-option);
- CPU diagram is now reversed and in only two colors;
- Added currently set Trace Flags list data set into informational option  (I-option) ;
- Added list of SQL related Services into informational option  (I-option) ;

When you type would say "W10" (wait for 10 seconds) then select it by a cursor and press Ctrl+6 the script will capture Wait statistics, essential SQL metrics and I/O metrics. Then script waits for 10 seconds and compares previous values with the new ones and reports any difference.
That option can bring a lot of information telling you what SQL Server is doing right now and what potential bottleneck can be.

That is CPU usage diagram you get after simple "Ctrl+6". After it changed to reverse order it is easier to define timing. For instance on the diagram below you can see that SQL Server experienced very heavy CPU usage about 260-270 minutes ago.

Ctrl+8 Changes/Improvements

- Fixed diagram's scale by implementing logarithmic measuring;
- Top 10 diagram includes "Distinct Top 10" combination of MAX CPU, MAX I/O, & MAX # of executions. Number of top queries can potentially rise up to 30;

Now Query executions' diagram can have more than 10 queries and because it is in logarithmic scale now there are no hassle with circle sizing.
Axis-X represents amount of CPU used by a query
Axis-Y represents amount of I/O used by a query
Circle radius represents number of query executions.
If you move cursor to a particular circle you'll see Queries metrics
To see more details on the query you want to research, such as query text, execution plan, etc.
You have to copy-paste "query_hash" value to an editor window, select it and press Ctrl+8 again.


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