SQL 2012 AlwaysOn Availability - Replica role showing as Resolving

I was faced an situation in AlwaysOn on one of  the Production server which was showing the error message as
Error: 983, Severity: 14, State: 1.

Unable to access database ‘DBNAME’ because its replica role is RESOLVING which does not allow connections. Try the operation again later.

When googling about the issue i found an useful blog in MSDN which iam sharing below :

Thanks to Denzil Ribeiro for posting this article.

I was dealing with an issue where we had an AlwaysOn availability group with 2 replicas configured for Automatic failover. There was a 30 second glitch where folks could not connect to the Database on the primary and automatic failover did not kick in. Well, that was our initial impression at least. The purpose of this post is to expose you to the different logs available in troubleshooting AlwaysOn Availability group issues, not so much on this particular problem itself.

Symptoms on Primary:  Connections failed for a brief moment with the error below and then was all good.

Error: 983, Severity: 14, State: 1.

Unable to access database ‘HADB’ because its replica role is RESOLVING which does not allow connections. Try the operation again later.

So there were 3 questions to answer:

a.      What was the reason for the error?

b.      Why didn’t automatic failover kick in? Or did it?

c.      Was it supposed to fail over to the other node?

First of all we need to understand the FailureConditionLevel which controls when failover occurs both from an SQL FCI (failover cluster) or AlwaysOn Availability group Automatic failover perspective.  For detailed information regarding Failover Policies for Failover Cluster Instances, refer to this article:  http://msdn.microsoft.com/en-us/library/ff878664.aspx

In my case the FailoverConditionLevel is set to 5 (Default is 3).  This setting can be altered with the following TSQL script:

If I look at the article referenced above, I notice that the FailoverConditionLevel has the following attributes:

5

Indicates that a server restart or failover will be triggered if any of the following conditions are raised: SQL Server service is down. SQL Server instance is not responsive (Resource DLL cannot receive data from sp_server_diagnostics within the HealthCheckTimeout settings). System stored procedure sp_server_diagnostics returns ‘system error’. System stored procedure sp_server_diagnostics returns ‘resource error’. System stored procedure sp_server_diagnostics returns ‘query_processing error’.

One thing to note here is that the Cluster action is only if any of the subsystems report an “error”, no action is taken on a warning.

So effectively what happens is the following:

•          Cluster service runs LooksAlive check
  
•          Sp_server_diagnostics results sent to Resource Monitor DLL
  
•          Resource Monitor DLL detects any error state and notifies the cluster service
  
•          Cluster Service takes the resource offline
  
•          Notifies SQL Server to issue an internal command to take the availability group offline.
  
• There is also the whole concept of a lease that is explained here:
  

http://blogs.msdn.com/b/psssql/archive/2012/09/07/how-it-works-sql-server-alwayson-lease-timeout.aspx

In order to understand this better I attempted and was able to reproduce the scenario.

I then looked at the Cluster Diagnostic extended event Log, the AlwaysOn extended event log, the cluster log, and the SQL Server error log to try to piece together what exactly happened.

Cluster Diagnostic Extended Event Log:

We see from this log that the System component did throw an error. Which equates to there were N number of dumps created and or a spinlock orhpaned after an Access violation or a detected Memory scribbler condition

The  Cluster Diagnostics Logs are located in the Log directory as shown below  and are different log files than the cluster log itself.

They are of the format : ServerName_InstanceName_SQLDIAG_*.xel

As we can see below, you see the component_health_result indicate that the system component of sp_server_diagnostics returned an error, when the resource monitor than interpretted as a Failure due to the FailureConditionLevel set, and propagated the resource “NOTHEALTH” to the cluster service which triggered the LooksAlive check to return “not alive” or false status.

AlwaysOn Extended Event log

The AlwaysOn Health Extended Event logs cover the Availability Group related diagnostics such as State changes for the Group or Replica or Databases, errors reported, lease expiration and any Availability Group Related DDL that is executed. The format of the logs is:AlwaysOn_health*.xel

If we look at the log snippet below, we see that the AG lease expired, and that triggered us to attempt a failover which in turn changes the state from PRIMARY_NORMAL to RESOLVING_NORMAL.

Cluster Log

Note: The times are in UTC so you have to convert them to match up with the other log files.

To generate the log: How to Generate a Cluster Log on Windows 2008 onwards

00006918.00015978::2013/04/03-18:54:37.251 INFO  [RES] SQL Server Availability Group: [hadrag] SQL Server component ‘system’ health state has been changed from ‘warning’ to ‘error’ at 2013-04-03 11:54:37.247

00006918.00014ef4::2013/04/03-18:54:37.970 ERR   [RES] SQL Server Availability Group: [hadrag] Failure detected, the state of system component is error

00006918.00014ef4::2013/04/03-18:54:37.970 ERR   [RES] SQL Server Availability Group< 2012AG>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel

00006918.00014ef4::2013/04/03-18:54:37.970 ERR   [RES] SQL Server Availability Group< 2012AG>: [hadrag] Resource Alive result 0.

00006918.00014ef4::2013/04/03-18:54:37.970 ERR   [RES] SQL Server Availability Group< 2012AG>: [hadrag] Resource Alive result 0.

00006918.00014ef4::2013/04/03-18:54:37.970 WARN  [RHS] Resource 2012AG IsAlive has indicated failure.

00019d20.00000e5c::2013/04/03-18:54:37.970 INFO  [RCM] HandleMonitorReply: FAILURENOTIFICATION for ‘2012AG’, gen(0) result 1.

00019d20.00000e5c::2013/04/03-18:54:37.970 INFO  [RCM] TransitionToState(2012AG) Online–>ProcessingFailure.

00019d20.00000e5c::2013/04/03-18:54:37.970 INFO  [RCM] rcm::RcmGroup::UpdateStateIfChanged: (2012AG, Online –> Failed)

00019d20.00000e5c::2013/04/03-18:54:37.970 INFO  [RCM] resource 2012AG: failure count: 1, restartAction: 2.

00019d20.00000e5c::2013/04/03-18:54:37.970 INFO  [RCM] Will restart resource in 500 milliseconds.

-        If you see the “restart action” highlighted above, a restart is attempted on the current node first before failing over to the other node and in this case the restart is successful so it doesn’t really fail over to the other node. If we take a look at the cluster Availability group Resource Properties, you can confirm that the Restart action does indicate that a restart will be attempted on the current node first

-        

00019d20.00019418::2013/04/03-18:55:06.079 INFO  [RCM] TransitionToState(2012AG) DelayRestartingResource–>OnlineCallIssued.

00019d20.00019418::2013/04/03-18:55:06.079 INFO  [RCM] HandleMonitorReply: ONLINERESOURCE for ‘2012AG’, gen(1) result 997.

00019d20.00019418::2013/04/03-18:55:06.079 INFO  [RCM] TransitionToState(2012AG) OnlineCallIssued–>OnlinePending.

…

00006918.0001f1c0::2013/04/03-18:55:07.298 INFO  [RHS] Resource 2012AG has come online. RHS is about to report status change to RCM

SQL Server Errorlog

More details on Lease Expiration: http://blogs.msdn.com/b/psssql/archive/2012/09/07/how-it-works-sql-server-alwayson-lease-timeout.aspx 

2013-04-03 11:54:43.59 Server      Error: 19407, Severity: 16, State: 1.

2013-04-03 11:54:43.59 Server      The lease between availability group ‘2012AG’ and the Windows Server Failover Cluster has expired. A connectivity issue occurred between the instance of SQL Server and the Windows Server Failover Cluster. To determine whether the availability group is failing over correctly, check the corresponding availability group resource in the Windows Server Failover Cluster.

2013-04-03 11:54:43.64 Server      AlwaysOn: The local replica of availability group ‘2012AG’ is going offline because either the lease expired or lease renewal failed. This is an informational message only. No user action is required.

2013-04-03 11:54:43.64 Server      The state of the local availability replica in availability group ‘2012AG’ has changed from ‘PRIMARY_NORMAL’ to ‘RESOLVING_NORMAL’. The replica state changed because of either a startup, a failover, a communication issue, or a cluster error. For more information, see the availability group dashboard, SQL Server error log, Windows Server Failover Cluster management console or Windows Server Failover Cluster log.

2013-04-03 11:54:43.84 spid31s     The availability group database "HADB" is changing roles from "PRIMARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.

2013-04-03 11:54:43.84 spid27s     AlwaysOn Availability Groups connection with secondary database terminated for primary database ‘HADB’ on the availability replica with Replica ID: {89c5680c-371b-45b9-ae19-2042d8eec27b}. This is an informational message only. No user action is required.

n  The error below can occur if the Local Log records are hardened but quorum is lost with the cluster so the remote harden cannot be completed.

2013-04-03 11:54:45.16 spid58      Remote harden of transaction ‘user_transaction’ (ID 0x00000000001ee9e2 0000:000006eb) started at Apr  3 2013 11:54AM in database ‘HADB’ at LSN (37:28:204) failed.

2013-04-03 11:54:46.42 spid31s     Nonqualified transactions are being rolled back in database HADB for an AlwaysOn Availability Groups state change. Estimated rollback completion: 100%. This is an informational message only. No user action is required.

n  This phase is after the “restart action” as seen in the cluster log where we are attempting a restart on the same node before failing over to the other node.

2013-04-03 11:55:06.25 spid58      AlwaysOn: The local replica of availability group ‘2012AG’ is preparing to transition to the primary role in response to a request from the Windows Server Failover Clustering (WSFC) cluster. This is an informational message only. No user action is required.

2013-04-03 11:55:07.27 spid58      The state of the local availability replica in availability group ‘2012AG’ has changed from ‘RESOLVING_NORMAL’ to ‘PRIMARY_PENDING’. The replica state changed because of either a startup, a failover, a communication issue, or a cluster error. For more information, see the availability group dashboard, SQL Server error log, Windows Server Failover Cluster management console or Windows Server Failover Cluster log.

2013-04-03 11:55:07.55 Server      The state of the local availability replica in availability group ‘2012AG’ has changed from ‘PRIMARY_PENDING’ to ‘PRIMARY_NORMAL’. The replica state changed because of either a startup, a failover, a communication issue, or a cluster error. For more information, see the availability group dashboard, SQL Server error log, Windows Server Failover Cluster management console or Windows Server Failover Cluster log.

So in answering the 3 prior questions I had with the logs

a.      The reason we got into this state was the system component reported an error ( was a bunch of exceptions), we won’t go into those here

b.      Failover was attempted, but initial attempt is to restart on the same node and it did end up coming online on that node.

c.      No, it should not have failed over to the other node

Hope the exposure to these logs is helpful in troubleshooting AlwaysON Availability group issues

Ref : https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/04/08/sql-2012-alwayson-availability-groups-automatic-failover-doesnt-occur-or-does-it-a-look-at-the-logs/

SSPI Errors in SQL SERVER.

I see a lot of issues related to SQL Server connectivity. One common error I see in the SQL Server logs is the SSPI error.

Logon Error: 17806, Severity: 20, State: 2. 
Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT:192.168.0.5] 
Logon Error: 18452, Severity: 14, State: 1. 
Logon Login failed for user ”. The user is not associated with a trusted SQL Server connection. [CLIENT:192.168.0.5]

We normally see two kinds of SSPI errors. One is “Cannot generate SSPI context” and the other is “SSPI Handshake Failed”. The first error is commonly because the client is trying a Kerberos authentication and that failed, but it did not fall back to NTLM. The second one happens usually when the user is not authenticated.

Log Name: Security 
Source: Microsoft-Windows-Security-Auditing 
Date: 1/15/2011 2:52:01 PM 
Event ID: 4625 
Task Category: Logon 
Level: Information 
Keywords: Audit Failure 
User: N/A 
Computer: SQLMACHINE.corp.mydomain.com 
Description: 
An account failed to log on. 
Subject: 
Security ID: NULL SID 
Account Name: - 
Account Domain: - 
Logon ID: 0x0 
Logon Type: 3 
Account For Which Logon Failed: 
Security ID: NULL SID 
Account Name: APPSERVER$ 
Account Domain: CORP.MYDOMAIN.COM 
Failure Information: 
Failure Reason: Unknown user name or bad password. 
Status: 0xc000006e 
Sub Status: 0x0 
Process Information: 
Caller Process ID: 0x0 
Caller Process Name: - 
Network Information: 
Workstation Name: - 
Source Network Address: - 
Source Port: - 
Detailed Authentication Information: 
Logon Process: Kerberos 
Authentication Package: Kerberos 
Transited Services: - 
Package Name (NTLM only): - 
Key Length: 0

In this scenario, the app was trying to connect with the machine account. Typically when using windows authentication, if the application is running in the context of Local System or Network Service, the application will connect using the machine account. If you take a Profiler trace, the account name is shown as MachineName$.  
If we add the APPSERVER$ account to the Administrators group of the SQL Server machine, we don’t see the problem.

Ref :

https://blogs.msdn.microsoft.com/dipanb/2010/12/08/sspi-handshake-failed-could-result-when-the-security-event-log-has-reached-the-maximum-log-size/

Upgrading SQL Server 2000 to SQL Server 2008

High-level Upgrade Project Plan
Here is a high level checklist which can be built upon to derive an actual project plan.

On the current production:
Before Upgrade:

• Best Practice Analyzer for SQL Server 2000
• Execute Upgrade Advisor and resolve necessary issues
  • Backward compatibility
  • Deprecated features
  • Discontinued features
  • Breaking changes
  • Behavior changes
  • SQL-DMO  (Use SMO)
  • Osql/isql Utility (Use SQLcmd)
  • Rebuild.exe (Rebuilddatabases option from setup.exe)
  • SQLMail (Use Database mail)
  • SQL Server 2008 upgrade assistant (Optional) or SQL Server Profiler (Replay Trace)
  • Continue capturing baselines of performance data and queries
    • SQLDiag/PSSDiag
    • RML Utilities: http://support.microsoft.com/kb/944837
    • SQL Nexus: http://www.codeplex.com/SQLNexus
    • PAL (Performance Analysis of Logs): http://www.codeplex.com/PAL
    • Identify and tune high-resource consumption queries
    • Run DBCC CHECKDB on the databases to be upgraded to make sure that they are in a consistent state.
    • Make sure that the user databases to be upgraded are set to autogrow and that the PRIMARY file group of each user database has sufficient disk space. Additional disk space is required to allow for the additional space that is required for the PRIMARY file group when you install SQL Server 2008. You can turn off this option after the upgrade is complete.
    • Make sure that the log file for each user database is set to autogrow and has sufficient additional disk space. Additional space is required by transaction log files of user databases. You can turn off this option after the upgrade is complete if it is required by your application or database maintenance plan.
    • Set the AUTO_UPDATE_STATISTICS option to ON before upgrading to SQL Server 2008. Statistics are not upgraded as part of the upgrade process, and relying on statistics from previous SQL Server releases might result in suboptimal query plans. By setting the AUTO_UPDATE_STATISTICS option to ON, all statistics are updated when they are first referenced.
    • Make sure that no pending replication activities on server. Stop & Break the Replication. 
    • Stop & Break the Log shipping
    • Make sure that you have current backups of the databases that you are upgrading by using the BACKUP DATABASE command, and verify their validity by using RESTORE VERIFY ONLY before you start the upgrade process. If you use the detach/attach method, you should make sure that you copy instead of move the data files so that in the unlikely event something should go wrong, you can quickly and easily reattach your data files on the original SQL Server 2000 or SQL Server 2005 instance.

On UAT Server:
Before Upgrade:

• Deploy new hardware and OS / cluster
• Run SQLIOSim and other pre-deployment checks on new hardware
• Install SQL Server 2008 on new server with all patches and optimizations
• Establish a UAT environment for SQL Server 2008  and Install latest Service Pack

During Upgrade:

• Restore fresh copies of the production databases on UAT

After Upgrade:

• Set the database compatibility levels to 100
• Execute DBCC CHECKDB WITH DATA_PURITY to check the database for column values that are not valid or are out of range. After you have successfully run DBCC CHECKDB WITH DATA_PURITY against an upgraded database, you do not have to specify the DATA_PURITY option again because SQL Server will automatically maintain "data purity." This is the only DBCC CHECKDB check that you must run as a post-upgrade task.
• Update statistics on all databases after you upgrade them. Execute UPDATE STATISTICS in user-defined tables in SQL Server databases. (Full Scan)
• Execute DBCC UPDATEUSAGE on all attached databases to update usage counters and make sure that correct values exist for table and index row counts.
• Repopulate full-text catalogs.
• Migrate logins, jobs to new SQL instances (Save the script to run on new prod server)
  • Logins ( sp_helprevlogin)
  • Jobs
  • Linked Servers
  • Setup Replication
  • Setup Log Shipping
  • Make sure that the relational databases are working correctly by executing a sample set of queries.
  • Update any scripts affected by SQL Server 2008 behavior changes. (Save the script to run on new prod server)
  • Modify the impacted code (listed later on) to ensure that it runs in compatibility level 100. (Save the script to run on new prod server)
  • Take special care for linked servers – they should not point to production
  • Run System Monitor – SQL Server: Deprecated Feature Object also run profiler to capture deprecation events
  • Conduct testing for modified code on the UAT
  • Conduct stress testing of the system
  • Capture performance related data and compare with baselines from production
    • SQLDiag/PSSDiag
    • RML Utilities: http://support.microsoft.com/kb/944837
    • SQL Nexus: http://www.codeplex.com/SQLNexus
    • PAL (Performance Analysis of Logs): http://www.codeplex.com/PAL
    • If necessary troubleshoot any performance issues with help from Microsoft
    • Verify that functionally we are good to go

On new Prod Server:
Before Upgrade:

• Deploy new hardware and OS / cluster
• Run SQLIOSim and other pre-deployment checks on new hardware
• Install SQL Server 2008 on new server with all patches and optimizations
• Establish a UAT environment for SQL Server 2008  and Install latest Service Pack

During upgrade:

• Move the databases to the new instances by using one of the following options.
  • Backup and restore
  • Detach and attach
  • Manual schema rebuild and data export/import
  • Log shipping
  • Copy Database Wizard

After Upgrade:

• If required, take the older instances offline
• If required, rename the new instances to match the old instance names
• Take the older databases offline to avoid accidental usage / updates to the old copies
• Repeat (if required) the previous 2 steps for the rest of the databases
• Set the database compatibility levels to 100
• Execute DBCC CHECKDB WITH DATA_PURITY to check the database for column values that are not valid or are out of range. After you have successfully run DBCC CHECKDB WITH DATA_PURITY against an upgraded database, you do not have to specify the DATA_PURITY option again because SQL Server will automatically maintain "data purity." This is the only DBCC CHECKDB check that you must run as a post-upgrade task.
• Update statistics on all databases after you upgrade them. Execute UPDATE STATISTICS in user-defined tables in SQL Server databases. (Full Scan)
• Execute DBCC UPDATEUSAGE on all attached databases to update usage counters and make sure that correct values exist for table and index row counts.
• Repopulate full-text catalogs.
• Migrate logins, jobs to new SQL instances  (If script is generated on UAT then use that script)
  • Logins ( sp_helprevlogin)
  • Jobs
  • Linked Servers
  • Setup Replication
  • Setup Log Shipping
  • Update any scripts affected by SQL Server 2008 behavior changes. (Use script generated on UAT Server)
  • Take special care for linked servers – they should point to appropriate servers
  • Modify the impacted code (listed later on) to ensure that it runs in compatibility level 100 (Use script generated on UAT Server)
  • Make sure that the relational databases are working correctly by executing a sample set of queries.
  • Conduct stress testing of the system
  • Capture performance related data and compare with baselines from production
    • SQLDiag/PSSDiag
    • RML Utilities: http://support.microsoft.com/kb/944837
    • SQL Nexus: http://www.codeplex.com/SQLNexus
    • PAL (Performance Analysis of Logs): http://www.codeplex.com/PAL
    • Setup Replication
    • Setup Log shipping
    • Change the data source in reporting services (Point to the new servers)

Additional steps for excellence (Post Upgrade)

• Making appropriate SQL Server settings using SQL Server 2008 Policy-Based Management.
• Configure Management Data Warehouse
• Using Database Mail instead of SQL Server 2000 SQL Mail.
• Recreating SQL Server Agent jobs and SQL Server 2000 or SQL Server 2005 maintenance plans.
• Transferring external Transact-SQL scripts to the correct location for the new instance.
• Configure/update server logins on the new instance and database users in the upgraded database.
• Configure alerts on the new instance.
• Configure DTS/SSIS packages on the new instance.
• Update connection strings at clients so that they can connect to the new instance, unless you are replacing the old server with a new server that has the same identity.
• Create <Nos of physical CPUs> TEMPDB data files, each of same size.
• Don’t add Startup flags which you were using on old server (8207,1204, 3605)
• Make sure that you do not have Virtual log file (VLF) Fragmentation issue
• Change Page verification setting to checksum at database level for all user databases
• Implement Database Mirroring
• Make a use of Data and Backup compression
• Make a use of Server /Database level Auditing
• Make a use of Resource Governor
• Make a use of Central Server Management
• Make a use of Transparent Data Encryption
• Make a use of Extensible Key Management

Contingency plan:

• Define SLA for acceptance of the new system and for rollback time if required
• Plan for a rollback action if required. Consider that the rollback might even be required many days after the actual upgrade was conducted.
• Identify data comparison tools which may be required to reconcile the data from the upgraded system back to the rollback system.
• Have simple scripts (such as a CHECKSUM based script) to check for any data discrepancies.

Develop Acceptance Criteria and Rollback Steps:
An upgrade plan must consider all possible possibilities. Some might be ignored as improbable, but for all likely scenarios, the highest priorities must be to protect any production data and to be able to restore the system to its original state if it is required. The following tasks help provide that protection:

• Back up production data. In the upgrade checklist, include steps for backing up all the databases and other data that would be required to rebuild the system.
• Develop acceptance criteria and a go/no-go decision point. As part of the upgrade checklist, at some point decide whether the upgrade is successful and the system can be put back into production. The final go/no-go decision might involve a team of people, including the testers who determine whether the application operates as expected.
• Have a rollback plan in place. Specify in sufficient detail how to restore the system if it is necessary. The more detailed the plan, the better, because rollbacks usually occur in high-stress situations. Clearly defined steps are easier to follow in those contexts.
• Test the rollback. Test the rollback plan to make sure that it will actually work. The degree of testing might be a function of how important the data is and how time-critical a rollback would be. There can be no confidence in an untested rollback plan.

General Post-Upgrade Tasks:

Full-Text Upgrade. Any databases that were marked full-text enabled or disabled before the upgrade will maintain that status after the upgrade. After the upgrade, the full-text catalogs will be rebuilt and populated automatically for all full-text-enabled databases. This is a time- and resource-consuming operation. For more information, see Chapter 6, "Full-Text Search."

Database Maintenance Plans. Database maintenance plans in SQL Server 2000 consisted of Transact-SQL commands executed by SQL Server Agent. Starting with SQL Server 2005, database maintenance plans are SSIS packages. If you upgrade from SQL Server 2000 to SQL Server 2008, your database maintenance plans will still work. However, they will be listed under the legacy branch of SSMS. To upgrade these packages to SQL Server 2008, right-click the plan you want to upgrade and select Migrate. For more information about this process, see Chapter 1, "Upgrade Planning and Deployment," and Chapter 2, "Management and Development Tools." You can also find more information in How to: Migrate SQL Server 2000 Database Maintenance Plans in SQL Server Books Online.

Following features are disabled by default in new installations:

• Ad hoc distributed queries
• Automation
• SQL Mail
• Web Assistant stored procedures
• Named pipes
• xp_cmdshell

Upgrading Log Shipped Databases:
This section covers how to upgrade databases that are using the built-in log shipping feature. This section assumes an in-place upgrade of the instance containing either the primary or secondary database.

Feature Changes in SQL Server 2008 Log Shipping:
Unlike in failover clustering, any objects that reside outside the database itself must be manually accounted for. This means that items such as SQL Server Agent jobs, SQL Server-level logins, linked servers, and anything not self-contained within the database must be either scripted or moved to the destination server in another way. Think of this process as a disaster recovery drill: you need to take the same steps to prepare for the upgrade as you would if preparing to perform a role change. See "Script or Export All Objects " above for information about getting objects out of SQL Server and how to perform such tasks as synchronizing logins.

Whether you want to upgrade a SQL Server 2000 database participating in log shipping or a SQL Server 2005 database participating in log shipping, if an upgrade occurs and the secondary database is left in either STANDBY or NORECOVERY mode, the secondary database will not be upgraded to a SQL Server 2008 database until it is brought online WITH RECOVERY.

SQL Server 2008 supports one new log shipping feature not in SQL Server 2005 log shipping: backup compression. Backup compression, available only in SQL Server 2008 Enterprise, by default is not enabled after the upgrade to SQL Server 2008. To enable it, go into the log shipping configuration and manually enable it on the primary database.

Log Shipping Upgrade Scenarios:
SQL Server 2000 and SQL Server 2005 have similar upgrade scenarios for log shipping. The two scenarios are upgrading with or without a role change. A role change is the log shipping-specific terminology for the process of switching the current primary database to promote the secondary database as the new primary.

Note: When upgrading without a role change and either preserving or reconfiguring log shipping, as long as the primary database and any secondary databases are at the same point, the secondary will be upgraded once log shipping is restarted or reconfigured. This works because the database upgrade is a logged operation: Once the transaction logs start flowing again to the secondary database, it will be upgraded as part of the log shipping process. This scenario greatly speeds up the upgrade process and reduces the number of steps required.

Upgrade with a Role Change:
In this upgrade scenario for log shipping, a role change is performed to shorten downtime. When upgrading with a role change, the only time the database should be unavailable is during the actual switch from the primary database to the secondary database. In theory, the process sounds simple, but in reality, it is more complex.

When the instance containing the secondary database is upgraded, the secondary database will not be upgraded because it is still in a state to accept the loading of transaction log backups. This means that after the instance is upgraded, there is the ability to apply the transaction logs from the primary database. However, once the cutover is started, the primary database can no longer be used because there is no way to keep the two databases synchronized: when a database is brought online WITH RECOVERY, it can no longer accept transaction logs. In addition, after the secondary becomes the new primary, all users and applications would need to be redirected to the new instance and database. If that process is not centrally managed, many desktops would need to be touched to achieve the switch. Thus, this is not a recommended upgrade scenario.

At this point, although the old primary database could be upgraded, log shipping would no longer be configured. You would have to completely reconfigure log shipping, which means making a backup from the new primary and restoring it on the "new" secondary (the old primary). If the database is small, that might not be difficult. But if you are dealing with a VLDB or limited network bandwidth for copying a full database backup—not to mention the time it would take to restore—upgrading without a role change might be a better option.

On the plus side, besides reducing downtime, upgrading with a role change provides a built-in fallback plan. If the SQL Server 2008 upgrade does not work for any reason or is not accepted after a few days, the original instance and database would be nearly ready to go. You would just need to use a manual method of getting the updated data out of SQL Server 2008.

Upgrade without a Role Change:
When upgrading without a role change, an upgrade is performed on an existing instance of SQL Server without doing the role change. This will preserve the state of the databases. But depending on the version of SQL Server you are upgrading (see the specific sections for SQL Server 2000 and SQL Server 2005 below), it might or might not upgrade log shipping in the process. This option generally has more downtime associated with it, but for those who cannot afford to reinitialize log shipping, upgrading without a role change might be a better option.


Unconfigure Log Shipping:
Similar to upgrading without a role change is the option of completely removing the log shipping configuration for a particular database. This would yield a result similar to upgrading without a role change. But in the case of SQL Server 2000, it might leave a cleaner upgrade, as we will discuss in the next section. Because a SQL Server 2008 transaction log cannot be applied to a SQL Server 2000 or SQL Server 2005 database, log shipping would need to be reinitialized.

Upgrading from SQL Server 2000 to SQL Server 2008 Log Shipping:
There is no direct upgrade path for log shipping configurations on a SQL Server 2000 database. However, you can migrate from SQL Server 2000 log shipping to SQL Server 2008 log shipping. Microsoft first introduced log shipping in SQL Server 2000 Enterprise. Log shipping was configured as part of the Database Maintenance Plan Wizard. Although it was mainly used to expose stored procedures triggered by SQL Server Agent jobs as well as xp_sqlmaint, it could not be set up via stored procedures. When Microsoft released SQL Server 2005, it changed how log shipping could be configured, and log shipping was no longer integrated and associated with a database maintenance plan.

Upgrading Replicated Databases 
Side-by-Side Upgrade to a New Server or Cluster:
Snapshot replication tends to be the easiest in terms of a non-in-place upgrade because it is the least complex process.

1. In the replication topology, where an in-place upgrade of an existing instance to SQL Server 2008 will not be done, install a new instance of SQL Server 2008. This might be for a Publisher, a Distributor, or a Subscriber.

1. Generate scripts for the entire existing SQL Server 2000 or SQL Server 2005 replication topology.
2. Upgrade the scripts to SQL Server 2008, and make sure that the instance names and databases are updated to reflect their new locations. Before upgrading the scripts, make copies so that the old environment can be restored if necessary.
3. The Distributor must be SQL Server 2008. Run the appropriate upgraded replication script at the Distributor to create publication and distribution. At this point, the old replication topology is still in use.
4. To do the cutover, stop all traffic and kill any connections into the Publisher to ensure that no one tries to access it during the upgrade.
5. Assuming the Publisher is also going to be the main application database, upgrade it using one of the methods described in "Methods for New Hardware and Side-by-Side Upgrades," above. Just using a snapshot will not necessarily move the entire database. Also, ensure that moving the database to a new instance follows whatever approved and supported procedures the application vendor provides. Do not just move it.
6. Run the appropriate upgraded script at the Publisher.
7. Run the appropriate upgraded scripts at the Subscriber to connect it to the new replication topology. If the Subscriber is not a new instance of SQL Server 2008, make sure it is at a version of SQL Server that can be supported in a SQL Server 2008 replication topology. The Subscriber will need to be completely initialized. Remember to script and migrate any objects that are required by the database, but which are not accounted for by replication.
8. Verify that the replication agents and jobs are started with no errors.
9. Verify that transactional replication is working via the Replication Monitor.
10. Configure administration such as backup jobs for the database.
11. Generate new scripts for the upgraded replication architecture.
12. To ensure that no one will connect to the old environment, it is recommended that you stop the services if possible on the original topology after it has been moved to the new instance. For more guidance, see the section "Decommissioning and Uninstalling After a Side-by-Side or New Hardware Upgrade" earlier in this chapter.
13. The newly upgraded SQL Server environment is now ready for use by applications and end users. Point them to the new locations if necessary.

Side-by-Side Upgrade to a New Server or Cluster:
When not upgrading merge replication in-place, the upgrade process complexity will depend on the complexity of the replication topology and which components will be changed to SQL Server 2008. Tackle the switch to the new instances in logical groups, starting from the top down.

1. In the replication topology, where an in-place upgrade of an existing instance to SQL Server 2008 will not be performed, install a new instance of SQL Server 2008. This may be for a Publisher, a Distributor, or a Subscriber.
2. Generate scripts for the entire existing SQL Server 2000 or SQL Server 2005 replication topology.
3. Upgrade the scripts to SQL Server 2008 and make sure that the instance names and databases are updated to reflect their new locations. Before upgrading the scripts, make copies so that the old environment can be restored if necessary.
4. Ensure that the existing Publisher is at a version that can participate in the replication topology when SQL Server 2008 is added.
5. The Distributor must be SQL Server 2008. Run the appropriate upgraded replication script at the Distributor to create publication and distribution. At this point, the old replication topology is still being used.
6. If necessary, run the appropriate upgraded replication script at the Publisher. At this point, you are still actively using the old replication topology.
7. To upgrade one tier of merge replication, follow these steps:
8. Initialize the new Publisher. Assuming the Publisher is going to be the main application database as well, migrate it using one of the methods described in "Methods for New Hardware and Side-by-Side Upgrades" earlier in this chapter. Just using a snapshot will not necessarily capture the entire database in the same way a backup will. Also, ensure that migrating the database to a new instance follows whatever approved and supported procedures the application vendor or developers provide. Do not just move it.
9. Configure a new subscription from the old Publisher to the new Publisher.
10. To do the cutover, stop all traffic and kill any connections into the Publisher to ensure that no one tries to access it during the upgrade. Because both sides can update data, no traffic or connections should be allowed at either side.
11. Make sure that no transactions are left to replicate.
12. After all transactions have been replicated, delete the subscription from the old Publisher to the new Publisher.
13. Run the appropriate upgraded script at the Publisher to add it to the new replication topology.
14. Run the appropriate upgraded scripts at the Subscriber to connect it to the new replication topology. If the Subscriber is not a new instance of SQL Server 2008, make sure it is at a version of SQL Server that can be supported in a SQL Server 2008 replication topology. We recommend that you delete the old subscription to ensure a clean configuration. This will mean a complete re-synchronization of the Subscriber.
15. Verify that the replication agents and jobs are started with no errors.
16. Verify that merge replication is working via the Replication Monitor. Also, insert some "dummy" data and make sure it propagates to all Subscribers.
17. Remember to script and migrate any objects required by the database that are not accounted for by replication.
18. Configure administration such as backup jobs for the database.
19. Generate new scripts for the upgraded replication architecture.
20. To ensure that no one will connect to the old environment, it is recommended that you stop the services if possible on the original topology after it has been migrated to the new instance. For more information, see the section "Decommissioning and Uninstalling After a Side-by-Side or New Hardware Upgrade" earlier in this chapter.
21. The newly upgraded SQL Server environment is now ready for use by applications and end users. Point applications and clients to the new locations if necessary.

Side-by-Side Upgrade to a New Server or Cluster: 
When transactional replication will not be upgraded in-place, determine the complexity of the topology. Tackle the switch to the new instances in logical groups, starting from the top down. The complexity of this form of upgrade will depend on which pieces are going to be changed.
In the replication topology, where you are not doing an in-place upgrade of an existing instance to SQL Server 2008, install a new instance of SQL Server 2008. This may be for a Publisher, a Distributor, or a Subscriber.

1. Generate scripts for the entire existing SQL Server 2000 or SQL Server 2005 replication topology.
2. Upgrade the scripts to SQL Server 2008 and make sure that the instance names and databases are updated to reflect their new locations. Before upgrading the scripts, make copies so that the old environment can be restored if necessary.
3. Ensure that the existing Publisher is at a version that can participate with SQL Server 2008 in the topology.
4. The Distributor must be SQL Server 2008. Run the appropriate upgraded replication script at the Distributor to create publication and distribution. At this point, the old replication topology is in use.
5. If necessary, run the appropriate upgraded replication script at the Publisher. At this point, the old replication topology is still being used.
6. To upgrade one tier of transactional replication, follow these steps:
   1. Initialize the new Publisher. Assuming the Publisher is going to be the main application database as well, migrate it using one of the methods described in "Methods for New Hardware and Side-by-Side Upgrades" earlier in this chapter. Just using a snapshot will not necessarily capture the entire database. Also, ensure that migrating the database to a new instance follows whatever approved and supported procedures the application vendor or developers provide. Do not just move it.
   2. Configure a new subscription from the old Publisher to the new Publisher.
   3. To do the cutover, stop all traffic and kill any connections into the Publisher to ensure that no one tries to access it during the upgrade. If peer-to-peer, updating Subscribers, and/or bi-directional transactional replication are being used, ensure that neither side has any connections to the Publishers.
   4. Make sure that no transactions are left to replicate. Run sp_repltrans at the Publisher to get a list of the outstanding transactions marked for publication.
   5. Once the result set is empty, delete the subscription from the old Publisher.
   6. Run the appropriate upgraded script at the Publisher to add it to the new replication topology.
   7. Run the upgraded scripts at the Subscriber to connect it to the new replication topology. If the Subscriber is not a new instance of SQL Server 2008, make sure it is at a version of SQL Server that can be supported in a SQL Server 2008 replication topology. We recommend that the old subscription be erased to ensure a clean configuration. This will mean a complete re-synchronization of the Subscriber.
   8. Verify that the replication agents and jobs are started with no errors.
   9. Verify that transactional replication is working via the Replication Monitor. Also, insert some "dummy" data and make sure it propagates to all Subscribers.
   10. Remember to script and migrate any objects required by the database that are not accounted for by replication.
   11. Configure administration such as backup jobs for the database.
   12. Generate new scripts for the upgraded replication architecture.
       1. To ensure that no one will connect to the old environment, it is recommended that you stop the services if possible on the original topology after it has been migrated to the new instance. For more guidance, see the section "Decommissioning and Uninstalling After a Side-by-Side or New Hardware Upgrade" in this chapter.
       2. The newly upgraded SQL Server environment is now ready for use by applications and end users. Point applications and clients to the new locations if necessary.

Note on moving logins between SQL 2005 to SQL 2008:
Sp_helprevlogin changes for SQL 2008: http://support.microsoft.com/kb/918992  (‘How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008’)

Side-by-side Upgrade:
In a side-by-side upgrade, instead of directly replacing the older instance of SQL Server, required database and component data is transferred from an instance of SQL Server 2000 or SQL Server 2005 to a separate instance of SQL Server 2008. It is called a "side-by-side" method because the new instance of SQL Server 2008 runs alongside the legacy instance of SQL Server 2000 or SQL Server 2005, on the same server or on a different server.

There are two important options when you use the side-by-side upgrade method:

• You can transfer data and components to an instance of SQL Server 2008 that is located on a different physical server or on a different virtual machine, or
• You can transfer data and components to an instance of SQL Server 2008 on the same physical server

Both options let you run the new instance of SQL Server 2008 alongside the legacy instance of SQL Server 2000 or SQL Server 2005. Typically, after the upgraded instance is accepted and moved into production, you can remove the older instance.

Whether a side-by-side upgrade is to a separate instance on the same server or to a new instance on another server, data must be transferred in what is mostly a manual process. The result is two instances, legacy and new, that can run side by side.
As just noted, the key point in a side-by-side upgrade is that you must manually transfer data files and other supporting objects from the older instance of SQL Server to the instance of SQL Server 2008. The SQL Server 2008 Setup program will not perform this task. The objects that you must transfer include the following:

• Data files
• Database objects
• SSAS cubes
• Configuration settings
• Security settings
• SQL Server Agent jobs
• SSIS packages

Steps for using Upgrade Assistant:

1. Prepare a test environment by setting up test servers that have the necessary software and appropriate configuration.
2. Upgrade Assistant then guides the backing up relevant application databases and capturing the application database workload.
3. It transfers the relevant files and databases to the test environment.
4. The tool then runs Upgrade Advisor on the test databases to analyze database schema, trace files, and script files for potential compatibility issues.
5. Upgrade Assistant next replays the baseline trace, by using the baseline workload to capture relevant execution data from the legacy application on the test server.
6. You can now reset the test databases and upgrade the test server by using SQL Server 2008 Setup.
7. Replay the test trace workload on the test server that runs SQL Server 2008 and capture the relevant SQL Server 2008 execution data.
8. Compare the results of the baseline test workload on SQL Server 2008 against the original SQL Server 2000 or SQL Server 2005 results (also on the test server). If there are any material differences between the two results, work to resolve these in advance of the production upgrade.

Whether you perform a side-by-side upgrade on the same server or separate servers, consider the following points:

• A side-by-side upgrade requires manually transferring data and components to the instance of SQL Server 2008. After you install the parallel instance of SQL Server 2008, test the process of manually transferring components to the new instance.
• To repeat the side-by-side upgrade tests, you might not have to restore the test server from a ghost image. You could uninstall SQL Server 2008 and remove the data files to set the server back to its original state and repeat the upgrade tests. Make sure that you test uninstalling SQL Server 2008, and verify that the legacy version of SQL Server 2000 or SQL Server 2005 is working correctly.
• In a side-by-side upgrade, the rollback will most likely be to the original instance of SQL Server 2000 or SQL Server 2005. Make sure that you test the rollback scenario.
•  

In a side-by-side upgrade on a single server, consider the following additional items:

• You could run the legacy SQL Server instance in parallel with the new instance of SQL Server 2008. If that is part of the upgrade plan, make sure in the test environment that running in parallel will not require too much of the server's resources.
• When the cutover to the new instance of SQL Server 2008 occurs and the instance is verified as ready for production, stop the legacy instance of SQL Server, leaving it dormant for a while as a potential rollback instance.
• After the upgrade has passed acceptance tests, uninstall the legacy instance without disturbing the new production instance.
• Decide whether the production system will be online during the installation of SQL Server 2008. If this is the case, test the effect that SQL Server 2008 Setup has on application performance.

In a side-by-side upgrade to a different server, consider the following additional item:

• If the upgrade plan includes removing the old server from the domain and renaming the new server with the legacy name and legacy IP address, test this step as well.

Now, perform final tests on the new SQL Server 2008 server. To rerun the tests and gain confidence in the plan and deployment, repeat the process by restoring the baseline target server.

Upgrade Known Issues:
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/6d43c321-8fe7-45a7-8406-8ec875e05ca9/

SQL Server 2000 to 2008 Upgrade Whitepaper:
http://www.google.co.in/url?sa=t&source=web&cd=2&ved=0CB4QFjAB&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F2%2F0%2FB%2F20B90384-F3FE-4331-AA12-FD58E6AB66C2%2FSQL%2520Server%25202000%2520to%25202008%2520Upgrade%2520White%2520Paper.docx&ei=z6ylTfS5F8SqrAezgN3lCQ&usg=AFQjCNHREhaRPPZE_tjdjNENilWNKPNpIw

SQL Server 2008 Upgrade Technical Reference Guide:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af75-9975aea5bea7&displaylang=en

Windows Service Account:
http://msdn.microsoft.com/en-us/library/ms143504.aspx
http://msdn.microsoft.com/en-us/library/ms143504(SQL.105).aspx
http://msdn.microsoft.com/en-us/library/ms191543.aspx

Ref : http://vivekharshranjan.blogspot.fr/2011/11/upgrading-sql-server-2000-to-sql-server.html