About Me - Who is DR DBA?

Hello. My name is Rudy Panigas and I am a Senior Microsoft SQL Server Database Administrator (DBA) with over 10 years of experience. I created this blog to share my views, knowledge and to discuss Microsoft SQL server in order to help out others in this field. Please test these scripts before using. I do not warranty and do not take any responsibility for it, its misuse or output it creates. You can reach me at this email address: sqlsurgeon@outlook.com

Tuesday, May 20, 2014

Antivirus SQL Server Recommended Setting

Hello Again,

This blog will show the Microsoft Support recommended antivirus (AV) setting for SQL Server. Each version of SQL Server will be listed below with different setting for each version. Below are the directories and file-name extensions that must be exclude from AV scanning.

Common AV Settings for all versions of SQL Server
Regardless of the version of SQL Sever the exclusion below will apply.
SQL Server data files
These files usually have one of the following file-name extensions:

  • .mdf
  • .ldf
  • .ndf

SQL Server backup files
These files frequently have one of the following file-name extensions:

  • .bak
  • .trn

Full-Text catalog files
Default instance: Program Files\Microsoft SQL Server\MSSQL\FTDATA
Named instance: Program Files\Microsoft SQL Server\MSSQL$instancename\FTDATA

Trace files

  • .trc
These files usually have the .trc file-name extension. These files can be generated either when you configure profiler tracing manually or when you enable C2 auditing.

SQL audit files (for SQL Server 2008 or later versions)

  • .sqlaudit
These files have the .sqlaudit file-name extension. For more information, see the following topic in SQL Server Books Online

SQL query files

  • .sql
These files typically have the .sql file-name extension and contain Transact-SQL statements.
The directory that holds Analysis Services data
Default location C:\Program Files\Microsoft SQL Server\MSSQL.X\OLAP\Data
For more specifics contact the DBA team for actual locations as this can change based on the installation and requirements.

Clustering Setting
Exclude the following locations from being scanned

  • Quorum drive letter
  • C:\Windows\Cluster
  • DTC drive letter

SQL Server 2012
Apply the recommendation mentioned in the Common AV Settings for all versions of SQL Server section and the exclusions below.
%ProgramFiles%\Microsoft SQL Server\MSSQL11.<Instance Name>\MSSQL\Binn\SQLServr.exe
%ProgramFiles%\Microsoft SQL Server\MSRS11.<InstanceName>\ReportingServices\ReportServer
\Bin\ReportingServicesService.exe
%ProgramFiles%\Microsoft SQL Server\MSAS11.<Instance Name>\OLAP\Bin\MSMDSrv.exe

SQL Server 2008 R2
Apply the recommendation mentioned in the Common AV Settings for all versions of SQL Server section and the exclusions below.
%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.<Instance Name>\MSSQL\Binn\SQLServr.exe      %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.<Instance Name>\ReportingServices\ReportServer
\Bin\ReportingServicesService.exe
%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.<Instance Name>\OLAP\Bin\MSMDSrv.exe

SQL Server 2008
Apply the recommendation mentioned in the Common AV Settings for all versions of SQL Server section and the exclusions below.
%ProgramFiles%\Microsoft SQL Server\MSSQL10.<Instance Name>\MSSQL\Binn\SQLServr.exe
%ProgramFiles%\Microsoft SQL Server\MSSQL10.<Instance Name>\ReportingServices\ReportServer
\Bin\ReportingServicesService.exe
%ProgramFiles%\Microsoft SQL Server\MSSQL10.<Instance Name>\OLAP\Bin\MSMDSrv.exe

SQL Server 2005
Apply the recommendation mentioned in the Common AV Settings for all versions of SQL Server section and the exclusions below.
%ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SQLServr.exe
%ProgramFiles%\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\Bin\ReportingServicesService.exe
%ProgramFiles%\Microsoft SQL Server\MSSQL.2\OLAP\Bin\MSMDSrv.exe

Please let me know if you find any additional information I should add.

Thanks,

Wednesday, March 26, 2014

SQL Server 2012 on a Windows 2012R2 Cluster – Things to watch out for

While performing a new installation of SQL Server 2012 on a Windows 2012R2 cluster, I learned a few things along the way and I want to share them with you.

There are 3 main area to watch out when building the cluster and SQL Server

1.      Integration Services (SSIS) is not cluster aware. You can get SSIS working in the cluster. However, if you have a SSIS package that is executing and the cluster fails over (from active to passive node) the SSIS package will not continue to execute. In fact you have to restart the SSIS package execution. Therefore you need to build some logic in you package to detect if the SSIS package execution was completed or not and if not, who to roll back the changes and re execute it. There is also a fix to allow remote connection to SSIS which can break too

2.      Reporting Services (SSRS) will not start after applying patches. It appears that the patch can/does break SSRS but it’s an easy fix 

3.  Always On Availability Groups does not work between clusters. That is, if you have a cluster (say 2 nodes) and you want to replicate with Always On Availability Groups to another cluster (again say a 2 node cluster), this will not work! There is no documentation from Microsoft support about this. So be aware of this limitation

Below are the links that help with the issues notes above and others I found useful when building a Windows 2012R2 cluster and SQL Server 2012.

Integration Services (SSIS) in a Cluster is not Cluster Aware

Configure the Integration Services Service as a Cluster Resource

Grant Access to the Integration Services Service

Connect to a Remote Integration Services Server

Cannot Start SQL Server Reporting Services after applying an update

Before Installing Failover Clustering

SQL Server Failover Cluster Installation

Create a New SQL Server Failover Cluster (Setup)

Error during Installation of an SQL Server Failover Cluster Instance

Please let me know if you find any other useful information and/or links in this topic.

Thanks and enjoy.


Rudy