Category Archives: SQL

SQL 2012 Failover Cluster Instance SP1 installation failed with error

Installing SQL Server SP1 on SQL 2012 Failover Cluster Instance (FCI) failed with  the following error:

There was a failure to calculate the default value of setting PatchResult.

Error code 0x85640001

The issue was that when you launch SQLServer2012SP1-KB2674319-x64-ENU.exe, it creates a temporary folder (something like g:\27eb5c29eceb5ae55b8b531263faacc) with setup files on a cluster disk which happened to store SQL data files.

During SP1 installation, it restarts the SQL Server service which then took the cluster disk where SQL data files and the SP1 temp folder hosted offline, then the service pack was trying to read from disk that is unavailable caused the install to fail.

The workaround is to copy the SP1 temp folder to the local c:\ drive and run Setup from c:\ location, SP1 installation completed with no problems.

I know it sounds weird but the SP1 will extract the files to whatever drive has more space and in this case was a cluster disk.

SQL 2012 SP1 CU6 issue with SSIS DB in Availability Group

Recently we were installing SQL Server 2012 SP1 CU6 to fix the slow performance when you run the SSIS Server Maintenance Job to remove old data in SQL Server 2012, you can read about the issue here.  We ran into a serious issue where the installation failed and couldn’t start SQL Server services.

Let me first describe the server, it is a named instance of SQL Server 2012 SP1 Engine and SSIS with AlwaysOn Availability Group, primary instance in main data center and secondary replica in DR site. The AG comprises all databases and SSISDB.

SQL 2012 SP1 CU6 installation failed with the following error:

Script level upgrade for database ‘master’ failed because upgrade step ‘SSIS_hotfix_install.sql’ encountered error 945, state 2, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

The issue was related to configuring SSIS db  “SSISDB” with AlwaysON Availability Group. So what happened is that SQL Server service packs usually run in single-user mode, while an availability database must be a multi-user database. Therefore, during installing the CU6, all availability databases including SSISDB will be taken OFFLINE which become inaccessible and thus fail the patch.

To workaround this issue, here are the steps:

  1. Start SQL Server service with Trace Flag 902: 
    • >Net Start MSSQL$InstanceName /T902
  2. Open SQL Server Management Studio, go to Availability Group and remove SSISDB from the availability databases
  3. Open New Query, execute the SSIS_hotfix_install.sql script which can be found in Install folder under \Program Files\Microsoft SQL Server\MSSQL11.MSSQL$InstanceName \MSSQL
  4. Stop SQL Server services:  
    • >Net Stop MSSQL$InstanceName
  5. Start SQL server service from SQL Server configuration manager
  6. Add SSISDB back to Availability Group

That’s all and we are back in business. Click here from more information on how you can make use of SSIS and AlwaysOn Availability Groups.

T-SQL Linear Regression Function

Predictive Analytics is the wave of the future and has become vital in today’s data evolution.

Predictive analytics incorporates a variety of techniques from statistics, modeling, machine learning, and data mining that analyze current and historical facts to make predictions about future.

I’ll describe the linear regression approach and how to write a T-SQL function to calculate the regression and produce the Intercept, Slope and R2 which are used in a regression equation to predict a value. In simple linear regression, the topic of this post, the predictions of Y when plotted as a function of X form a straight line.

The following table lists a sample data of car drivers driving experiences (in years) and monthly auto insurance premiums.

 

Driving Experience

(X value)

Insurance premium

(Y values)

7

75

10

68

12

65

18

60

20

57

25

50

Table1

Here is the T-SQL code will take the X and Y values from table 1 and calculate the regression coefficients:

declare @n int,           

              @Intercept DECIMAL(38, 10),

              @Slope DECIMAL(38, 10),

              @R2 DECIMAL(38, 10)

Select @n=count(*) from Table1

Select

@Slope = ((@n * sum(x*y)) – (sum(x)*sum(y)))/ ((@n * sum(Power(x,2)))-Power(Sum(x),2)),

@Intercept = avg(y) – ((@n * sum(x*y)) – (sum(x)*sum(y)))/((@n * sum(Power(x,2)))-Power(Sum(x),2)) * avg(x)

From Table1

 

Select @R2 = (@Intercept * SUM(Y) + @Slope * SUM(x*y)-SUM(Y)*SUM(y)/@n) / (SUM(y*y) – SUM(Y)* SUM(Y) / @n)

Select @Slope as Slope, @Intercept as Intercept, @R2 AS R2

From Table1

 

So to predict what will be the insurance premium if the driving experience years = 15

Y = Intercept + 15 * Slope (where y is the insurance premium)

For an in depth article about the linear regression, please refer to the Wikipedia Entry,

SQL Server Enterprise Management Data Warehouse (eMDW)

eMDW

Few years ago, I was managing 100’s of SQL servers with 100’s to 1000’s of databases in a very busy environment. My challenge was how to manage all SQL Server instances, databases and have a complete inventory of my environment. SQL Server Management Studio is a tool will allow me to view data per instance, so I have to connect to each instance and browse the properties of each instance but not from a central view. I needed a tool that I can see total number of Servers with summary that I can drill down by environment by business unit down to an object level. I want to look up a user permission across SQL Servers and level of access, I need to know how many databases, objects and users are by environment i.e.  Production, test, dev and QA, how much total storage my SQL Server are using with drill down capability and break it down by file type and display growth trending.

Long story short, I needed a centralized management view of my SQL server environments that I can have a complete inventory that can answer my previous questions.

I began building a centralized reporting system by creating a master repository database on a SQL Server instance. The repository would store data about every SQL Server instance, database, object, and user in an enterprise database environment.

I developed a custom an SSIS package that collects a plethora of SQL data with details on SQL server Instances and databases, and store the data in a central database.

After data is in the repository, you can use Reporting Services to view the data. I developed different dashboards and reports in SQL Server Reporting Services to view instance, databases, objects and users data. This solution helps me monitor the status and inventory of 100’s to 1000’s of databases and assess how big my environment is and have access to enterprise data from centralized reports.

You can download the entire solution at: GitHub