How to upgrade SQL Server? Step by Step Guide – Screen Shots

Microsoft has planned to come up with new version of SQL Server on every 3-5 years time frame. The current release of SQL Server is SQL Server 2012. Upgrading a SQL Server is critical activity which a DBA has perform to continue to get Microsoft support.

This weekend, I was performing a SQL Server upgrade from SQL Server 2000 to SQL Server 2005, still pretty old but the customer application is not being tested on SQL Server 2008 / R2 so that the only version which is achievable.

There are multiple ways to upgrade SQL Server

  1. Side by Side upgrade
  2. In Place upgrade

SIDE by SIDE upgrade is always an better option for DBAs, this option will leave the current instance as it is and we install a new SQL Server instance as a named instance and perform a backup and restore method to transfer databases from old version to newer version of SQL Server. In this solution, no down time is required as both instances can in parallel.

In Place upgrade, in this option we upgrade binaries from existing installation to a newer version of SQL Server. This method requires downtime and may fail in betwwen.

In my case, I did a In place upgrade because of following two limitations.

  • Limitation of Storage / resources -  The server which I am gonna upgrade is lower end and doesn’t’ have enough storage to run two copied of databases.
  • Application Limitation – Application SME is not available to modify connection string from default instance to Named instance.

This is a development server upgrade and production environment will be upgrade once every application level functionality is being verified by business on the DEV environment.

Step by Step Guide to Upgrade SQL Server In place Upgrade from SQL Server 2000 to SQL Server 2005 Enterprise Edition.

PRE Upgrade TASK

Prior to actual upgrade, please ensure that your database / database objects / application are compatible for newer version of SQL Server. Every version of SQL Server add some new features and deprecate some old / legacy  features. So prior to upgrade, please check and confirm that  you are not using any feature of SQL Server / TSQL Code which is deprecate in newer version of SQL Server.

Upgrade Advisor checks for any potential compatibility issues, deprecated functionalities, and feature and behavior changes, and provides you with a recommended list of tasks that need to be done before and after the upgrade.

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

This is tuff task, thus Microsoft has come up with a solution named “Upgrade Advisor” , which check and verifies each and every piece of your database / database objects will work in next version f SQL Server. If there is any deprecate feature, which you are currently using, SQL Server Upgrade Advisor will produce a report for the same.

If any feature / Code is being reported by Upgrade Advisor as warning / Error / deprecate feature, please work with developer to take special consideration.

To run Upgrade Advisor for SQL Server, you must have this application installed on your system. If you don’t have this Upgrade Advisor Installed, No worries, this is a free application from Microsoft. you can find Upgrade Advisor at Microsoft Site or it’s also available in SQL Server installation media / DVD.

Install Upgrade Advisor. Start the Upgrade Advisor from SQL server installation media

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

Enter Registration information and finally select upgrade advisor to install.

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

Once the Upgrade Advisor is installed, run that for all user databases and system databases and check the report.

This may take hours to run depending on size of the database. In my case this took approx 2 1/2 hours to complete.

Then verify that you are ready to upgrade and all your database objects will work on newer version of SQL Server.

SQL Server Upgrade Actually

Prior to start SQL Server Installation / upgrade. Please verify that you have met all pre requisite for SQL Server 2005 installation like Windows Installer Services. In case of SQL Server 2008, .Net framework is also required.

Ensure that you have Windows Installer Services Installed on your Windows System. If you don’t have Windows Installed, then you can install Windows Installer 4.5  from Microsoft. In my case I need to install This on Windows 32 bit so I downloaded and installed WindowsServer2003-KB942288-v4-x86.exe

Once this is being Done, Restart system and begin with Windows 2005 Setup installer by double clicking SETUP.EXE in installation media file.

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

Enter the product key and License aggreement

Select the component which you want to install

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

Click on Installed Instances and select the instance which you want to upgrade

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

Once the Instance in identified, which you want to upgrade, the setup will show you current version and once you click on Details, it will show you available options during this setup session.

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

Select the mode for authentication mechanism. This credentials will be used by SQL Server connection to old instance, which we are upgrading.

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

 

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

 

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

OPEN SQL Server Management studio and verify SQL Server version

How to upgrade SQL Server? Step by Step Guide   Screen Shots SQL Server upgrade planning sql server Upgrade Activities sql server upgrade sql server in place upgrade

Prior to send this good news to business, please validate SQL Server installation log files to verify there is no issue with any component of sql server. Please check my previous article to check installation logs (BOOTSTRAP files).

Now apply Updated Service pack to keep software upto date. so now in my case, I need to install and apply SQL service pack 4, which is latest available for this version of SQL Server 2005.

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information Error 8623 / 8632

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. ERROR MSG 8623   

This Error was received by one of the developer on his Development Server. The first impression was, Developer has must have written a extremely complex Long queries to meet complex Business requirement.

But Actually he was just running a simple SELECT statement, like this

SELECT Column 1 , Column 2 ,Column  3 from Table 1 where  Column 1 in (Select DISTINCT Column1 from Table 2 where COLUMN2 = @userVAR)

It’s hard to digest that this is a complex Query and we were able to reproduce this issue by running this again and again.

Just for information, The tables used in above illustration has huge data. Table 1 has approx. .9 million records and Table has approx .14 million records

My objective was to get away from this Message and User should able to get the results because I was running out of time.

SOLUTION / FIX :

I suggested developer to Join the tables instead of using the IN clause, which immediately FIXED this issue.

A day after, I was spending time to analyze why this SQL server treated that simple Query as extremely COMPLEX QUERY and retuned an Error Message. I started running this query on server by changing where clause condition and I was surprised with result.

I was able to get this error again when attempting to select records through a sub query query with a large number of entries in the “IN” clause. The moment I sub query started returning less records there was no Error/ Warning message.

Recommendation :  If you have left out with no other option and have to use IN CLAUSE only with large table then

  1. Simplify your query
  2. may divide query into multiple sub query (DIVIDE and RULE)
  3. modify the Query plan bu supplying HINTS.

LESSON LEARNED :

In case you are using a sub query to extract the records from other table. THE IN clause should have less than  10,000 records (< 10,000), if its returning more than that you might get I was getting error “ran out of internal resources and could not produce a query plan“. Later on I found the same thing in Microsoft Documentation about this IN clause limitation.

This once again confirmed that Joining table is better option that using a IN CLAUSE.

Tivoli Data Protection SQL Server – Restore Database with different name

HOW TO RESTORE SQL Server Database with Different / Alternate NAME from backup created by Tivoli Data Protection (TDP) for Microsoft SQL Server ?

Restore a backup from TDP library is pretty easy but you just need to be familiarized with tool to interact with Data Protection for Microsoft SQL Server.

To Restore a USER Database from a Tivoli, SQL Server Data Protection, you need to

OPEN GUI tool named Data Protection for Microsoft SQL Server; Start Menu\Programs\Tivoli Storage Manager\Data Protection for Microsoft SQL Server

Tivoli Data Protection SQL Server   Restore Database with different name  SQL Server third party Backup Software SQL Server TDP sql restore backup

  1. Click on the ‘Restore Databases’ tab.
  2. Select the ‘Show Active and Inactive’ and ‘Wait for Tape Mounts for File Information’ checkboxes.
  3. Selecting the ‘Show Active and Inactive’ option returns information on all backups within the retention period (not selecting this option will display only the most recent backup)
  4. The ‘Wait for Tape Mounts for File Information’ is required if replacing an existing database
  5. Expand the SQL Servers group.
  6. Expand the server name to display a list of all databases.
  7. Highlight the database being restored. The list of candidate backups is displayed in the right window.
  8. Select the backup from which you need to restore by clicking the checkbox
  9. Ensure the database being restored is selected and the correct backup selected.
  10. Right Click on the Selected Backup and select Restore INTO Tivoli Data Protection SQL Server   Restore Database with different name  SQL Server third party Backup Software SQL Server TDP sql restore backup
  11. Enter the name of the database being restored to and Click OK.Tivoli Data Protection SQL Server   Restore Database with different name  SQL Server third party Backup Software SQL Server TDP sql restore backup
  12. Again Right Click on the selected backup and select Relocate to relocating your database files to a new location and Click OK Tivoli Data Protection SQL Server   Restore Database with different name  SQL Server third party Backup Software SQL Server TDP sql restore backup
  13. Click the Restore button, which will start the Restore Operation and you can Monitor the progress window to confirm completion of the restore; respond to error messages accordingly. Tivoli Data Protection SQL Server   Restore Database with different name  SQL Server third party Backup Software SQL Server TDP sql restore backup

This will successfully restore your selected database from Achieve with a New Database name.

SQL Server 2012 Cluster Install Error : "SQL Server Database Services feature state"

"SQL Server Database Services feature state". This was the Error Message, which I received this morning during building / Installing a SQL Server 2012 cluster.

SQL Server 2012 Cluster Install Error : "SQL Server Database Services feature state" SQL Error sql cluster install error

FIX / Resolution / Workaround :

Change registry key on active node:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQLDEV2012\ConfigurationState]

Change all entry values from 2 to 1

Incident happened to me in Detail

I had  some issues in installing SQL Server 2012 in first node in a two node cluster, where a SETUP has ended up with error but I manually resolved those issues and my SQL Services worked fine of NODE 1 of SQL Server Cluster.

This morning when I was adding a another node in the same cluster, I ended up getting error "SQL Server Database Services feature state". This error basically means, the previous installation of SQL Server didn’t completed with successful status, which was correct in my case.

But I corrected these issues, why SQL Server Installation is unable to check the current status /why is checking previous status.

The answer is, During SQL Server Node installation, Set up check installation status from Active NODE and that information is read out from Registry entry. This registry setting is located at

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQLDEV2012\ConfigurationState]

SQL Server 2012 Cluster Install Error : "SQL Server Database Services feature state" SQL Error sql cluster install error

Change all values from 2 to 1 where 2 means Failed and 1 means success. SO I am explicitly telling setup that my previous installation was  Successful.

After this Re-RUN the test and it should be completed successfully this time.

SQL Server 2012 Cluster Install Error : "SQL Server Database Services feature state" SQL Error sql cluster install error

Summary: There is no need to reinstall the SQL server on NODE 1 to fix Cluster Installation RULE Error "SQL Server Database Services feature state" . Just modify the registry value to fix this issue.

User does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed – SQL Server Reporting Services Error

User ‘DBATAG-DEMO\cluadmin’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

I received this error, when I was trying to configure SQL Server Reporting Services for “Scale-OUT Deployment” for NLB configuration.

User does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed   SQL Server Reporting Services Error SQL Error Reporting Services report Server Configuration

I was logged in as Domain / Enterprise Administration account still getting this message. I have already worked on this issue sometime back. So immediately as a workaround

WORKAROUND / FIX / SOLUTION :

Start the Internet Explorer with Administrative Privileges by Right Click on Internet Explorer and select “Run as Administrator

User does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed   SQL Server Reporting Services Error SQL Error Reporting Services report Server Configuration

This will temporarily fix this issue at session level.

User does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed   SQL Server Reporting Services Error SQL Error Reporting Services report Server Configuration

To Solve this Issue Permanently / Solution

  1. Start Windows Internet Explorer.
  2. On the Tools menu, click Internet Options.
  3. Click Security.
  4. Click Trusted Sites.
  5. Click Sites.
  6. Under Add this Web site to the zone, type http://<var>ServerName</var>. If you are not using HTTPS for the default site, click to clear the Require server certification (https:) for all sites in this zone check box.
  7. Click Add.
  8. Repeat step 7f and step 7g to add the http://localhost URL, and then click Close.
  9. Note This step enables you to start Internet Explorer and open either the localhost or the network computer name of the server for both the Report Server application and the Report Manager application.
  10. Create role assignments that explicitly grant you access together with full permissions. To do this, follow these steps:
  11. Start Internet Explorer together with the Run as administrator option. To do this, click Start, click All Programs, right-click Internet Explorer, and then click Run as administrator.
  12. Open Report Manager URL, which you configured using Reporting Services Configuration Manager    User does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed   SQL Server Reporting Services Error SQL Error Reporting Services report Server Configuration
  13. In the Home dialog box, click Properties.
  14. Click New Role Assignment.
  15. Type a Windows user account name by using the following format:
  16. Click to select the Content Manager check box.
  17. Click OK.
  18. In the Home dialog box, click Site Settings.
  19. Click Configure site-wide security.
  20. Click New Role Assignment.
  21. Type a Windows user account  User does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed   SQL Server Reporting Services Error SQL Error Reporting Services report Server Configuration
  22. Click System Administrator.
  23. Click OK.
  24. Close Report Manager.
  25. Use Internet Explorer without the Run as administrator option to reopen Report Manager.

This should FIX this issues and you should directly get into Reporting Manager

SQL Task to Delete Old Files from Server

As DBA we Schedule everything to automate the system as possible as we can and at times there is a requirement to delete old files from the Server. There are various ways to DELETE OLD FILES FROM SERVER

  1. Write a Script (VB / Power shell / BATCH) file to Delete Old LOG / Report files and schedule a task in Windows Task Scheduler
  2. Write a Script (VB / Power shell / BATCH) file to Delete Old LOG / Report files and call this Script file from a SQL Server AGENT JOB
  3. Create a SQL Server Maintenance Plan and delete old files.

Let’s take a quick look on this methods and there pros and cons

Solution 1 : Write a Script (VB / Power shell / BATCH) file to Delete Old LOG / Report files and schedule a task in Windows Task Scheduler

STEPS to Create a Windows Task Scheduler to Delete Old files

STEP 1  - Create a Script file using either VB / ACTIVE X / Power shell / Batch File which can delete X number of old files.

Sample VB Script to Delete old files from various locations and different number of old days, in this Script all files older than 35 days will be deleted from F:\Reports and all files older than 10 days will be deleted from G:\SQLBackup

Call fnDeleteOldFiles("F:\Reports", 35)
Call fnDeleteOldFiles("G:\SQLBackup", 10)

Function fnDeleteOldFiles(FolderPath, RetentionIndays) 
   Dim fso, f, fc, FileEntry
   Set fso = CreateObject("Scripting.FileSystemObject")
   If (fso.FolderExists(FolderPath)) Then
      Set f = fso.GetFolder(FolderPath) 
      Set fc = f.Files
      For Each FileEntry in fc
         If DateDiff("d", FileEntry.DateLastModified, Now) > RetentionIndays Then
            FileEntry.Delete(True) 
         End If
      Next
      Set FileEntry = Nothing
      Set fc = Nothing
      Set f = Nothing
   End if
   set fso = Nothing
End Function

Sample PowerShell Script to Delete old files older than 10 days

#----- defining  parameters -----#
#----- current date ----#
$Now = Get-Date 
#----- define amount of days,  All files older than these days will be Deleted----#
$Days = "10"
#----- define folder where files are located ----#
$TargetLocation = "G:\SQLBackup"
#----- define type of files ----#
$Extension = "*.log"
#----- define LastWriteTime parameter based on $Days ---#
$LastWrite = $Now.AddDays(-$Days)

#----- get files based on lastwrite filter and specified folder ---#
$Files = Get-Childitem $TargetLocation -Include $Extension -Recurse | Where {$_.LastWriteTime -le "$LastWrite"}

foreach ($File in $Files) 
    {
    if ($File -ne $NULL)
        {
        write-host "Deleting File $File" -ForegroundColor "DarkRed"
        Remove-Item $File.FullName | out-null
        }
    else
        {
        Write-Host "No more files to delete!" -foregroundcolor "Green"
        }
    }

Sample Batch (CMD) Script to Delete old files older than 10 days

@echo off
:: set Target Location 
set dump_path=G:\SQLBackups

:: set number of Days of files and folders to delete
set days_to_keep=10

:: remove files from %dump_path%
forfiles -p %dump_path% -m *.* -d -%days_to_keep% -c "cmd  /c del /q @path"

:: remove sub directories from %dump_path%
forfiles -p %dump_path% -d -%days_to_keep% -c "cmd /c IF @isdir == TRUE rd /S /Q @path"

STEP 2  - Create a repetitive Task in Windows Task Scheduler and call this Script file to delete old files.

Go to Task Scheduler, Start button >> Control Panel >> Administrative Tools>> Task Scheduler >> Action menu >> Create Basic Task >> Schedule, as per your requirement >> Locate a Script, which we created >> Finish

Solution 2 : Write a Script (VB / Power shell / BATCH) file to Delete Old LOG / Report files and call this Script file from a SQL Server AGENT JOB

STEP 1  - Create a Script file using either VB / ACTIVE X / Power shell / Batch File which can delete X number of old files, same as previous section

STEP 2  - Create a JOB inside SQL Server using SQL Server AGENT.

Solution 3 : Create a SQL Server Maintenance Plan and delete old files

 

SQL Task to Delete Old Files from Server SQL Server Maintenance Plan DELETE Older Files

I know this is very old topic and this is how I ended up in writing on this. I was working on a Reproving Services project where a  daily report files is Generated and stored locally on the Server and this Job / Schedule was working perfectly fine.

This Morning, the request didn’t received the file because the Job failed and Job failed because the there is no space for a new file creation.

I was shocked this is 500 GB drive, then how come a dedicated drive go out of space, when I dig into details, there are thousand of files exists on this Server as no file is being deleted since this system was set up.

When I reached to business they said, they no more need a month old files. So there is a lack of maintenance task to delete old files.

So I immediately implemented maintenance task (SQL Server Job / Schedule) to delete all report files older than 5 weeks.

SEQUENCE BREAKS / GAP in Numbers after Restart SQL Server (gap between numbers after restarting server)

A user reported that there is huge GAP between numbers generated by SEQUENCE after restarting the SQL Server.

SEQUENCE BREAKS / GAP in Numbers after Restart SQL Server (gap between numbers after restarting server) SQL Server Sequence SQL Server 2012

bit surprised, Don’t worry this is Expected behavior of SQL Server.

If you don’t know that Sequence like Identity is new feature of SQL Server 2012, please check my previouos post How to Create and use Sequence in SQL Server for details.

By default when you create a SEQUENCE you can either supply CACHE size. Caching is used to increase performance for applications that use sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers.

But even if you don’t supply this parameter, SQL Server Automatically create a CACHE for SEQUENCE.

To fix this issue, you need to make sure, you add a NO CACHE option in sequence creation / properties like this.

CREATE SEQUENCE TEST_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
   NO CACHE

Here is step by step by re production on issue for gap between numbers after restarting server and solution