SQL SERVER – Installation failed with error – Wait on the Database Engine recovery handle failed

You might have seen on Facebook that I have completed the new setup of my machines. Since I use VMs to do my tests. To have a domain, I made a VM as domain controller and another as a member server. Next step was to install SQL Server and by mistake, I have run the setup of SQL on a domain controller. As per Microsoft documentation they don’t recommend installing SQL on the domain controller and there is a warning in SQL Setup.

setup dc 01 SQL SERVER   Installation failed with error   Wait on the Database Engine recovery handle failed

—————————
Rule Check Result
—————————
Rule “Computer domain controller” generated a warning.
Installing SQL Server 2016 CTP2.4 on a domain controller is not recommended.
—————————
OK
—————————
I proceeded further and it failed at the end with below error
Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
Here is the complete message in summary.txt file.
Feature:                       SQL Server Replication
Status:                        Failed: see logs for details
Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
Next Step:                     Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
Component name:                SQL Server Database Engine Services Instance Features
Component error code:          0x851A001A
Error description:             Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
I followed the error message as it asked to check ERRORLOG. Here is message in ERRORLOG
Error: 17190, Severity: 16, State: 1.
Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.
Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.
Error: 17826, Severity: 18, State: 3.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
Error: 17120, Severity: 16, State: 1.
SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

I searched on the internet and found that this could be issued with a SQL Service account. I selected all default configuration and it was NT Service\MSSQLServer

I uninstalled SQL Server and this time I selected Local System account for SQL Service in setup wizard and voilà it went fine.

Have you ever faced any such error on a regular machine? What was the solution?

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Backup to Azure Blob error – The remote server returned an error: (409) Conflict

Cloud is inevitable. With various innovations happenings in this area, SQL Server is not left behind. It is becoming a first class citizen when it comes to moving to cloud. In the past couple of releases, we have been seeing the enhancements of integrating with Azure getting stronger. Last year I wrote a blog about taking backup directly to Azure Storage.

SQL SERVER – Backup to Azure Blob using SQL Server 2014 Management Studio

As part of a demo environments, I configured it long back and recently I stated seeing failures of backup in ERRORLOG and found below error message.

2015-04-03 15:39:12.40 spid165     Error: 18210, Severity: 16, State: 1.
2015-04-03 15:39:12.40 spid165     BackupVirtualDeviceFile::DetermineFileSize: SetPosition(0,EOF) failure on backup device ‘https://sqlauth.blob.core.windows.net/backups/SQLATHTEST_da634d4074d9451591fe99d6e6981f83_20150331160324-07.log’. Operating system error Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (409) Conflict.

I searched for the ways to fix this error and landed across MSDN article which asked to enable trace flag 3051 to get additional details.

DBCC TRACEON (3051,-1)

Once I ran the command and ran the back-up again, it failed but there was an additional file generated in the same folder which has ERRORLOG. Here is the content

11/10/2015 3:39:15 PM: ======== BackupToUrl Initiated =========
11/10/2015 3:39:15 PM: Inputs: Backup = False, PageBlob= True, URI = https://sqlauth.blob.core.windows.net/backups/SQLATHTEST_da634d4074d9451591fe99d6e6981f83_20150331160324-07.log, Acct= sqlauth, FORMAT= False, Instance Name = MSSQLSERVER, DBName = SQLAUTHTEST LogPath = D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log
11/10/2015 3:39:15 PM: Process Id: 7384
11/10/2015 3:39:15 PM: Time for Initialization = 9.0003 ms
11/10/2015 3:39:15 PM: BackupToUrl Client is getting configuration from SqlServr
11/10/2015 3:39:15 PM: Time for Handshake and VDI config = 10.0001 ms
11/10/2015 3:39:15 PM: Time for Get BlobRef = 0.9949 ms
11/10/2015 3:39:16 PM: An exception occurred during communication with Azure Storage, exception information follows
11/10/2015 3:39:16 PM:  Exception Info: The remote server returned an error: (409) Conflict.
11/10/2015 3:39:16 PM:  Stack:    at Microsoft.WindowsAzure.Storage.Core.Executor.Executor.ExecuteSync[T](StorageCommandBase`1 cmd, IRetryPolicy policy, OperationContext operationContext)
at Microsoft.WindowsAzure.Storage.Blob.CloudPageBlob.AcquireLease(Nullable`1 leaseTime, String proposedLeaseId, AccessCondition accessCondition, BlobRequestOptions options, OperationContext operationContext)

If we look at stack it talks about lease. 409 conflict – Indicates that the request could not be processed because of conflict in the request, such as an edit conflict in the case of multiple updates.

I downloaded Azure Explore tool from http://www.cerebrata.com/products/azure-explorer/introduction and using the tool I was able to break lease which was open. Below is the option in the UI called ‘Break Lease On Blob’ once you are connected to the Storage account.

lease 01 SQL SERVER   Backup to Azure Blob error   The remote server returned an error: (409) Conflict

Another way was to break the lease using the way documented in MSDN https://msdn.microsoft.com/en-us/library/jj919145.aspx (Deleting Backup Blob Files with Active Leases) and it needs coding which I am not an expert at.

Have you ever tried using backupToURL option with SQL Server? Will you be using these tools for your environments? Do let me know via comments.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – How to Suppress SQL Server messages in Application Event Log

Error messages are a great source of help when it comes to troubleshooting. What can I say, this is maybe be the most repeated phrase from me in this blog about error messages. The messages have grown over ages and got better with the SQL Server dev team putting in extra effort in every release. Having said that, I thought of adding something interesting in this blog.

Logging of errors in ERRORLOG is helpful in many situations. It has helped me many time to fix issues. Recently I came across an email where there was a strange behavior reported. Here in the email from my friend.

Hi Pinal,
We are using 3rd party software which uses SQL Server as backend. We also have monitoring software which keeps track of messages in Event log and sends alert to Wintel team.
Recently there was a brute force attach for sa password on that server so there were many login failure attempts like below:

2015-11-05 20:14:47.040 Logon        Error: 18456, Severity: 14, State: 8.

2015-11-05 20:14:47.040 Logon        Login failed for user ‘sa’. Reason: Password did not match that for the login provided. [CLIENT: 10.10.10.20]

There messages were logged many time and IP address listed there is some machine which we don’t know. We were able to stop the connection by firewall.

Here is the problem: Monitoring software didn’t report anything because strangely these messages were NOT logged in application event log at all.

Do you know how that can happen?
Thanks in advance.

Whenever I get such questions, I always asked for ERRORLOG.

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

I looked into ERRORLOG file and found something interesting as below.

Registry startup parameters:

-d D:\MSSQL12.SQL2014\MSSQL\DATA\master.mdf
-e D:\MSSQL12.SQL2014\MSSQL\Log\ERRORLOG
-l D:\MSSQL12.SQL2014\MSSQL\DATA\mastlog.ldf
-n

Command Line Startup Parameters:

-s "SQL2014"

We can see a non-default startup parameter called “n”. I searched MSDN documentation and found its usage. “Does not use the Windows application log to record SQL Server events”

So I replied to my friend and later it was identified that 3rd party tool has done that change for that instance.

Mystery solved and I learned something new.  Have you ever used any such non-default startup parameter in production server?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to take InMemory – OLTP FileGroup Offline?

InMemory topics are always interesting and a great learning experience. When I wrote the other post, it was more about how InMemory tables are loaded into memory even if they are not queried. The basics, here is to bring the data for In Memory Tables ahead to memory irrespective of their usage.

After this blog post was published, someone wrote me a mail asking if it is possible to take the InMemory Filegroups offline? And will the table’s data taken offline too? This question was obvious but still needed validation and I was curious to understand why this wouldn’t work in normal circumstances. The script to start working on this post has to start from previous post. Please create the database and then before the cleanup step – start to run the below scripts.

At this stage, your query must return some value for the below query:

USE IM_OLTP;
GO
SELECT CONVERT(CHAR(20), OBJECT_NAME(OBJECT_ID)) AS Name,*
FROM sys.dm_db_xtp_table_memory_stats
WHERE OBJECT_ID> 0

For all practical purposes, InMemory tables and filegroups are just like normal files. We should be able to take them offline using the same way we can do other filegroups.

-- Take InMemory FG offline
ALTER DATABASE IM_OLTP MODIFY FILE (NAME = 'IM_OLTP_mod', OFFLINE)
GO

This should complete successfully and when we run the above DMV query to find the memory consumers, this must return no rows.

SELECT CONVERT(CHAR(20), OBJECT_NAME(OBJECT_ID)) AS Name,*
FROM sys.dm_db_xtp_table_memory_stats
WHERE OBJECT_ID> 0

Now that was pretty simple and easy to figure out. I would like to know if you have ever done anything like this in your systems?

Quick Quiz Question:

Now if taking the filegroup offline was easy, what about bringing the filegroup online next?

ALTER DATABASE IM_OLTP MODIFY FILE (NAME = 'IM_OLTP_mod', ONLINE)
GO

Simple isn’t it? No. You will encounter an error:

Msg 155, Level 15, State 1, Line 1
‘ONLINE’ is not a recognized CREATE/ALTER DATABASE option.

So the quiz question is, how can we bring the filegroup online in SQL Server 2014 version? What are the steps. Let me know via comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Script level upgrade for database ‘master’ failed – CREATE SCHEMA failed due to previous errors

SQL Server troubleshooting topics seem to be the most searched for in this blog. In continuation to this, blog for today is also inspired with one such error message. Some time back I have written below blog which talks about upgrade script mode of SQL Server:

SQL SERVER – Login Failed For User – Reason Server is in Script Upgrade Mode

One of the reader reached out to me with below error in ERRORLOG

Setting object permissions…
Error: 2714, Severity: 16, State: 6.
There is already an object named ‘TargetServersRole’ in the database.
Error: 2759, Severity: 16, State: 0.
CREATE SCHEMA failed due to previous errors.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 2714, state 6, 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.
Error: 3417, Severity: 21, State: 3.
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Above error is truly scary. “Restore master from a full backup, repair it, or rebuild it”. So I asked my friend to share the script ‘sqlagent100_msdb_upgrade.sql’ to check which query is failing. It was not very difficult to find (I searched for “Setting object permissions…” which was printed in ERRORLOG before error) and I found that below is the piece present in script, which was failing.

IF (EXISTS (SELECT *
FROM msdb.dbo.sysusers
WHERE (name = N'TargetServersRole')
AND (
issqlrole = 1)))
BEGIN
-- If there are no members in the role, then drop and re-create it
IF ((SELECT COUNT(*)
FROM msdb.dbo.sysusers   su,
msdb.dbo.sysmembers sm
WHERE (su.uid = sm.groupuid)
AND (
su.name = N'TargetServersRole')
AND (
su.issqlrole = 1)) = 0)
BEGIN
EXECUTE
msdb.dbo.sp_droprole @rolename = N'TargetServersRole'
EXECUTE msdb.dbo.sp_addrole @rolename = N'TargetServersRole'
END
END
ELSE
EXECUTE
msdb.dbo.sp_addrole @rolename = N'TargetServersRole'

To be very precise, adding the role was failing on MSDB database on his SQL Instance. I asked him to run below and got an error.

TS Role 01 SQL SERVER   Script level upgrade for database master failed   CREATE SCHEMA failed due to previous errors

Now, Since SQL we were not able to connect to SQL, we need to use trace flag 902 to bypass the script. Here are the steps followed.

1. Applied the trace flag -T902 on SQL Server configuration manager
2. Start SQL Services and it should allow us to connect because the trace flag would bypass upgrade script mode.
3. Find the schema/role TargetServersRole in MSDB and make a note of members, if any.
4. In his case, the schema was having someone else as owner. Asked him to modify using below T-SQL
USE [msdb]
GO
ALTER AUTHORIZATION ON ROLE::[TargetServersRole] TO [TargetServersRole]
GO

5. After fixing the cause, we stopped SQL, removed trace flag and started again.
6. This time script got executed and SQL was out of upgrade mode.

Have you also encountered similar issue?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Log Shipping Restore Job Error: The file is too recent to apply to the secondary database

If you are a DBA and handled Log-shipping as high availability solution, there are a number of common errors that come that you would over a period of time become pro on resolving. Here is one of the common error which you must have seen:

Message
2015-10-13 21:09:05.13     *** Error: The file ‘C:\LS_S\LSDemo_20151013153827.trn’ is too recent to apply to the secondary database ‘LSDemo’.(Microsoft.SqlServer.Management.LogShipping) ***
2015-10-13 21:09:05.13     *** Error: The log in this backup set begins at LSN 32000000047300001, which is too recent to apply to the database. An earlier log backup that includes LSN 32000000047000001 can be restored.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
Above error is a shown in failure of the history of restore job. If the failure is more than configured thresholds, then we would start seen below error in SQL ERRORLOG on secondary also:
2015-10-14 06:22:00.240 spid60       Error: 14421, Severity: 16, State: 1.
2015-10-14 06:22:00.240 spid60       The log shipping secondary database PinalServer.LSDemo has restore threshold of 45 minutes and is out of sync. No restore was performed for 553 minutes. Restored latency is 4 minutes. Check agent log and logshipping monitor information.

To start troubleshooting, we can look at Job activity monitor on secondary which would fail with the below state:

LS Restore 01 SQL SERVER   Log Shipping Restore Job Error: The file is too recent to apply to the secondary database

If you know SQL transaction log backup basics, you might be able to guess the cause. If we look closely to the error, it talks about LSN mismatch. Most of the cases, a manual transaction log backup was taken. I remember few scenarios where a 3rd party tool would have taken transaction log backup of database which was also part of a log shipping configuration.

Since we know the cause now, what we need to figure out is – where is that “out of band” backup? Here is the query which I have written on my earlier blog.

-- Assign the database name to variable below
DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'LSDemo'
-- query
SELECT TOP (30) s.database_name
,m.physical_device_name
,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize
,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken
,s.backup_start_date
,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn
,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn
,CASE s.[type]
WHEN 'D'
THEN 'Full'
WHEN 'I'
THEN 'Differential'
WHEN 'L'
THEN 'Transaction Log'
END AS BackupType
,s.server_name
,s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date DESC
,backup_finish_date

Once we run the query, we would get list of backups happened on the database. This information is picked from MSDB database.

Below picture is self-explanatory.

LS Restore 02 SQL SERVER   Log Shipping Restore Job Error: The file is too recent to apply to the secondary database

Once we found the “problematic” backup, we need to restore it manually on secondary database. Make sure that we are using either norecovery or standby option so that other logs can be restored. Once file is restored, the restore job would be able to pick-up from the same place and would catch up automatically.

What are the other problems you have seen with Log-shipping? If you can share some of the common errors, it would be of great help for others and I will try to blog about them too with your help.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Error Fix: Msg 300, VIEW SERVER STATE

errorstop SQL SERVER   Error Fix: Msg 300, VIEW SERVER STATEI travel quite a bit and the month of October and November seem to be filled with half my life is going to be spent hopping from one airport to another. Not to mention the amount of screening that go through every single time in every flight. However strange as it might sound, in one of such airport transits I was waiting for my bag to be screened through the machine and I was watching the screen on the other side where the security officer sits. It was interesting because it was a very skillful task as he was finding a needle in a haystack (in the literal sense). He was able to figure out what items, sharp items and toys which are restricted were getting passed through. I was amused and was transported into a world of wonderland because it was quite a challenging task.

With these memories not subsiding, I landed to my desired destination and started checking my mails. There was one mail that caught my attention because suddenly it was a junior DBA who was trying to give some permissions and was getting an error. I was quick to get interested because it was a topic around security and I wanted to crack it. The message stated like:

Msg 300, Level 14, State 1, Line 1
VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

As I always mention, most of the SQL Server error messages these days are well documented and were self-explanatory. To make sure I got the error message right, I requested the person to send me the query that was being executed that caused the error. The reply was:

SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats

 

This made complete sense and the solution for this was also simple. From a repro point of view, I created a user Pinal and tried to run the above command. And guess what, as luck might turn out – the same error message as described in the start. To mitigate this error, I was to give the following GRANT and the error message disappeared:

USE MASTER
GO
GRANT VIEW SERVER STATE TO Pinal

Just curious to know have you as a DBA ever granted this right to users? What was the scenario in your case? Would love to see why you don’t want to give to specific users? Will be a great learning for all of us. Let me know via the comments, please.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Linked server creation error: OLE DB provider “SQLNCLI11” for linked server returned message “Invalid authorization specification”

One of the blog reader sent me the below mail. I always find that using linked server have been of concern and trouble at a number of places. So when the mail landed – well I was not surprised that there was a problem. It is common and hence the resolution for the same is also common yet not discussed. So I thought this blog will bring the nuances that I thought was the resolution and which helped our blog reader.

Hi Pinal,
I have two SQL Server instances on same machine and I want to fetch data from each other. So I went ahead and created linked server. In SQL Server Management Studio, Server Objects > Right-click, Linked Servers, and then selected New linked server. I gave the remote server name as SQL16NODEB\SQL2014 and “Server type” as SQL Server as shown below.

linked 01 SQL SERVER   Linked server creation error: OLE DB provider SQLNCLI11 for linked server returned message Invalid authorization specification

While saving I got error as below

TITLE: Microsoft SQL Server Management Studio

——————————
The linked server has been created but failed a connection test. Do you want to keep the linked server?
——————————

The OLE DB provider “SQLNCLI11” for linked server “SQL16NODEB\SQL2014” reported an error. Authentication failed.

Cannot initialize the data source object of OLE DB provider “SQLNCLI11” for linked server “SQL16NODEB\SQL2014”.

OLE DB provider “SQLNCLI11” for linked server “SQL16NODEB\SQL2014” returned message “Invalid authorization specification”. (Microsoft SQL Server, Error: 7399)

linked 02 SQL SERVER   Linked server creation error: OLE DB provider SQLNCLI11 for linked server returned message Invalid authorization specification

If I hit No, the linked server is not created. If I created Yes, linked server is created, but any query is failing with the same error.

What should I do?

Thanks,
<name hidden>

I asked to check and run

sp_testlinkedserver N'SQL16NODEB\SQL2014'

and as expected, it failed with the same error.

OLE DB provider “SQLNCLI11” for linked server “SQL16NODEB\SQL2014” returned message “Invalid authorization specification”.

Msg 7399, Level 16, State 1, Procedure sp_testlinkedserver, Line 1

The OLE DB provider “SQLNCLI11” for linked server “SQL16NODEB\SQL2014” reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1

Cannot initialize the data source object of OLE DB provider “SQLNCLI11” for linked server “SQL16NODEB\SQL2014”.

linked 03 SQL SERVER   Linked server creation error: OLE DB provider SQLNCLI11 for linked server returned message Invalid authorization specification

The message “Invalid authorization specification” means that linked server settings to connect to the server are not correct.  To fix that, we need to go back to linked server properties and go to “security” tab and choose the proper authentication method from last two.

linked 04 SQL SERVER   Linked server creation error: OLE DB provider SQLNCLI11 for linked server returned message Invalid authorization specification

If there is a SQL Login to be used, then we need to provide account and its password.

Here is the T-SQL command for 3rd option where @useself is set to true

USE [master]
GO
EXEC MASTER.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQL16NODEB\SQL2014', @locallogin = NULL , @useself = N'True', @rmtuser = N''
GO
'

Here is the T-SQL command for 4th option where @useself is set to false so we need to provide @rmtuser and @rmtpassword

USE [master]
GO
EXEC MASTER.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQL16NODEB\SQL2014', @locallogin = NULL , @useself = N'False', @rmtuser = N'sa', @rmtpassword = N'sa'
GO

Hope this will helps and do let me know if you have ever got this error in your environments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Restore Corrupted Model Database from Backup?

Another email from one of my blog reader, which made me think about the incorrect information which is available on the internet.

Hi Pinal,

I have corruption in model database and due to that SQL Service is not able to start. Here are the messages in SQL ERRORLOG

2015-09-23 21:45:07.00 spid11s     Srting up database ‘model’.

2015-09-23 21:45:07.45 spid11s     Error: 5125, Severity: 24, State: 2.

2015-09-23 21:45:07.45 spid11s     File ‘E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\model.mdf’ appears to have been truncated by the operating system.  Expected size is 4288 KB but actual size is 4280 KB.

2015-09-23 21:45:07.49 spid11s     SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

What should I do? I have backup of the model database and want to restore it. I have spoken to many folks and I was getting different answers. Can you please provide me right direction?

Thanks,
<Name Hidden>

In this case problem is straight forward, restore backup of model when current model is corrupted and SQL is not starting due to that. If we search on internet, we get below incorrect hints:

  1. Start SQL with trace flag 3608. To do that Use this from command prompt: sqlservr.exe -T3608
  2. Start SQL Server via command prompt using sqlservr.exe -c –m

To test above, I renamed the database files of model database and then tried restore after started via trace flag 3608. Here is the message which I received during restore.

Shared Memory Provider: The pipe has been ended.
Communication link failure

model 02 SQL SERVER   How to Restore Corrupted Model Database from Backup?

Restoring model is not a big deal, it doesn’t need any special switch or startup parameter. We can use T-SQL query or Management Studio UI to restore model.

Our problem above is that SQL is not getting started because model is in bad state. Below are the working solutions.

model 01 SQL SERVER   How to Restore Corrupted Model Database from Backup?

Hopefully, this flow chart would help in getting exact steps needed.  Model can be restored directly via T-SQL or SSMS.

model 03 SQL SERVER   How to Restore Corrupted Model Database from Backup?

Have you ever found incorrect information on the internet? Never trust information from untrusted sources.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Failed Rule “Valid DSN” and “Valid Database compatibility level and successful connection”

I always try to keep myself up-to-date with latest release of SQL Server. As a part of that I wanted to upgrade my SQL Server 2016 CTP2.2 to CTP2.3 version. While doing in-place upgrade I was blocked with few error and I was not able to proceed. Here is the screenshot of upgrade screen.

ctp ssrs 01 SQL SERVER   Failed Rule Valid DSN and Valid Database compatibility level and successful connection

When we click on those messages, here is the text.

Error # 1

—————————
Rule Check Result
—————————

Rule “Valid DSN” failed.
The report server configuration is not complete or is invalid. Use Reporting Services Configuration Manager to verify the report server configuration.
—————————
OK
—————————

Error # 2

—————————

Rule Check Result
—————————
Rule “Valid Database compatibility level and successful connection” failed.

The report server database is not a supported compatibility level or a connection cannot be established. Use Reporting Services Configuration Manager to verify the report server configuration and SQL Server management tools to verify the compatibility level.

—————————
OK
—————————

Then I looked into the setup log to see what they have to say and found below. Below is what is also shown in the pop-up message.

(08) 2015-09-18 10:46:10 Slp: Initializing rule      : Valid DSN
(08) 2015-09-18 10:46:10 Slp: Rule is will be executed  : True
(08) 2015-09-18 10:46:10 Slp: Init rule target object: Microsoft.SqlServer.Configuration.RSExtension.DsnUpgradeBlockers
(08) 2015-09-18 10:46:10 RS: Error validating the dsn: No DSN server name found.
(08) 2015-09-18 10:46:10 Slp: Evaluating rule        : RS_ValidDSN
(08) 2015-09-18 10:46:10 Slp: Rule running on machine: SQL16NODEB
(08) 2015-09-18 10:46:10 Slp: Rule evaluation done   : Failed
(08) 2015-09-18 10:46:10 Slp: Rule evaluation message: The report server configuration is not complete or is invalid. Use Reporting Services Configuration Manager to verify the report server configuration.
(08) 2015-09-18 10:46:10 Slp: Send result to channel : RulesEngineNotificationChannel

(08) 2015-09-18 10:46:10 Slp: Initializing rule      : Valid Database compatibility level and successful connection
(08) 2015-09-18 10:46:10 Slp: Rule is will be executed  : True
(08) 2015-09-18 10:46:10 Slp: Init rule target object: Microsoft.SqlServer.Configuration.RSExtension.DsnUpgradeBlockers
(08) 2015-09-18 10:46:10 RS: Error validating the dsn: No DSN server name found.
(08) 2015-09-18 10:46:10 Slp: Evaluating rule        : RS_ValidDatabaseVersion
(08) 2015-09-18 10:46:10 Slp: Rule running on machine: SQL16NODEB
(08) 2015-09-18 10:46:10 Slp: Rule evaluation done   : Failed
(08) 2015-09-18 10:46:10 Slp: Rule evaluation message: The report server database is not a supported compatibility level or a connection cannot be established. Use Reporting Services Configuration Manager to verify the report server configuration and SQL Server management tools to verify the compatibility level.

The log clearly mentioned the action I need to take.

Solution

  1. Open Reporting Service Configuration Manager and go to Database tab and we see the “Current Report Server database” as empty. That is THE problem.

ctp ssrs 02 SQL SERVER   Failed Rule Valid DSN and Valid Database compatibility level and successful connection

  1. To fix that, click on “Change database” and below Wizard would open

ctp ssrs 03 SQL SERVER   Failed Rule Valid DSN and Valid Database compatibility level and successful connection

  1. We can easily follow the wizard and provide details. I have used default settings. Database Server:

ctp ssrs 04 SQL SERVER   Failed Rule Valid DSN and Valid Database compatibility level and successful connection

  1. On Database Page:

ctp ssrs 05 SQL SERVER   Failed Rule Valid DSN and Valid Database compatibility level and successful connection

  1. Credentials:

ctp ssrs 05 SQL SERVER   Failed Rule Valid DSN and Valid Database compatibility level and successful connection

  1. Summary

ctp ssrs 06 SQL SERVER   Failed Rule Valid DSN and Valid Database compatibility level and successful connection

  1. Progress and Finish

ctp ssrs 08 SQL SERVER   Failed Rule Valid DSN and Valid Database compatibility level and successful connection

  1. Finish

ctp ssrs 09 SQL SERVER   Failed Rule Valid DSN and Valid Database compatibility level and successful connection

Once above steps are complete, we can come back to screen which was giving error and choose “Re Run” and then we should be able to proceed next with setup. I was able to upgrade to next version without above error:

ctp ssrs 10 SQL SERVER   Failed Rule Valid DSN and Valid Database compatibility level and successful connection

Have you even seen such upgrade blockers?

Reference: Pinal Dave (http://blog.sqlauthority.com)