Rudy Panigas - Blog on Microsoft SQL Server

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

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;

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!