SQL Server 2012 Mainstream Support Ending reminder

Time sure does fly and just a reminder Mainstream Support for SQL Server 2012 ends in just over 3 months on 11th July 2017.
Ideally any new SQL Server installs should be the newest, or as close to the newest version of SQL Server as possible, to get the maximum RoE.
With SQL Server 2016 SP1, a lot of Enterprise features are now included in the other editions, so is a great option. Something to be aware of though is that Microsoft is now requiring Software Assurance to be acquired for use of a DR instance. The use of one DR instance was usually included by default in earlier versions.

Task Manager alternative for Linux

Task Manager is extremely useful for Windows DBAs, however in the Linux world the use of GUIs may be few and far between.
To your rescue is the top command
Simply open a command/terminal prompt and run top
It may appear to be very basic, but the power is in the options available via simple key presses.
Pressing h on the keyboard will show the options available to you.
So if for example you pressed c you would now see the full command/path information:
To show CPU/Memory info simply press m
To change the refresh rate press d and enter a new refresh rate
If you need to kill a problem PID, you can use k
Try out the various available options to get a real insight into the Linux server activity and press q once finished to close it.

Does the Windows firewall slow down SQL Server connectivity?

Discussions on whether to enable or to not enable the Windows firewall on internal SQL Severs can get quite heated.
This may require further testing, but initial testing shows that there is very little difference between having the Windows firewall enabled or not.
In a real simple test, a connection was attempted 30 times to a remote SQL Server with the Windows firewall enabled and another 30 times with the Windows firewall disabled.
The test consisted of a batch file recording the start/end times of the following command:

sqlcmd -S instancename -E -Q "SELECT @@SERVERNAME;"

Variations are expected due to SQL Server workloads, but never the less the times averaged out. Having the Windows firewall enabled was slightly faster with an average of 39.7 milliseconds compared to 40.8 milliseconds, which I was not expecting.
Below shows the milliseconds taken between the start/end of each test with the Windows firewall on and off:

MSDTC firewall config on a SQL server

If you’ve worked with BizTalk, you’ll know that configuring the Windows firewall can prove to be challenging when it comes to MSDTC.
The below Inbound Rules are known to work on the SQL server and are configured using wf.msc:
1) New Rule -> Custom -> All programs -> Protocol type (TCP), Local port (RPC Dynamic Ports), Remote port (All Ports)
-> Next -> Next -> Next -> Domain -> RPC_Dynamic_Ports -> Finish
2) New Rule -> Custom -> All programs -> Protocol type (TCP), Local port (RPC Endpoint Mapper), Remote port (All Ports)
-> Next -> Next -> Next -> Domain -> RPC_Endpoint_Mapper -> Finish
Also, check the following service states:

  • COM+ System Application (Manual)
  • System Event Notification Service (Auto)
  • Distributed Transaction Coordinartor (Auto)
  • Is there a service for the Linux SQL Server vNext Agent?

    As at CTP 1.4, there doesn’t appear to be one visible in Linux.
    Currently the Agent appears to be tied into the SQL Server service with no separate options for stop/start/restart.
    Interestingly, in the screen shot you can see that on this server the Agent is running under the machine account vnextvm\vnextvm$. Only SQL accounts are currently supported at this stage.

    RDP to Linux VM

    You wouldn’t typically have a default desktop on a Linux Production SQL server.
    Linux offers many different desktops and newer versions of Ubuntu e.g. 16.10, allow copy/paste and folder access between the Windows host and Linux server.
    Allow RDP TCP port 3389 using either

    sudo ufw allow in 3389/tcp

    Next add xrdp and the desktop. At this point in time the Ubuntu Unity desktop shown above is not easily available to install.

    sudo apt-get install -y xrdp
    sudo apt-get install -y mate-core mate-desktop-environment mate-notification-daemon

    Update /etc/xrdp/startwm.sh by adding mate-session. I used gksu nautilus to locate and edit the file.
    You should now be able to RDP.

    Adding SQL Server Agent to Linux

    With the release of SQL Server vNext CTP 1.4 you can now add the SQL Server Agent using the following on Ubuntu:

    sudo apt-get update 
    sudo apt-get install mssql-server-agent
    sudo systemctl restart mssql-server

    Interestingly once installed, the SQL Server Agent refused to start, even after a restart and the latest SSMS showed the SQL Server Agent as inaccessible.
    Running the below allowed me to access the SQL Server Agent in SSMS but the Agent still doesn’t start.

    sp_configure 'show advanced options','1'
    sp_configure 'Agent XPs','1'
    sp_configure 'show advanced options','0'

    Update: Not having much luck I suspected the issue may be that the server name was more than 15 characters. This is currently a reported issue by Microsoft. Starting over, I created a new VM called vnextvm and the agent installed and started this time with no issues.

    Are you ready to support SQL Server on Linux?

    If you’ve been a SQL Server DBA for the past 5,10,15 years then your exposure to Linux has likely been minimum or even non-existent.
    Consider some of the typical methods you’d use/check when investigating a SQL Server that is no-longer responding:

    Windows Linux
    Connect using RDP
    Locate and view SQL Errorlogs
    Windows Event Logs
    SQL Server Configuration Manager
    Disk Management
    Device Manager
    Task Manager
    Performance Monitor
    Local Policies
    Local Security
    Folder/File Permissions

    In the Linux column, would you know the corresponding tool?
    It’s just a matter of time before a vendor/project/Manager hands over a SQL Server on Linux for you to support and at the end of the day when there is an issue with SQL it’ll likely end up in your lap.
    So now, is a really good time to start getting up to speed with Linux support.