Monthly Archives: October 2016

SQL Server Security – Installation

–by Ginger Keys

Much of the responsibility of a DBA is to keep the SQL Server environment stable and healthy, to ensure data integrity and availability, and to make sure the data is safe and secure. There are many vulnerabilities to SQL Server, and many available features to safeguard and protect the data. SQL Server security is a giant topic and there are many facets to consider. We’ll discuss several SQL security topics in later articles covering some of the following areas: physical security, OS security, SQL configuration, user management, and application security.


Data security is at the forefront of everyone’s concerns lately with hackers obtaining sensitive data from big retailers, financial institutions, and even government organizations. Some data is intentionally compromised, while in other situations the data is compromised accidentally. Some security threats are internal to the organization, while others are external threats.

To begin this journey into SQL server security, I’ll start with SQL Server installation.

Physical Environment

Let’s assume you have already taken precautions to put your SQL server(s), backup drives, storage devices, and any other physical devices into a secure environment. This means locked rooms and restricted access to those rooms and any other hardware and network devices. A room that protects and suppresses flooding or fire is preferable. And if you can, secure your database backups at an off-site location. A natural disaster can be detrimental, and a disgruntled or careless employee can be disastrous to an organization if given access to these mission critical assets.


Reduce Surface Area

During the installation of SQL Server, chose the least amount of features and install only the necessary components. In other words if you don’t need Analysis Services, Integration Services, or Reporting Services, don’t install it. You can always come back later and install features you may need in the future. The more components of SQL Server you have installed on your server, the more vulnerable your server is and the more opportunity for someone to attack.


If you do use Reporting Services, its best to install that on a separate server from your database server. Also it is not advised to install SQL Server on the same machine as IIS or your web server, as these have historically been vulnerable to attack.

Service Packs / Patches

Download and install the most up to date service pack and hotfixes available during installation. Also after initial installation check for and install these to make sure there are no holes in your security. Microsoft frequently releases security fixes, updates, service packs and hotfixes to resolve security threats and vulnerabilities, so be diligent in applying these to your environment. After SQL is installed, it is always prudent to download and install any updates to a test system prior to deploying to an existing production server, and make sure you backup your productions databases before applying updates.

Service Accounts

Its best to assign the service accounts running SQL Server a non-user domain account with least amount of privileges. Don’t use the built-in Windows accounts – Network Service or Local System. These built in accounts have access to Windows and a compromise into the SQL system could cause the OS to be compromised as well, giving the bad guys access to the machine and its resources.

The SQL Server Agent service account should be different from the other SQL Server service accounts because SQL Server Agent account requires permission in the system admin role. The other service accounts do not need this elevated privilege.

Only Enable Necessary Services

The only SQL Server services that should be enabled are the SQL Server service, and the SQL Server Agent service. Of course if you install the SSAS, SSIS, or SSRS features, you will enable the services associated with these installed features.

Use SQL Server Configuration Manager to disable the following services:

SQL Server VSS Writers service – used by Volume Shadow Copy service (VSS). Enable this if it is actively being used.

Active Directory Helper service – SQL will automatically enable and disable this as needed.

SQL Server Browser service (if you use fully qualified connection strings). The browser service is a Windows service and provides connection information to incoming requests.

If you are not installing SSRS, SSAS, or SSIS, these services should not appear in list of services in SQL Server Configuration Manager.


Don’t Use Default TCP/IP Ports

Default ports are entry points into SQL Server and are vulnerable to hacking attempts. Using a different port other than the well-known default (1433) conceals the entry point to SQL Server. This can be set in SQL Server Configuration Manager after installation. Changing the port number doesn’t prevent hackers from attacking, but will at least conceal the entryway.


Don’t Install SQL on Domain Controller

It is possible to install SQL on a domain controller, but for security reasons it is not advised. SQL server failover clusters as well as AlwaysOn groups are not supported on domain controllers. And you will not be able to create security groups or provision SQL service accounts on a domain controller…setup will fail.

Disable Unused Protocols

Shared Memory, Named Pipes, TCP/IP, and VIA (VIA protocol is deprecated and will be removed in a future version of SQL Server) – disable the network protocols that are not needed. Enable whichever network protocol you decide to use for your SQL Server, and disable the others.


Disable xp_cmdshell

It is best not to enable xp_command shell. This is disabled by default, and should remain disabled. Occasionally there will be legitimate reasons to use xp_command shell, but this should be done very carefully and sparingly. This feature lets you execute commands against the Windows host and can open access to the OS itself, if hacked. A better alternative might be to use command EXEC or use CLR instead. Click here for more info .

Increase Server Log Files

By default, SQL contains 7 error log files (one current, and six archives). Error logs contain critical information about your SQL server. A new log is created each time the SQL service starts, and the oldest archived log gets deleted. For security reasons it is best practice to keep a large number of error logs, depending on the space you have available. Someone with access to your SQL server could execute sp_cycle_errorlog and regenerate the logs at a rate that could ‘delete’ their malicious activity or cover up the fact that they have gained access to your system. If you have highly sensitive data or stringent auditing requirements you can increase the number of retained error logs up to 99, just be sure to monitor your disk space.



This article covers some basic best practices during installation to make your SQL Server secure. This is just the tip of the iceberg when it comes to securing your SQL environment. There are always exceptions to the rules, but these recommendations should help to give you guidelines in setting up your SQL server. Our next article will discuss controlling access to your SQL environment, including authentication, roles, permissions, and related topics that can help ensure your SQL server is protected.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!


Large MSDB From Hidden sysxmitqueue Table

— by Lori Brown @SQLSupahStah

Recently, I was checking on some of the servers that I manage and found that one had an 8GB msdb database. While that is not too bad it is kind of large so I figured that there must be something that needed to be cleaned up. I ran the Disk Usage by Top Tables report expecting to see a table that was huge to leap out at me. However, this time there was nothing. As a matter of fact, it looked like the largest table only had about 187MB of data in it. So, what in the world was going on?

I did some looking around and found that there is a hidden and undocumented table named sysxmitqueue that can sometimes become huge if you have event notifications set up. If there are errors in how the notification is set up, there will be lots of error messages in the hidden table and the transmission_queue view.

Run this query to find out of you have msdb bloat from the sysxmitqueue table:

USE msdb


— check for size of sysxmitqueue table

SELECT object_name(i.object_id) AS TableName,

i.[name] AS IndexName,

(sum(a.total_pages)*8)/1024 AS TotalSpaceMB,

(sum(a.used_pages)*8)/1024 AS UsedSpaceMB,

(sum(a.data_pages)*8)/1024 AS DataSpaceMB

FROM sys.indexes i

INNER JOIN sys.partitions p

ON i.object_id = p.object_id

AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a

ON p.partition_id = a.container_id

WHERE object_name(i.object_id) = ‘sysxmitqueue’

GROUP BY i.object_id, i.[name]


In my case, I found that this table had almost 8GB of data in it. Bingo!! Gotta clean it up now. Once again after doing some research, I found that lots of people have had problems with this table. Some say to set a new broker like so:



But that will cause problems if you do it in a production system because the service broker id will be different than is being used by anything else that has distributed conversations. If you do this, you could leave conversations stranded.

There is also a way to end conversations that takes a little time but will not cause issues if you are careful. I found that I could not query the sysxmitqueue at all but found that info from this table is also found in the sys.transmission_queue ( view. You can query it easily.

— get list of conversations

SELECT DISTINCT conversation_handle, to_service_name

FROM sys.transmission_queue

I used the above query to get a list of the conversations that needed to be cleaned up. The to_service_name column is the service name you created while setting up event notifications. You can use values from the conversation_handle column to tell an “END CONVERSATION…” statement ( which conversation exactly to end.

— end open conversations


If you have a lot of open conversations, running this manually can take a while but you won’t nuke anyone else’s conversations this way.

The last thing I need to do is figure out why my event notification is not ending conversations correctly. That will be another post later so….


For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!

AlwaysOn 2016 – Maintenance

— by Ginger Keys

SQL Server AlwaysOn provides a fairly robust environment for disaster recovery; however you can never be too careful when it comes to technology and protecting your data. Regular maintenance routines (i.e. backups, etc.) need to be set up and performed on an ongoing basis, even when you have your databases in an AlwaysOn Availability Group.

AlwaysOn allows some maintenance routines to be performed on the secondary replicas, but determining which maintenance to perform on what replica can be somewhat confusing. We will discuss the following basic maintenance routines and where to execute them.

  • Backups
  • Integrity Checks
  • Index Maintenance
  • Update Statistics


Even though there is a copy of your database on the secondary replica(s), you should still continue to back up your databases and transaction logs on a regular basis.   Backups can be very resource intensive by putting strain on I/O and CPU especially when using compression. Because of this, offloading your backup operations to a secondary replica can greatly improve the performance of your primary production replica.

With an AlwaysOn group, you have several options of where to back up your databases as shown below. You can choose your backup preference when you set up your AlwaysOn group, or you can go back later and set your backup preferences through the AG properties.


Prefer Secondary – backups should occur on a secondary replica except when the primary replica is the only replica online. In that case, the backup should occur on the primary replica. This is the default behavior.

Secondary Only – backups will happen only on a replica that is designated as a secondary, and will execute in the order of the backup priority you set. Backups will not occur on the primary replica.

Primary – backups will only occur on the replica currently designated as primary.

Any Replica – backups will occur on the replica with the highest backup priority setting.

If you perform backups on your primary replica, Full, Differential, and Tlog backups can be run normally. If you perform backups on a secondary replica, there are some differences to be aware of:

  • You must configure your AlwaysOn group to specify where you want your backups to occur. You can do this through the New Availability Group setup GUI (as shown in picture above), or by using TSQL script (for more info click here These backup preferences will apply to both Full and Tlog backups.
  • The automated backup preference setting has no impact on ad-hoc backups. Manual ad-hoc backups will occur on whatever replica you perform the backup.
  • If you create your backup jobs through the Maintenance Plan, create the jobs on all replicas that have any intention of potentially performing backups. The job will automatically include the scripting logic that calls and checks the sys.fn_hadr_backup_is_preferred_replica function, and will not execute if the replica is not the preferred backup location. The job will not fail, it simply won’t execute. And in the event of failover the job will already exist on the replica the AlwaysOn Group has failed over to, and will execute accordingly.
  • Full Backups run on secondary replicas are copy only backups. Regular backups are not allowed. Copy-only backups do not impact the log chain or clear the differential bitmap.
  • Differential backups are not allowed on secondary replicas.
  • Tlog Backups only support regular backups on a secondary replica (copy only is not allowed). Tlog backups can be taken on any replica regardless of synchronization mode, and the log chain will remain consistent.
  • If databases exist in your SQL instance that are not part of the AlwaysOn Group, create separate maintenance jobs for those databases apart from the AlwaysOn databases so that the sys.fn_hadr_backup_is_preferred_replica function is not invoked. This way your non-AlwaysOn databases will not get lumped in with the AlwaysOn databases. Having separate maintenance routines for separate groups of databases can get a little hairy, as well as managing the jobs that need to run on each replica…for excellent guidance on this click here
  • Since the instances on the AlwaysOn replicas do not share an msdb database backups will not show up in the msdb backup tables.  This is important to know in the event you have to perform audits, or need to research backup history.


Integrity Checks

Perform Integrity Checks on all replicas if possible.

There are many differing opinions in the IT world on where to run the DBCC CHECKDB in an AlwaysOn Group.   Some believe integrity checks should be run on the secondary only, some believe it should be run wherever your database backups are happening, and some believe it should be run on all replicas.

SQL allows integrity checks to be offloaded to any of the secondary replicas in order to prevent resource contention on the primary replica, which keeps performance optimized. This is a good idea in theory, however if there is corruption in the data files of the primary database (assuming you have offloaded your integrity checks to a secondary) it will not be detected by the integrity check.   Even though data in the databases is the same across all replicas, the actual data files reside on the separate disk subsystems of each replica. The integrity check runs against the data files on each disk subsystem, and if your hardware or a file is corrupt on one of your replicas the integrity check run on another replica will not catch it.

Corruption on a primary does not propagate to the secondary because complete database pages are not sent, only the tlog records which only describe the physical changes made to the pages…they don’t contain the actual pages. Performing an integrity check on a primary replica doesn’t detect corruption on a secondary and vise-versa.   They are separate databases with their own data files kept in sync through transaction logs that describe the physical changes.

If your organization is extremely sensitive to performance for your AlwaysOn group, we recommend you restore a copy of your database backups to a server not in your AlwaysOn group (like a test server) and run the integrity checks there. This will provide a consistency check of your data, will ensure your backups are viable, and will eliminate overhead of running CHECKDB on the primary.

Index Maintenance

Perform Index Maintenance on primary replica

Because secondary databases are read-only, index maintenance must be performed on the primary replica. Index maintenance generates large amounts of logged changes by design and consequently will result in large transaction logs being replicated to the secondaries. Also index maintenance can cause the primary log files to grow large because the log cannot be truncated until redo has completed the changes in all secondary replicas.

Some ways to reduce resource contention for index maintenance is:

  1. Schedule during off peak times if possible
  2. Break up maintenance into smaller phases, spread out over time period
  3. Use MAXDOP to limit the number of processors used for the operation
  4. Rebuild/reorganize based upon specified criteria:
    1. Ignore heaps or small tables
    2. Rebuild/reorganize based upon fragmentation levels acceptable to your environment

If you are fortunate enough to have a large maintenance window to be able to rebuild your indexes on a daily basis, there is not much need to update statistics as rebuilding indexes does this by design. The frequency of your maintenance routines will depend on each individual SQL environment. As a general rule with our clients, we rebuild indexes once per week and update statistics every day…but again, your environment may require something different.

Update Statistics

Update Statistics on the primary replica.

SQL Server creates statistics on database tables and indexes that gives the query optimizer information about the distribution of data. This allows SQL to compile the most efficient query plan in order to improve query performance and run the queries as quickly as possible.

In an AlwaysOn Group of databases, statistics that are created and updated on the primary replica are persisted to the secondary replicas as part of the data replication of the transaction logs records.

But what if you’re using your secondaries for read-only operations and the secondary databases need different statistics than the primary? Databases on the secondary replicas are restricted to read-only access so statistics cannot be created on them! If the secondary replicas cannot create statistics, then performance of read-only workloads can suffer.

Thankfully SQL will create and maintain statistics on the secondary replicas in tempdb by using the suffix _readonly_database_statistic appended to the name of the temporary statistic. This keeps it separate from the permanent statistics that have been sent over from the primary. These temporary statistics can only be created by SQL Server, and you need to allow auto update and auto creation of statistics.

For more information on SQL statistics, click here


This article covers a high-level overview for performing some basic maintenance on your AlwaysOn databases. These are suggestions based on a simple AlwaysOn environment. There are always exceptions to these rules and circumstances where a system is set up in a more complex manner.   For example, you may want Tlog backups to occur on a different replica than the Full backups. You may have your AlwaysOn group also involved in log shipping or some other functionality that requires more or different maintenance routines.   There are methods to accommodate every environment, but is beyond the scope of this article. Hopefully these topics have given you a basic foundation for maintaining your AlwaysOn environment.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!