Initial impressions of SQL Server v.Next Public Preview

Microsoft announced its SQL Server on Linux public preview yesterday. I’m really excited to check it out. Here are some interesting things I found during my testing. I’ll write more as I play with it further.

  • If you want to play it on Ubuntu, it needs to be 16.04 or above. I didn’t pay attention to that initially, and started installing on Ubuntu 14. Below is a typical message you would get:
    The following packages have unmet dependencies:
     mssql-server : Depends: openssl (>= 1.0.2) but 1.0.1f-1ubuntu2.21 is to be installed
    E: Unable to correct problems, you have held broken packages.
    

    Running sudo apt-get dist-upgrade brought my Ubuntu to 16.04. The install was smooth afterwards.

  • Instruction for Red Hat Enterprise Linux also works for Fedora. I tested it on Fedora 23. I think it should also work on CentOS, although I didn’t test it myself.
  • The machine needs to have at least 3.25 GB of memory. On Ubuntu, install won’t continue if that condition is not satisfied:
    Preparing to unpack .../mssql-server_14.0.1.246-6_amd64.deb ...
    ERROR: This machine must have at least 3.25 gigabytes of memory to install Microsoft(R) SQL Server(R).
    dpkg: error processing archive /var/cache/apt/archives/mssql-server_14.0.1.246-6_amd64.deb (--unpack):
     subprocess new pre-installation script returned error exit status 1
    Processing triggers for libc-bin (2.21-0ubuntu4.3) ...
    Errors were encountered while processing:
     /var/cache/apt/archives/mssql-server_14.0.1.246-6_amd64.deb
    E: Sub-process /usr/bin/dpkg returned an error code (1)
    

    On Fedora, installation finishes, but you won’t be able to start the service:

    [hji@localhost ~]$ sudo /opt/mssql/bin/sqlservr-setup 
    Microsoft(R) SQL Server(R) Setup
    
    You can abort setup at anytime by pressing Ctrl-C. Start this program
    with the --help option for information about running it in unattended
    mode.
    
    The license terms for this product can be downloaded from
    http://go.microsoft.com/fwlink/?LinkId=746388 and found
    in /usr/share/doc/mssql-server/LICENSE.TXT.
    
    Do you accept the license terms? If so, please type "YES": YES
    
    Please enter a password for the system administrator (SA) account: 
    Please confirm the password for the system administrator (SA) account: 
    
    Setting system administrator (SA) account password...
    sqlservr: This program requires a machine with at least 3250 megabytes of memory.
    Microsoft(R) SQL Server(R) setup failed with error code 1. 
    Please check the setup log in /var/opt/mssql/log/setup-20161117-122619.log
    for more information.
    
  • Some simple testing 🙂 From the output below, we learn that: 1)in sys.sysfiles, full file name is presented like “C:\var\opt\mssql\data\TestDb.mdf”; 2) Database name, at least inside sqlcmd, is not case-sensitive. By the way, login is also case-insensitive: SA is sA.
    1> create database TestDb;
    2> go
    
    Network packet size (bytes): 4096
    1 xact[s]:
    Clock Time (ms.): total       447  avg   447.0 (2.2 xacts per sec.)
    1> use testdb;
    2> go
    Changed database context to 'TestDb'.
    
    Network packet size (bytes): 4096
    1 xact[s]:
    Clock Time (ms.): total         3  avg   3.0 (333.3 xacts per sec.)
    1> select filename from sys.sysfiles
    2> go
    filename                                                                                                                                                                                                                                                            
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    C:\var\opt\mssql\data\TestDb.mdf                                                                                                                                                                                                                               
    C:\var\opt\mssql\data\TestDb_log.ldf                                                                                                                                                                                                                                
    
  • I then did a quick testing of advanced feature, like Clustered Columnstore Index (CCI). Yes, it’s available in SQL Server for Linux!
    1> create table Person (PersonID int, LastName nvarchar(255), FirstName nvarchar(255))
    2> go
    
    Network packet size (bytes): 4096
    1 xact[s]:
    Clock Time (ms.): total        28  avg   28.0 (35.7 xacts per sec.)
    1> create clustered columnstore index Person_CCI on Person;
    2> go
    
    Network packet size (bytes): 4096
    1 xact[s]:
    Clock Time (ms.): total        25  avg   25.0 (40.0 xacts per sec.)
    1> 
    
    Network packet size (bytes): 4096
    1 xact[s]:
    Clock Time (ms.): total         1  avg   1.0 (1000.0 xacts per sec.)
    
    

Overall, it looks pretty nice! I’ve got to say, I’m really impressed with Microsoft’s embrace of Linux. By the way, if you use Windows 10, I recommend Bash on Ubuntu on Windows. It’s in beta, but it works for me pretty well so far.

Stay tuned for more. I’ll definitely write more as I play with this new toy!

No 32-bit for SQL Server 2016 Express

I’ve learned that SQL Server 2016 Standard and Enterprise Editions no longer provide 32-bit. But I do wonder about SQL Server 2016 Express Edition. It’s different in that it’s free, and mostly geared toward lightweight usage, people who are learning, etc. So perhaps it still offers 32-bit?

After some upgrade work to one SQL Server 2008 R2 Express 32-bit, I can tell you with real experience that SQL Server 2016 Express does NOT have 64-bit either.

So the latest Express edition that has 32-bit is SQL Server 2014. Like Allan Hirt, I also say good riddance. It’s time to move on.

SQL Server best practice: grant permissions to per-service SID

Since Windows Server 2008/Windows Vista, from SQL Server 2008 onward, SQL Server installation process automatically generates per-service security identifier (SID). Whenever possible, it is recommended to grant rights to this service SID for security reasons, instead of your SQL Server’s startup account, which typically is domain user account.

For example, for performance reasons, I always want to SQL Server to have the following rights: Instant File Initialization and Lock Pages in Memory. The former enables instantaneous data (not log) file growth; whereas the later prevents Windows system from paging SQL Server data to virtual memory on disk.

Those rights can be granted via the Local Security Policy application, secpol.msc. Navigate to Security Settings -> Local Policies -> User Rights Assignment, you’ll find them there. Please note that Instant File Initialization is actually called “Perform volume maintenance tasks”.

Before service SID was introduced, I always granted those rights to SQL Server’s startup account. In my case it was typically a domain\user account. With the introduction of service SID, SQL Server’s resource access rights is the sum of both its startup account and service SID. Therefore it is recommended to grant rights to service SID, for obvious security reasons.

To prove that’s the case, let’s conduct the following experiment. For default instance of SQL Server, its service SID is NT Service\MSSQLSERVER. For named instance, its service SID is NT Service\MSSQL$InstanceName. Please note instant file initialization, once enabled, only works for SQL Server data files, not logs.

1. Assume your SQL Server instance is running under a domain\user account without “Perform volume maintenance tasks”;
2. Run the following code:

dbcc traceon(3004,3605,-1)
go
 
create database TestDb
go
 
exec sp_readerrorlog
go
 
drop database TestDb
go
 
dbcc traceoff(3004,3605,-1)
go

Pay attention to the output of “exec sp_readerrorlog”. You should see something similar to this:

2016-05-19 23:39:35.830 spid51 Zeroing C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDb.mdf from page 0 to 1024 (0x0 to 0x800000)
2016-05-19 23:39:35.890 spid51 Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDb.mdf (elapsed = 66 ms)

3. Now using secpol.msc, grant your service SID, in my case, NT Service\MSSQLSERVER, the right of “Perform volume maintenance tasks”;
4. Restart SQL Server instance;
5. Repeat step 2, you shouldn’t see entry similar to the one listed above in the error log, indicating that SQL Server has the combined rights of its startup account and its service SID.

By the way, this also applies to data and log folder permissions. Only grant data and log folder permissions to service SID, not its startup account. I have automated that process here.

Happy learning!

Grant full permission to Windows folders and files

For a Windows application or service, it is often necessary for either the user or service startup account to have full control to its folders and files. In most cases, this is transparent to the user. It is generally being taken care of by various delegation mechanisms behind the scene. However, this can be a problem during application upgrade or migration, when one rearranges disk drive letters or mount point names, or moves things from one server to another. To resolve this, one could manually go to each file and folder property page through Windows Explorer, then the Security tab, then the Edit… button, then the Add… button, etc. This method isn’t too bad if only a handful of files and folders are involved. But it is easy to see that manual method gets old really quickly, is error prone, and does not scale.

For example, say you are doing SQL Server migration for a data warehouse instance, where there are multiple mount points and hundred of database files due to filegroup usage and table partitions. During the migration process, you’ll move them to a new location and attach those database files. To ensure SQL Server service SID has proper permission is no small task and we need an automated way.

Given this is Windows, I started searching around for a PowerShell solution. You’d think this is a common problem, and therefore it shouldn’t be too difficult to find a solution. But that’s not the case so I wrote my own. Have fun with it!

Notes:

  • Make sure you start PowerShell with “Start as Administrator”. This is to get around the issue that the creator and permission granter are different identities;
  • The first thing the script does is to give ownership to the Administrators group. During execution, you’ll see output like:
    SUCCESS: The file (or folder): “x:\MountPoints\A1” now owned by the administrators group.
    that is to be expected.
  • The script’s execution is recursive, meaning all sub-folders and files are covered.

Without further ado, here is the PowerShell function:

function grantFullPermission ($folderpath, $userAccount) {
    Invoke-Expression "takeown /f $folderpath /r /a /d Y"
    $aclWork = (Get-Item $folderPath).GetAccessControl("Access")
    $ssaf = New-Object system.Security.AccessControl.FileSystemAccessRule($userAccount, "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")
    $aclWork.SetAccessrule($ssaf)
    Set-ACL $folderPath $aclWork }

As an example, to grant proper permission to 3 folders listd in the code, you can run the following script as administrator:

function grantFullPermission ($folderpath, $userAccount) {
    Invoke-Expression "takeown /f $folderpath /r /a /d Y"
    $aclWork = (Get-Item $folderPath).GetAccessControl("Access")
    $ssaf = New-Object system.Security.AccessControl.FileSystemAccessRule($userAccount, "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")
    $aclWork.SetAccessrule($ssaf)
    Set-ACL $folderPath $aclWork }

$folders = @("x:\MountPoints\A1", "x:\MountPoints\A2\", "x:\MountPoints\B1")

$folders | foreach {
    grantFullPermission $_ 'NT SERVICE\MSSQLSERVER' }

You could also make this a short program that takes two parameters: a folder and an account. Remember to quote the parameters if they have space character.

param(
[parameter(Mandatory=$true)]$DirectoryPath,
[parameter(Mandatory=$true)]$AccountName)

function grantFullPermission ($folderpath, $userAccount) {
    Invoke-Expression "takeown /f $folderpath /r /a /d Y"
    $aclWork = (Get-Item $folderPath).GetAccessControl("Access")
    $ssaf = New-Object system.Security.AccessControl.FileSystemAccessRule($userAccount, "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")
    $aclWork.SetAccessrule($ssaf)
    Set-ACL $folderPath $aclWork }

grantFullPermission $DirectoryPath $AccountName