SQL Server Blogs Common Sense

Hello everyone,


It has been sometime since my last blog, however I am still here. People have asked me why hasn't my blog been updated for a while. The answer is simple. My blog is different than most. I only write about things that others have not written or ideas and solutions that I have developed because no one else has. With this outlook of my blog, the articles posted are more interesting and original.


With the release of SQL Server 2016 on Linux, I'm sure you will find many blogs in 2017. This is a new area for me and other DBAs and will relook at my previous blogs and see how they have changed in this new version.


If you are looking for information on backups, granting user access, error messages, etc. then you should look at the multitude of other blogs and sites with this information. If you are looking for common sense writing then you have found come to the right place.


Wishing you all the best in 2017 and let's hope it's ever a better year than last.


Rudy

How to Upgrade SQL Server

Hello everyone,

In this blog, I will provide an outline of recommended tasks for the upgrade to SQL Server 2014. You can use this document to also upgrade to other versions.

To ensure a successful upgrade of your SQL Server to a new version, we need to review the following areas

Pre Work – Before you start

Data Loads – Getting your data loaded

Post Work – Finalize the environment

Pre Work – Before you Start

1. Hardware and Software Requirements for Installing SQL Server 2014

https://msdn.microsoft.com/en-us/library/ms143506.aspx

Review the minimum hardware and software requirements like editions

2. The SQL Server 2014 Upgrade Advisor tool

https://msdn.microsoft.com/en-us/library/ee210467.aspx

Analyses the configuration and recommends changes that need to be made

3. Review Breaking Changes 

https://msdn.microsoft.com/en-us/library/ms143179.aspx

New changes to SQL Server may break applications, scripts and/or functions that are bases on earlier versions of SQL Server

4. Review Behavioral Changes 

https://msdn.microsoft.com/en-us/library/ms143359.aspx

Behavior changes affect how features work and/or interact in the new versions

5. Review Release Notes

https://msdn.microsoft.com/en-us/library/dn169381(v=sql.15).aspx

These notes describes the known issues with current version and should be reviewed before installation

6. Document all your findings

Record what you have learned. Update this document as you go along with the upgrade

7. Database Instant File Initialization      

https://msdn.microsoft.com/en-us/library/ms175935.aspx

Turn on this feature in the OS to help speed up the creation of databases

8. Start the installation of SQL Server

https://technet.microsoft.com/en-us/sqlserver/ff803383.aspx

Ensure that you install the latest service packs and review cumulative patches

Data Loads – Getting your Data Loaded

1. When creating a new database(s) place data files, partitions and logs on to different disk 

2. Create multiple TempDB files on to different disks (use SSD drives)

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6e193115-5b6a-47d6-869b-795ee88bd4a8/tempdb-multiple-files?forum=sqlgetstarted

3. Create an automated backup job

• Back up all databases (include system and empty databases)
• Verify that backup locations are correct
• Use built in compression

4. Load user database data by restoring from ** latest full ** backups

5. Compatibility level. Ensure that the restored databases are set to compatibility level of new installation. If not set may result in possible issues and you won't be able to take advantage of the new features

• Compatibility level for 2014  is 120
• Compatibility level for 2012  is 110
• Compatibility level for 2008 and  2008R2  is 100  
• Compatibility level for 2005 is 90

6.  Recovery Model. Document the current recovery models and ensure that the restored database are set to the same

7. Database Ownership. Change the ownership of databases once they are restored to “sa”

8. Collation of all User databases. Review the collation setting for all databases are set correctly

Post Work – Finalize the Environment

1. DBCC CHECKDB WITH DATA_PURITY 

Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range

Execute this check on “all” databases

     DBCC CHECKDB WITH DATA_PURITY;

https://msdn.microsoft.com/en-s/library/ms176064(v=sql.120).aspx

2. DBCC UPDATEUSAGE

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

Execute this check on “all” databases

    DBCC UPDATEUSAGE(db_name);

https://msdn.microsoft.com/en-us/library/ms188414(v=sql.120).aspx

3. Update Statistics

    Ensure you update statistics on all your databases

            USE db_name;

            GO

            EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';

https://msdn.microsoft.com/en-us/library/ms187348(v=sql.120).aspx

4. Refresh all Views

    Use sp_refreshview command

https://msdn.microsoft.com/en-us/library/ms187821(v=sql.120).aspx

5. Perform backups on “all” databases, yes again!

6. Check your compatibility levels

https://msdn.microsoft.com/en-us/library/bb933794.aspx

7. Update your finding document

8. Script out your logins from source and apply to new SQL Server.  Also check for orphan logins

https://gallery.technet.microsoft.com/scriptcenter/Get-logins-databases-816f66b2

9. Script out and recreate link servers, SQL jobs, SSIS packages, SSAS and SSRS and any other objects that have been missed

10. Automate Backup Databases

Perform full database backups often and create automated backup jobs

11. Review Error Logs

Ensure your error logs are clean before using SQL Server.

https://msdn.microsoft.com/en-us/library/ms187109.aspx

SQL Server Management Studios (SSMS) Not Working Properly Exception from HRESULT: 0X8002801D Type_E_LIBNOTREGISTERED

Hello all,

I have found the issue and fix to the problem I've had experiencing with SQL Server Management Studios not working on a server. 

Issue: SQL Server Management Studios (SSMS) does not work properly

           and you get the following error:

 Exception from HRESULT: 0X8002801D Type_E_LIBNOTREGISTERED

Resolution:

First check if the following is in place.

1.  Run REGEDIT

2.  Expand - HKEY_CLASSES_ROOT

3.  Expand - TypeLib

4.  Expand - (91A74EB0-EFA0-482B-B43C-35CFC74B275F)

5.  Expand - a.0

6.  You should see the following keys

        0

        FLAGS

        HELPDIR

7.  Expand - the key called 0 (zero)

8.  Expand - win32

9.  Right click on (Default)

10. You now should see the following:  

      C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\dteproperties.tlb

Next if the steps 6 - 10 are missing, create the keys as above and restart SSMS.

If the error still continues then check the path of the "dteproperties.tlb" and modify the above with the proper path

The issues is now resolved.

Thanks,

Rudy

Quick SQL Server Configuration Summary Report

Hello everyone,

I have written this script to detect and display a quick summary of your SQL Server installation/environment. 

Download this (updated March 30, 2015) script here Detail Configuration Script

To view the report, open the script in SSMS and execute.There are temp tables created and dropped at end of execution.

Here is a list of information produced in the reported.

Display SQL Server name\Instance name
Display Installation Date
Display Machine Name       
Display Instance Name
Display Edition and BIT Level
Display Production Service Pack Level       
Display Production Name          
Display Production Version
Display Logical CPU Count
Display Maximum Memory (Megabytes)
Display Minimum Memory (Megabytes)
Display IP Address
Display Port Number
Display Default Domain Name
Display Service Account name
Display Clustered Status
Display Kerberos
Display Security Mode
Display Audit Level
Display User Mode
Display Collation Type
Display SQL Server Errorlog Location
Display SQL Server Default Trace Location
Display Number of Link Servers
Display SysAdmin Members
Display ServerAdmin Members
Display Configuration setting
Display code that automatically executes on startup
Display SQL Service Status
Display Location of Database files
Display Link Servers
Display Database Collation type
Display Database Hard Drive Space Available
Display Database Information
Display Database Backup Information
Display SQL Job Status
Display SQL Mail Information
Display Database Mirroring Status
Display Database Log Shipping Status
Display Report Server (SSRS) Reports Information

Please execute on your test/development environment and verify results. 

I will be adding more to this script in the future so watch for updates. 

Please let me know you comments.

Thanks,

Rudy 

Auto Generating of Service Principal Name (SPN) Commands

Hello All,

Have you had to use Kerberos authentication and needed to create the commands for the Service Principal Name (SPN)? Then the script in this blog will help. Execute the script on the SQL Server with SQL Server Reporting Services (SSRS) and it will automatically generate the commands needed. 

If you need more details on Kerberos have a look at Rob Carrol blog. It is very good and covers how to enable Kerberos authentication for reporting services. Here is his link.
http://blogs.technet.com/b/rob/archive/2011/11/23/enabling-kerberos-authentication-for-reporting-services.aspx

Now on to my script.

First, load this script on to your server (please verify on test servers) and execute. It only creates temp tables, does not write anything to SQL Server and the temp tables are deleted once executed.

The script will automatically detects the SQL Server settings

******** Settings that have been automatically detected **************

Result for detection of SQL Server name --> SrvName
Result for detection of Service Account name -->MyDomain\svcAccount
Result for detection of Port Number --> 1433
Result for detection of default Domain Name --> MyDomain
Result for detection of cluster --> SQL Server is not clustered
Result for detection of Kerberos --> TCP is using Kerberos
Result for detection of IP Address --> 192.168.54.1


Next, the script will take the information found and create the SETSPN commands 


cls
echo '*** Automatic SQL Server Service Principal Name (SPN) ***'
echo ' '
echo 'Setting SQL Server Database SPN...please wait'
echo ' '
  
SetSPN -s "MSSQLSvc/SQLServerName:1433" "MyDomain\svcAccount"
SetSPN -s "MSSQLSvc/SrvName.MyDomain.com:1433" "MyDomain\svcAccount"


echo ' '
echo 'Setting SQL Server Reporting Services SPN...please wait'
echo ' '

SetSPN -s "http/SrvName" "MyDomain\svcAccount"
SetSPN -s "http/SrvName.MyDomain.com" "MyDomain\svcAccount"


echo ' '
echo 'Setting SQL Server Analysis Services SPN...please wait'
echo ' '

SetSPN -s "msolapsvc.3/SrvName" "MyDomain\svcAccount"
SetSPN -s "msolapsvc.3/SrvName.MyDomain.com" "MyDomain\svcAccount"

echo ' '
SetSPN -l MyDomain\svcAccount"
echo ' '

           ==> Auto SPN generation is now complete <==

Finally, copy the SetSPN statements and execute then with a Domain Admin account.  Verify that the account "MyDomain\svcAccount" has the following check on "Trust this user for delegation to any service [Kerberos only]" 

Note that once the SetSPN command is executed it may take several hours (once mine took approx. 8) before it takes effect.

Here is the link for my script
https://drive.google.com/file/d/0B07PMQYOhF2DMEJMcnpGVDg3eFE/view?usp=sharing

Thanks,

Rudy

SQL Server Performance Survival Guide

Hello and Happy New Year!

Today's blog is a link to the Microsoft Tech Net site that has an excellent guide regarding SQL Server performance. It is worth a look and a place in your browser's bookmarks.

http://social.technet.microsoft.com/wiki/contents/articles/5957.sql-server-performance-survival-guide.aspx

Below is the table of contents

Learning about Troubleshooting SQL Server Performance
Transaction Processing (OLTP)
Data Warehouse Performance
Troubleshooting Hardware Issues
Monitoring and Performance Tools
Performance Tips and Tricks
Performance Topics in SQL Server Books Online
Getting Advice and Guidance
SQL Server Customer Advisory Team
Community Resources
SQL Server Performance Blogs
SQL Server Performance Forums
SQL Server Resource Centers
SQL Server Performance Workshops
SQL Server Social Networking
Twitter
SQL Server Performance on Facebook
SQL Server Performance Books

Hope you find this useful,

Rudy

SSRS Subscription Status

Need to see the status of a report subscription in SSRS? Well now you can. This script, see download link below, will provide the following details.

• Event type
• Executed by
• Inactive or active 
• Last status
• Description
• Last run status
• Delivery extension
• Date modified

https://drive.google.com/file/d/0B07PMQYOhF2DWWhER19jazFEdk0/view?usp=sharing

Enjoy!

View User Access to your Reports in SSRS

How to see what access users have to your reports in SQL Server Reporting Service (SSRS)? Well now you can view the access with the following script, download it here 

https://drive.google.com/file/d/0B07PMQYOhF2DWWhER19jazFEdk0/view?usp=sharing

The script will show the following information

• Role name
• User name
• Path of report
• Report name
• Catalog type
• Description

Hope you find it useful.

Enjoy

Memory Usage by All Databases

Hello,

Do you know how much memory your databases are using and how much free memory you SQL Server has? Try this script below. 

The output will show the following:

• List all the databases with how much memory they are using
• Total usage for all databases
• Max memory set in SQL Server
• Memory available by subtracting the 2 values from above.

Enjoy!

DECLARE 
@TotalUsedByDatabases NUMERIC
,@TotalMAXMemorySQLSrv NUMERIC

SELECT
 isnull(db_name(database_id), 'resourcedb') [dbname]
, CAST(COUNT(row_count)/128.0 as decimal(10,2)) [size]
INTO #RAM
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id

SELECT dbname [Name of Databases], size [Memory Usage in MB] FROM #RAM

SET @TotalUsedByDatabases = (SELECT SUM(size) FROM #RAM);
SET @TotalMAXMemorySQLSrv = (SELECT CAST(value_in_use as NUMERIC) 
FROM sys.configurations 
WHERE name LIKE 'max server memory%');

SELECT @TotalUsedByDatabases [Total Memory used by Databases in MB]
, @TotalMAXMemorySQLSrv  [Max Memory Allocated to SQL Server in MB]
, @TotalMAXMemorySQLSrv - @TotalUsedByDatabases [  ** Memory Available ** in MB]


DROP TABLE #RAM;

SQL Server 2014 Checklist for Performance

Hello once again :)

Over time and discussing with other DBAs, I have compiled a checklist for performance. Here are some areas and comments for each. 

SQL Server 2014 Checklist for Performance

- Test your changes on your test servers
- Make changes incrementally - small change at a time
- Use 64 bit, even on a laptop

Memory
Set MIN and MAX values for memory. Max setting use script to calculate
Enable Optimize for Ad Hoc Workloads

CPU
Set "Cost Threshold for Parallelism" = OLTP = 45 and Reporting = 25. Default is 5 which is to low

Set "Max Degree of Parallelism"  leave it on after you have changed the cost threshold. 

Set NUMA = number of physical processors, not cores, is a good place to start

Disk
TEMPDB on separate disks,

Set TEMPDB data and logs onto separate disks

Use multiple files wtih equal sizes, not equal to the number of processors

Use index compression

More disks is better but limited to the number of controllers

Statistics
Enable AUTO_CREATE and AUTO_UPDATE

Make plans to manual updates on statistics with full scans

Defragment Indexes
Number of pages matter,defrag below 300-500 pages

Cannot defrag below 8 pages

Defragment indexes with less then 50% fragmentation and rebuilt index if higher than 50%

DO NOT USE 
Disable AUTO_CLOSE

Disable AUTO_SHRINK

DO NOT use Profiler GUI, use extended events, even in SQL 2008

Create server side trace using T-SQL scripts. Can you use the GUI to create but execute via T-SQL

Database Design
Separate log and data files onto separate disks

Use multiple file groups even on a single disk

Turn off AUTO_GROW (depends). If not, use fixed growth, not percentage. Do not leave defaults in place

Normalize the data as it benefits performance

Enforce constraints, have foreign keys, primary keys, unique indexes

Use narrow indexes, when possible

Indexes work better on integers - performance better

Don't create too many indexes (depends)

Rebuilt cluster indexes 

Coding
Return only use data you need

Use stored procedures or parameterized queries

Avoid cursors, WHILE, LOOP

Quality all object names

Avoid using sp_* stored procedure names

Avoid functions on columns and LIKE command

SET NOCOUNT ON

Don't nest the views and join views to views

Don't use NOLOCK 

Avoid recompiling execution plans

Use table variables instead of temp tables

Multi statement table valued functions are very bad!

Let me know if you have any additional checks to add

Thanks,

Rudy

Are you Ready for a New SQL Server Version?

Hello again,

As you are aware Microsoft like to provide DBAs and developers with more work every two years. So, are you ready for a new version of SQL Server and all the migration that goes along with it?

If you are not upgrading then you're lucky, for now. Most DBA/Developers I know are in the middle of upgrading to SQL Server 2012/2014.

If you are upgrading, then I would highly recommend reading an article from Michael J. Swart called "Developers, Ready for a New SQL Server Version?" 

http://michaeljswart.com/2014/11/devs_ready_for_a_new_version/

It's an excellent articles for developers and DBAs.

Thanks,

Rudy

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,

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

http://technet.microsoft.com/en-us/library/hh213127.aspx

Configure the Integration Services Service as a Cluster Resource

http://technet.microsoft.com/en-us/library/hh231005.aspx

Grant Access to the Integration Services Service

http://mssqltrek.com/2011/12/07/connecting-to-the-integration-services-service-on-the-computer-mirrorstand-failed-with-the-following-error-access-is-denied/

Connect to a Remote Integration Services Server

http://technet.microsoft.com/en-us/library/aa337083.aspx

Cannot Start SQL Server Reporting Services after applying an update

http://support.microsoft.com/kb/2745448

Before Installing Failover Clustering

http://msdn.microsoft.com/en-us/library/ms189910.aspx

SQL Server Failover Cluster Installation

http://technet.microsoft.com/en-us/library/hh231721.aspx

Create a New SQL Server Failover Cluster (Setup)

http://technet.microsoft.com/en-us/library/ms179530.aspx

Error during Installation of an SQL Server Failover Cluster Instance

http://blogs.msdn.com/b/psssql/archive/2013/09/30/error-during-installation-of-an-sql-server-failover-cluster-instance.aspx

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

Thanks and enjoy.

Rudy

My other blog at DELL Software - New

Hello all,

Today I have another blog which is with DELL Software and will be covering more topics. See below for link.

http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/01/21/10-sql-server-metrics-that-are-telling-you-something-by-rudy-panigas.aspx

As they become available I will be reposting them here on my blog site.

Enjoy and thanks,

Rudy

Interesting SQL Server Metric - Installation Date

Hello,

Do you collect metrics about your SQL Server environment? Wait, you don't? Here are some metrics you should collect.

• How many SQL Servers are installed?
• How many SQL Servers are installed?
• How many SQL Servers are used as production
• How many SQL Servers are used as development?
• How many SQL Servers are used as QA and UAT?
• How many SQL Servers are used as disaster recovery?
• How many SQL Servers are at your location, cloud, other sites, etc.?
• What applications are using each SQL Server(s)?
• How many database do you have?
• How big are the databases and total size of disk use?

Why collect metrics? Well there are many reasons, some are to help manage the SQL Servers and other are to show management what is happening. With management in mind, you should collect the date that SQL Server was installed. With installation date you can show many interesting facts to management.

Here is a list of just what you can show to management.

- How many SQL Servers you have
- How many are where installed each month, quarter and/or year
- How many SQL servers need to be upgrade (if you also collect versions/patch levels)
- What is the growth rate over any period

You can start to see that there is metrics here that management would love to see.

Below is a script that will show the server name, version and installation date.

SELECT @@SERVERNAME as 'SQL Server Name'
      , @@VERSION as 'SQL Server Version'
      , createdate as 'Date Installed' FROM sys.syslogins
WHERE [name] = 'NT AUTHORITY\SYSTEM' AND [dbname] = 'master';

Hope you find this script useful and have a happy holiday and a Happy New Year.

Rudy