Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,037,795 hits

  • Select GETDATE()

    May 2015
    M T W T F S S
     123
    45678910
    11121314151617
    18192021222324
    25262728293031

Archive for May, 2015

Restore ERROR : This backup cannot be restored using WITH STANDBY because a database upgrade is needed

Posted by blakhani on May 21, 2015


One of my colleague in office sent an email to find the cause of the error. He wanted to configure Log-Shipping with secondary on Standby mode. Rather than “reply to all”, I generally ping the person directly to tell the possible reason of error. Here is what we discussed (let’s assume that his name is Manoj – M)

B: Hi Manoj
M: Hello
B: Saw you email to DL about log shipping problem.
M: Oh yeah. Thanks for reaching out.
B: No problem.
B: Are you restoring database from lower version to higher version of SQL?
M: No, both are 2008.
B: That’s doesn’t sound right. Can you please run Select @@version command in Management Studio on both the server and share the output?
M: Sure. Give me a minute to connect and run the query.
B: Sure

<1 min pause>

M: There you go

Source:
Microsoft SQL Server 2008 (SP1) – 10.0.2573.0 (X64)
Feb 4 2011 11:27:06
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Destination:
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)

 

B: Okay. that is the problem. You are taking backup from SQL 2008 and restoring on SQL 2008 R2 in standby mode.
M: Both are not 2008?
B: No, destination is 2008 “R2” which is the next release after 2008.
M: Oh.. I thought its like a service pack on top of SQL 2008.
B: Unfortunately, its not a service pack. It’s fresh release having some enhancements.
M: Okay. So error message is correct.
B: Yeah. If you want to use secondary for reporting then you have to use standby mode. This can only be done on destination which has same version of SQL. Or you need to upgrade current primary server.
M: Hmm.. I got it now. Let me check with my DBA team and management to decide the next action.
B: Sure Manoj. Is there any other clarification you might need?
M: No Balmukund. I am good at this point. You have been very helpful. Appreciate it.
B: My pleasure. Bye for now.
M: Bye!

In summary, Here is what you would get in SSMS if we try to restore a backup from lower version of SQL to higher version of SQL in standby mode.

TITLE: Microsoft SQL Server Management Studio
——————————
SQL Server Management Studio restore database ‘DabbaWala’
——————————
ADDITIONAL INFORMATION:
Restore failed for Server ‘Destination’.  (SqlManagerUI)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
——————————
System.Data.SqlClient.SqlError: This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&LinkId=20476
——————————
BUTTONS:
OK
——————————

 

The error is raised because during restore from lower to higher version the database version needs an upgrade to match with system table (or any other) change in higher version. Here is the query to see the version I am talking about. 

Select name, version from sys.sysdatabases

This version upgrade is part of recovery process and we can’t pause recovery in middle and view the database state (that’s what is done in standby mode). So, the only way to restore that would be to use “with recovery” or “with norecovery” option.

Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle
    • Advertisement

    Posted in Error, Logshipping, Restore, SQL Server | Tagged: , , | 2 Comments »

    SQL Cluster Setup Error – System.Runtime.InteropServices.COMException (0x80070490): Element not found

    Posted by blakhani on May 19, 2015


    My job revolves around troubleshooting and fixing the broken thing. Here is one of the situation which I ran into recently and was unable to find solution on internet. It my responsibility to provide self-assist option to the SQL community so that they can find the problem and fix by themselves.

    I was trying to install SQL Server 2012 on a 2 nodes Windows cluster. When I tried installing it, it failed with error in subject line. At this first look it sounds like some COM+ error but as always, setup logs are my first place to find the errors. Here is the MSDN link which explains the various files created by setup

    https://msdn.microsoft.com/en-us/library/ms143702(v=sql.110).aspx (View and Read SQL Server Setup Log Files)

    The information which I saw in setup logs was pretty interesting. In particular, I looked into Detail.txt file which is the parent file of all MSI logs. (I have removed date and time for better reading)

    Error: Action "Microsoft.SqlServer.Configuration.SetupExtension.ValidateFeatureSettingsAction" threw an exception during execution.
    Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: Element not found. (Exception from HRESULT: 0x80070490) —> System.Runtime.InteropServices.COMException (0x80070490): Element not found. (Exception from HRESULT: 0x80070490)
       at Microsoft.SqlServer.Interop.MSClusterLib.ISClusResource.get_Disk()
       at Microsoft.SqlServer.Configuration.Cluster.ClusterPhysicalDisk.get_Partitions()
       at Microsoft.SqlServer.Configuration.ClusterConfiguration.ClusterDiskPublicConfigObject.IsPathOnSharedDisk(String path)
       at Microsoft.SqlServer.Configuration.SetupExtension.SlpInputSettings.ValidateNotOnSharedDisk(ValidationState vs, String directoryName, String bindingKey, String errorMessage)
       at Microsoft.SqlServer.Configuration.SetupExtension.SlpInputSettings.Validate_InstallSharedDir(ValidationState vs)
       at Microsoft.SqlServer.Configuration.SetupExtension.SlpInputSettings.ValidateSettings()
       at Microsoft.SqlServer.Configuration.SetupExtension.ValidateFeatureSettingsAction.ExecuteAction(String actionId)
       at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
       at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun)

     

    The stack goes from bottom to Top. If we read the function calls made, anyone can conclude that there is something happening with cluster disks. That’s a good hint. So, I went back to failover cluster manager and looked into Disks under “Storage”. There was a disk which was in failed state. I was not able to bring this online and that’s THE problem! SQL Setup would enumerate the disks to find eligible disks which can be used and it’s not able to find details about that disk. Here was the error when I attempted to bring it online.

    The resource ‘Cluster Disk 1’ did not come online.
    The desired state change for ‘Cluster Disk 1’ did not occur before the timeout expired.

    I realized that I have played with iSCSI and messed up the disk which was presented.

    Solution: Delete the disks which are not able to come online under “Storage > Disks” or “Available Storage” in failover cluster manager interface.

    Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in Cluster, Installation, Setup, SQL Server 2012, SQL Server 2014 | Tagged: , , , , | 3 Comments »

    Latest News : SQL Server 2016 announced – New Features and Enhancements

    Posted by blakhani on May 6, 2015


    From past SQL Server releases, there have been pattern of 2 years release. Here are the release date since SQL 2008 (taken from lifecycle page)

    SQL Server Version Lifecycle Start Date
    Microsoft SQL Server 2008 11/6/2008
    Microsoft SQL Server 2008 R2 7/20/2010
    Microsoft SQL Server 2012 5/20/2012
    Microsoft SQL Server 2014 6/5/2014

     

    Looking at this pattern and trend, anybody would have guessed that next release of SQL would be in year 2016. Recent announcement at Microsoft Ignite event confirmed the same. Here is the blog of the announcement made by our CEO Satya. http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx

    Here are my favorite features taken from sources listed at the end of the blog.

    • Enhancement in AlwaysOn Availability Group:
      • Round-robin load balancing – In SQL Server 2016, it would be possible to define routing of secondary on round robin basis. This would help in load-balancing. If you have worked with SQL Server 2012 and SQL Server 2014 the you would recall that when read-only routing is defined and application makes connection to Listener via read-only intent, the connection would go to first replica in routing list. In SQL Server 2016, we would have option to route requests to various secondary replica in round robin fashion.
      • DTC Support : SQL Server 2016 would support distributed transactions and cross database transactions when database is an availability database. As per earlier documentation, this scenario was unsupported. (Books online, blog)
      • Automatic failover based on database health: This was one of the ask from community (refer this connect item). Imagine a situation where databases are part of AG and the drive containing LDF of one database has gone bad. This would cause database to go unavailable BUT availability group would report healthy. This has been taken care in SQL Server 2016.
      • Up to 3 synchronous replicas for auto failover: In earlier versions there was only one “auto” failover partner. Here was can have more than one. So in case, two servers are down, still the automatic failover can happen.
      • Simplified Add Azure Replica Wizard: This would help in having hybrid deployment of AlwaysOn Availability Groups.
    • Stretch Database: This feature allows “hot” part of a table to be in on-premise SQL Server and cold part would be move to Azure SQL Database automatically. Good part is that it would be transparent to the end user.
    • Security Enhancements
      • Row Level Security: This is another security big win. Now we can control the rows which are visible to user based on the rights given to the logged in user. It is available in SQL Azure Database. (documentation)
      • Always Encrypted: Data on the wire would be encrypted.
      • Dynamic Data Masking: Data obfuscation to avoid unauthorized access. It is already available in SQL Azure Database. (Video, Blog)

    Dynamic Data Masking (Image taken from Channel9 Video)

    • Performance Improvements:
      • Query Data Store: Query plans can be stored along with various statistics in permanent tables. This would help in finding various query and what was the plan used. Parameter sniffing issue would be easy to track and kill.
      • Enhancements in In-memory OLTP: There were some limitations in SQL Server 2014 about In-Memory OLTP and they have been removed.

    There are many more features which would help customers in getting breakthrough mission critical performance, deeper insights across any data on many devices and enable the power of hyperscale cloud to unlock new hybrid scenarios. (quote taken from datasheet)

    Sources:

    My Plan: As soon as public preview is out, I would start learning series for SQL Server 2016. This would be similar to what I have done earlier for SQL Server 2014.

    Stay Tuned!

    Posted in SQL 2016 Learning Series | Tagged: , , , | 2 Comments »