Restore Master to Different Instance

While setting up a warm standby instance for one our job servers, I encountered an issue with decryption of credential passwords.  I have included the error text from the popup message box below.

An error occurred during decryption. (Microsoft SQL Server, Error: 15466)

This error occurred either during creation of a new credential or modification of an existing credential.

In our environment, we have elected to run all user jobs from a separate server, the job server referenced above.  A second instance has been brought online and we are configuring them for warm standby for each other.  Each instance will have the same configuration as the other; i.e. jobs, proxies, packages etc.  However, the jobs will only be enabled on the primary node for that job.

In an attempt to get the two instances in sync, the master and msdb databases from the original node were restored to the new node.  Restoring of the databases went well and it wasn’t until testing of the jobs to verify the functionality that the issue was uncovered.

The job failed and logged the error:

Error authenticating proxy domain\userid, system error: Logon failure: unknown
user name or bad password.)   

Having seen this error in the past when researching other job failures, I assumed that for some reason the password had been corrupted during the restore of the master database.

After opening the credential in question and rekeying the password, the error above was displayed.  I tried again with the same results. I then decided just to delete the credential and re-add with the correct password, failure again.  Attempting to add a new credential that had not existed on the server before ended with the same error.

After the repeated failures, I opened my browser to see what others had done to resolve this issue.  To my surprise, I couldn’t find another case of this happening.  I did find a couple of mentions of this perhaps pointing to a corrupt Server Master Key for the new instance.

Since this instance had been restored from another instance, I tried to import the Service Master Key from the original instance.   This attempt also failed.  When reviewing the articles above, the mention of the SQL Service Account got me to thinking.   What would happen if I changed the service account on my new instance to match the original instance?

Using SQL Configuration Manager, I made that change and restarted the instance.  Going back to SSMS I was able to create the proxy with the correct password, and I was able to run the job to success.

However, leaving the server in this configuration violated our security policy.  I hesitantly changed the service account back to the correct domain id, restarted the instance and then executed the job.  Success was once again reported by the job.

Below are the steps that were taken to resolve the issue:

• Restore master database
• Change SQLService Account to original account from source server
• Restart SQL Services
• Change SQLService Account to correct id for this instance
• Restart SQLServices

I hope that by documenting the issue, troubleshooting steps taken, and the successful resolution,  it may save someone else some time and headaches.

SSIS ForEach continuation after Error

ForEach in SQLServer Integration Services

Integration Services has built in constructs that provide the ability to loop through a result set.  This functionality can be used to loop through files in a directory, items returned from a query, or a list generated with another method.

One issue with the ForEach container, when using the default configuration, is that if one loop iteration fails the loop fails and processing stops.  Below is a technique that can be used to log the error and proceed to the next value in the list.

The package shown uses a ‘ForEach File Enumerator’ to loop through a directory, in this example c:\temp.  Three files exist in the directory that match the criteria.  LoopDemo.txt, LoopReadOnly.txt, and LoopSequential.txt.  LoopReadOnly.txt has the read only attribute set to true.

With the default options set for the ForEach loop construct the first file is deleted, LoopDemo.txt. When deletion on the second file is attempted, LoopReadOnly.txt, an error is produced and the loop fails after displaying the error.

By setting the ‘MaximumErrorCount’ to 0 on the ForEach container the failure will occur and the message displayed, however the loop will continue through the remaining file.

Errors produced by the task can be logged in any manner that is available, email notification, logging to a table etc.

Server in Script Upgrade Mode

When one of our SQL Server 2008R2 clusters failed over the amount of time needed between when the server came online and when clients could connect to the instance seemed to be overly long.  After some research into the logs I found this error occuring multiple times for different ids, most of the ids were automated processes that connect as quickly as possible.

Login Failed for user 'xxxxx'.  Reason: Server is in script upgrade mode.  Only administrator can connect at this time.

Going back over the upgrade logs the SQL Instance in question hadn't been patched in quite some time.  So what was the reason for the upgrade script running?

I googled for answer and came up empty as to why this would be happening after the patch was successfully installed.

After some additional research the issue was found,  SP1 had been installed on the primary node but not the secondary node of the cluster.  Each time the instance was failed over the SQL Engine had to perform and upgrade/downgrade of the system databases.

Lesson Learned:  Patch one node and schedule the other node upgrade.

SSMS 2005 Edit Job Issue

Several people at my company have been experiencing issues when double-clicking existing jobs on a SQL2005 server running SP3.  The individual owns the job they are attempting to modify but when they double-click the job Management Studio opens up a new job window rather than the existing job.
We searched the internet and were unable to find a resolution to this issue.  After some testing it was discovered that when the client tools are running the RTM version, 1399, and the instances are running SP2 or beyond this behavior occurs.

See screenshots below for how to find the versions you are running.

The simple fix to this was to install the latest service pack of SQL2005 for the client tools on the workstation.  There is no error given and none logged that we could find.

Lesson Learned:  Always keep your clients tools on the the same or later version of SQL that your instances are running on.

Register the server and to the right of the name you will see the version - 9.0.3042 in this example



In Management Studio, Click Help/About, the version is on the first line as shown circled in red.

9.00.1399 is the RTM release any other version may cause the issue noted above.

Conversion Strikes Again

While doing some performance tuning this week I found a query that was executing very frequently from a web application.  The query was doing a simple select from the base table with two columns in the where clause both of which were in the same index.
The query in question went something like this:
select contactid, documentid, documenttype from documents where contactid = ? and documentid = ?

Profiler showed the query is this format:
exec sp_executesql N' SELECT contactid, documentid, documenttype from Documents WHERE ContactID = @ContactID   AND DocumentID = @DocumentID', N'@ContactID nvarchar(36),@DocumentID int', @ContactID = N'464906a7-5acc-44a0-9b50-76da83ba05d8', @DocumentID = 280

The column ContactID is defined as a varchar(36) in the documents table.  When the value to compare to comes in as nvarchar a conversion is required and the index is lost, a table scan ensues and reads skyrocket.

The performance for the query is not that bad, but when that much data must be moved into memory to check for a match other items get pushed out.

The fix the developer decided to go with was to change the column definition to nvarchar from varchar.  This allowed the index to be used and the reads to drop significantly.

In my environment we are finding more and more places where conversion required for the where clauses are causing indexes to be ignored in favor of a less efficient index or a table scan.

Skipping Task when Empty File

At times just checking for a file existence may not enough, if the file exists but is empty the load task may need to be skipped. I have compiled the example below to show one method of skipping the DTS task on file empty. The example utilizes the Workflow Properties page of each DTS Task. Although most corporations are moving away from DTS in favor of new ETL work being done in SSIS, SQL Server Integration Services, there are still plenty of opportunites to improve DTS packages that are still running. This example only works if the load task is the only task in the DTS package. I’ll be posting an update later with an example of how to move to the next task without executing the current task.

The workflow properties tab is available on the following components in the DTS Task pane. Bulk Insert Task, ActiveX Script Task, Execute SQL Task and Data Driven Qeury Task. The option is located by right-clicking the component, selecting Workflow and then Properties. Once on the Workflow Property tab select Options, check ‘Use Active X Script’ and then click properties. The mentioned sequence will open an Active X Script task window. This script is executed prior to the main body of the task.

The code below opens a file that is defined in the global variable, CheckFIle. This is the full file path including name to be checked. If the file is empty or has a blank first line the task is skipped. The result in a job is that the step reports success.

'**********************************************************************
' Skip task if empty file or first line is blank, uses global variable CheckFile
'************************************************************************
Function Main()
   SET fso = CreateObject("Scripting.FileSystemObject")
   DataSetNameTxt = DTSGlobalVariables("CheckFile").value
   Set TextStream = fso.OpenTextFile(DataSetNameTxt,1)
   If TextStream.AtEndOFStream or TextStream.AtEndOfLine then
      Main=DTSStepScriptResult_DontExecuteTask
   else
      Main=DTSStepScriptResult_ExecuteTask
  end if
  TextStream.Close
  Set TextStream = nothing
End Function

By returning the value of either DontExecuteTask, skip the main task, or ExecuteTask proceed normally, execution the main body of the task is controlled.

Below are the screenshots to show the path to Workflow Properties Task input window:

Unexpected View Performance

Article published at http://www.sqlservercentral.com/articles/Views/71787/, covers an issue with differing datatypes hampering performance of a view. This was created after a real life experience I had.

SQLServerCentral is an excellent resource for anyone involved with SQL.

Take a look at the article and let me know what you think.