If you are using SQL Server Log Shipping then above error message number will be familiar.
One of the following error messages may be logged in the SQL Server error log:
Error message 14420
Error: 14420, Severity: 16, State: 1
The log shipping destination %s.%s is out of sync by %s minutes.
Error message 14421
Error: 14421, Severity: 16, State: 1
The log shipping destination %s.%s is out of sync by %s minutes.
Still there is a refinement in the error message (not solution) to understand :
Error: 14420, Severity: 16, State: 1
The log shipping primary database %s.%s has backup threshold of %d minutes and has not performed a backup log operation for %d minutes. Check agent log and logshipping monitor information.
Error: 14421, Severity: 16, State: 1
The log shipping secondary database %s.%s has restore threshold of %d minutes and is out of sync. No restore was performed for %d minutes. Restored latency is %d minutes. Check agent log and logshipping monitor information.
A brief note on major change in the Log Shipping between 2000 and 2005 versions is, within SQL 2000 version log shipping uses Sqlmaint.exe to back up and to restore databases. The usual BACKUP LOG statements are passed that creates a transaction log backup as part of a log shipping setup. Also this Sqlmaint.exe process connects to the monitor server and updates the log_shipping_primaries table with the last_backup_filename information. A change of process when you perform a COPY or RESTORE job on a secondary server, Sqlmaint.exe connects to the monitor server and updates the log_shipping_secondaries table.
More often I see within forums & newsgroups users asking for solution when it occurs in their environment, thinking it is a problem in log shipping. Actually it is not, as a part of log shipping, alert messages 14220 and 14221 are generated to track backup and restoration activity, so you will need to set the backup-alert & out-of-sync thresholds depending upon the backup interval between primary and secondary servers.
The message 14220 refers the difference between current time and time that indicates the last_backup_filename value stored on LOG_SHIPPING_PRIMARIES table within the Log Shipping Monitor server, greater than value that is set for the Backup Alert threshold. Similarly message 14221 indicates the time difference between the last_backup_filename in LOG_SHIPPING_PRIMARIES table and last_loaded_filename in LOG_SHIPPING_SECONDARIES table, which will be greater than the value set for the Out of Sync Alert threshold.
Microsoft documentation & BOL indicates that both of these messages does not necessarily indicate a problem with log shipping, but still if it occurs then you must check the schedule of log backup & restore. As the message indicates that the difference between the last backed up file and current time on the monitor server is greater than the time that is set for the Backup Alert threshold. Within my experience I see this occurs due to the time difference between Primary & Secondary server including the Log Shipping Monitor server, if you have setup on seperate instance. Also if there is any issue within the Log Shipping MONITOR server such as you restart it during any hotfix or patching of operating system, then the fields in the log_shipping_primaries table are not updated with the current values before the alert message job runs. Recently within a DR based SQL Server I have seen that one of the transaction log backup job has failed due to the some non-logged operations and DBA executing BACKUP LOG ... WITH NO_LOG within the scripts causing the
backup job on the primary server is failing and to resolve this I have checked the job history for the backup job to see a reason for the failure.
Similarly the message number 14421 does not necessarily indicate a problem with Log Shipping, as it refers that difference between the last backed up file and last restored file is greater than the time selected for the Out of Sync Alert threshold. Ideally, you must set this value to at least three times the frequency of the slower of the Copy and Restore jobs. If the frequency of the Copy or Restore jobs is modified after log shipping is set up and functional, you must modify the value of the Out of Sync Alert threshold accordingly. 8 out of 10 times I see this message is generated due to the problems within the BACKUP job and mostly during the COPY job from Primary & Secondary servers, causing to result in "out of sync" alert messages. Say if you have checked the job of backup & copy of file between servers has not issues then it is nothing bu network connectivity during copy job to fail.
Its the last day of SQLPASS Summit 2008 and in few hours time I will be presenting my talk (first time in US SQLPASS) on "Effective Usage of SQL Server 2005 Database Mirroring, what's new in SQL 2008" topic which is an enhanced version of what I have presented before in SQLBIts UK during September 2007.
Just a highlights on the session:
High Availability solution from SQL Server, since version 2005 your SQL Server database availability and high performance solution can be delivered using Database Mirroring. With fast failover and minimal data loss has traditionally involved higher hardware cost and greater software complexity? This session highlights effective usage of SQL Server 2005 Database mirroring, that can fail over quickly with no loss of committed data, does not require proprietary hardware, and is easy to set up and manage. So what's new within SQL Server 2008 version in this high availability ranch.
Also the best practices on the different modes of Database Mirroring, what it can offer and how effectively you can make use of these technologies within your SQL Server environment to keepup the High Availability.
Not only this there is still few more action-packed agenda within this Summit 2008 and if you happen to miss the event you can buy all the sessions on a DVD from SQLPASS website, check back their site.
You can view the show from this Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy link and also various videos sharing best practices and strategies on other technologies are available on this Library page.
(after a continuous chaseup of correcting the video broken-link here is the fresh one)
Format: asx
Duration: --:--
Are you visiting Tech-ed, North America?
Tech·Ed offers the premier technical education conference just for IT professionals. Microsoft and industry experts will share their expertise about how to architect, deploy, manage, and help secure a connected enterprise. With only a few weeks to go, there’s still time to register for Tech·Ed IT Professionals. Don’t miss out on all the great content and learning opportunities. Register now.
This year I'm leading the Panel Discussion with a topic on Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy.
Who should attend?
Architects, DBAs, Developers, IT Pros & Database Managers...
What is your take-away?
SQL Server 2005 and 2008 provide a plethora of HA technologies. Combining these technologies into a viable solution to support a global IT infrastructure is not as hard as it seems. Following straightforward best-practices and a solid understanding of the capabilities and trade-offs inherent in the technologies, putting together an HA strategy can be easier than you think. Come to this session to hear from SQL Server industry experts on high-availability and disaster-recovery – we’ll dispel myths and give you guidelines you can follow straight away to implement the RIGHT technology now.
When: Tuesday, June 10 3:00 PM - 4:00 PM
Speaker(s): Kevin Farlee, Allan Hirt, Satya Jayanty, Paul Randal, Kimberly Tripp
Choosing a better plan to reduce outage to your database application is a beginner to provide High Availability to your application, irrespective to database or server. You may be aware that SQL Server 2005 version provides the database mirroring which is an advantage to avoid the costs and pre-requisite for Clustering, as it comes when you think about High Availability.
So when you setup the Database Mirroring the partners within this session will be Principal, Mirror and witness, just to talk about witness instance requirement that it should be used only when you intend to use high-safety mode with automatic failover. In high-performance mode, for which a witness is never required, why and all subject I will cover within another blog post J.
For the subject purpose assume only HIGH SAFETY mode is used where you have an option of choosing Automatic failover mode within the database mirroring sessions having witness aside for quorum purpose. As it is one of the best implementation I choose high-safety mode with automatic failover as database is synchronized, if the principal database becomes unavailable, an automatic failover occurs. During this action the mirror server to take over the role of principal server and bring its copy of the database online as the principal database, like-to-like with 'no (virtually)' data-loss. I believe this is one of the best practice to provide the database be synchronized prevents data loss during failover, because every transaction committed on the principal database is also committed on the mirror database.
So think about how the automatic failver should take into affect, as a part of role switch, the amount of time that database mirroring will be out of service depends on the type of role switching and the cause of the role switch. The technical documentation for SQL Server refers that:
- For automatic failover, two factors contribute to the time service is interrupted: the time required for the mirror server to recognize that the principal server instance has failed, that is error detection, plus the time required to fail over the database, that is failover time.
- For a forced-service operation, though a failure has occurred, detecting and responding to the failure depends on human responsiveness. However, estimating the potential interruption of service is limited to estimating the time for the mirror server to switch roles after the forced service command is issued.
This is where the communication between Principal and Mirror with a Witness instance plays important role, which is a default timeout value of 10 secionds. That means if any of the partners are not ping-able for 10 seconds it can throw a timeout message and the database can failover. At the end of the day you should consider the real-problem wide of failover and not with any other errors from the operating system level that can cause failures such as, Network errors or I/O errors or Process (memory/drivers) errors, etc. So take advantage of causing such an auto-failover of default timeout value, you can make changes by configuring the partner timeout value as shown in the following T-SQL statement:
Use Master;
ALTER DATABASE [Database_Name] Set Partner TIMEOUT [Number_of_Seconds] ;
So here comes the big question what would be optimum value to set, the answer is it depends on your application method to control the committed transactions, where keeping the minimum set of transactions to commit for better availability & performance. Within a scenario of highly-transactional application such as stock exchange trading application, I setup with 25 seconds as the instance (witness) must receive a ping on that connection within the time-out period defined by the mirroring time-out value, plus the time required to send one more ping. Receiving a ping during the time-out period indicates that the connection is still open and that the server instances are communicating over it. On receiving a ping, a server instance resets its time-out counter on that connection.
Just to cover up the difference between running asynchronous & synchronous sessions, where the asynchronous will have default time-out value of 10 seconds that cannot be changed and within synchronous sessions, you can control the time-out period as mentioned above. But always make sure you have tested the failover methods and transaction checking within your environment before taking my word, another best practice.
When you think about high availability in SQL Server then you would think about Clustering and in addition to this within 2005 version we have Database Mirroring too that can provide automated failover support to your database server applications. By default you can see a failover cluster as a combination of one or more nodes (servers) with two or more shared disks, known as a resource group. The combination of a resource group, along with its network name, and an internet protocol (IP) address that makes up the clustered application or server, is referred to as a failover cluster or a failover cluster instance. To go further you may have Active/Active and Active/Passive configurations, that can react differently to each other as compared to failover clustering as the number of nodes supported for failover clustering depends on the operating system.
Its a general assumption that using Failover Clustering you can obtain the performance to the application, it isn't quite right. As both of these features are different to each other and dependant from application to application, that varies in hardware aspect too. To keepup the performance you have to deploy the load balancing approach, as time goes by the data within the database grows. Based on this you can expect to see some degradation in performance, particularly when table scans are involved. When you get into the millions or billions of rows, the traditional solution has been to use partitioned views, which are made up of tables with identical schemas hooked together with UNION ALL's. So the general rule to have the member tables are on their own filegroups, you may get better disk performance if the files in those filegroups are on separate physical drives. The tables can even be in separate databases and with 2005 feature of partitioning as long as all of the data is in the same database, you can use table partitioning, which is far easier to implement.
Further I might have seen the recommendation of using such database partitioning within SQL Clustered setup stating the major difference is that the member tables can reside on different instances of SQL Server and those instances can be installed on an N+1 cluster. So what is the big idea in having in such setup, say if any one member table goes offline in a partitioned view, the entire view goes offline. So not all of the data is offline and only for those members part of a cluster then gives you the reliability you need to support performance and provide load balancing. Another big question do you really want to deploy cluster when you have other high availability features are availble from SQL Server 2005 onwards, such as Database Mirroring (automated) and Log Shipping (manual failover tasks).
So going this way for the concept of DB Mirroing will have mirror as a separate instance of SQL Server, unlike in a cluster, and can be located thousands of miles away. Its caches get populated by the update activity that occurs as a result of the transactions duplicated from the principal. Within my setup of database mirroring I have seen that failover is generally quicker than in a cluster since SQL Server is already running on the mirror. Because the caches are at least partially primed, the initial performance is not as sluggish as it might be in the clustered scenario. And note that when a mirrored database fails over, the role of principal and mirror are reversed. The only task you need to be aware within DB mirroing that you need to deploy the equal amount of storage between Principal and Mirror server as the hardware is not shared between these instances, that is applicable to CPU too in case if you go for synchronous mode with no data loss. This varies in Clustering because multiple instances can run on a single node, they would be sharing CPU and RAM-and that could spell poor performance. Ideally, only a single instance should run on a single node.
Here comes the big question on licensing thought, when doing failover support, a server is designated as the passive server. The purpose of the passive server is to absorb the data and information held in another server that fails. A passive server does not need a license if the number of processors in the passive server is equal to or less than the number of processors in the active server. The passive server can take the duties of the active server for 30 days. Afterwards, it must be licensed accordingly.
Database mirroring and failover clustering are available for SQL Server 2005 Standard Edition and SQL Server 2005 Enterprise Edition. Backup log shipping is available in SQL Server 2005 Workgroup Edition, SQL Server 2005 Standard Edition, and SQL Server 2005 Enterprise Edition.
Both Database Mirroring and Clustering passive/mirrored nodes will need to have licenses applied if you are using for Reporting purpose and see the Licensing-FAQ in this regard to avoid any confusion. Also you can download the SQL Server 2005 Pricing and Licensing document for further information on SQL Server 2005 installations.
For the performance & scalability aspects within the Clustered/Mirrored environment follow these links:
SQL Server 2005 and Disk drive allocation unit size to 64K - any benefit or performance?
SQL Server Clustering - what is the difference between Active/Passive and Active/Active Clustering, which is good for better performance?
SQL Server 2005 Scalability and Performance - recommended limit on number of instances on a clustered environment?
Whats the best way to setup memory on a four-node cluster?
SQL Server 2005: Clustering FAQ is Active/Active a load balancing solution?
We had a need to setup Database Mirroring for a Disaster Recovery Project. We were at time running SQL 2005 Std SP1. i had to start the SQL Server Service with the startup parameter -T1400 to enable Mirroring.
For DB Mirriring to work the SQL Server Service must be started using a domain account (normally) so as to have access on both the Principal and the mirrored server.
This is where i had my first issue. The Sql Server Service Starts before the Network Service on this specific HP ML350 Box. So when i used Domain Account to start the service, it does not start, as it could not find the domain controller.I tried some tips i found on some forums about adding the service as dependency of other services. with no luck.
So i decided to use a local account. I Created a Local Account on the Principal and the Mirrored Server (Same User Name and Same password on both BOX)
Second Issue: SQL Server Agent Service doesnot start if i start the SQL Server Service with a Domain Account or any Local Account. And Mirroring doesnot work with LOCAL SYSTEM ACCOUNT. i had the following error in Event Log:
"SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role)."
To resolve the above:
I start by ensuring that this Local Account has Sysadmin role on the server. I even delete and recreate the SQLAgent login.
SP2 does not solve the problem either.
Then i enable AgentXPs using the below lines
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
Which infact solves the issue.
I was then in front of a 3rd issue: I could not Edit or Execute any Maintenence Plans!!!!!!! I got an error message like Agent Xps is not enable.
I had to re-run the above lines to enable again the Agent XPs.
Then i change the owner of each Maintenence PLAN and specify the Local user to be the owner.
I then implemented Database Mirroring which works fine.
Below is how to Manual Failover from the Mirrored Server
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
Recently got stuck with the stored procedure sys.sp_dbmmonitorupdate which is used to monitor the database mirroring session. As usual you could do using the GUI tool, but sometimes using TSQL methods is good enough to see what is happening on your system.
On the system with SQL 2005 Service pack2 when executing this stored procedure getting the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Also you could try running, exec @retcode = sys.sp_dbmmonitorMSgetthelatestlsn @database_name, @end_of_log_lsn output
As referred the SQL Server agent executes this stored procedure whenever the Database Mirroring monitor job requires the update or requested by the user. To fix this a bug has been raised on Connect site with the ID: 546359. The bug says there is a problem when the database name has either a SPACE or a HYPHEN.
As far as a fix is concerned, there is none in sight.
Options -
1) Change the DB Name
2) Roll your own version of the mirror monitor job/procedure and skip all those databsaes with SPACE or HYPHEN.
The msdb.sys.sp_dbmmonitorupdate can be called with a DB Name parameter - so it can be run in a wrapper for specific databases.
Further information on monitoring a Database Mirroring session refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx link.
* SQLAgent service should run under same domain account, which has local admin privilege and “Log in as a service” permission. This requires on both the server. In case, if SQL Server is not part of any Domain. We can use the user account which has identical name and password at servers i.e. ReplUser on SRVA with password ReplUser! and identically ReplUser on SRVB with password ReplUser!
* User account that is used to run the SQLAgent services have to have sysadmin privilege on both server, and it should be added in to db_owner fixed db role for database which is being replicated/replicating. * Should include all the tables which are referenced, this will help to keep data consistency.
* If there are text/image column we need to update those column explicitly Updatetext statement. http://msdn2.microsoft.com/en-us/library/aa260658(SQL.80).aspx
* Avoid using Identity column as PK but if it is already implemented modify the property of Identity column as ‘Not for Replication’.
* Define both server (Publisher and Subscriber) as remote server at each other.
* Allocate adequate space for distribution, subscription and publication db.
* Rowguid column is must, which also can be added at the time of configuring Merge replication.
* sp_dropmergearticle can be used to drop article from publication.
* sp_addmergearticle can be used to add article to publication.
Below are some links that will be usefull for replication troubleshooting, they are:
Troubleshooting Merge Replication http://support.microsoft.com/kb/315521 and
http://msdn2.microsoft.com/en-us/library/aa237486(SQL.80).aspx
Managing Identity values in Replication environment http://msdn2.microsoft.com/en-us/library/aa237098(SQL.80).aspx
Violation of Primary Key (PK) Constraint http://support.microsoft.com/kb/813494
Merge agent incorrectly deletes rows after PK violation error http://support.microsoft.com/kb/326483
Adding and Droping an article in existing publication http://msdn2.microsoft.com/en-us/library/ms152493.aspx
Working with BLOB data in Replciation http://technet.microsoft.com/en-us/library/ms151206.aspx
When configuring log shipping on a highly transactional environment (with SQL Clustering setup), we were hit by above error. All the Logins and their privileges are intact within the primary and standby servers along with Local server policies & group policies.
Initial thought of the error brings up permissions issue while copying the file from Primary server to Secondary server, in this case ensure the SQL Server Agent account is using a domain based account with relevant 'ADMIN' or 'NECESSARY' provisions to copy the file. If you are using the shared directories between the servers then ensure this account has WRITE privilege and EVERYONE group has READ access. After all these checks I can confirm that SQL Server services account is running using a domain account which is why I'm stumped, suprisingly if I use my own account to initiate the file copy it works.
Being this is a Clustered setup I have checked that the Secondary serve remains in a different data center where the primary data center domain is differnt to each other, though both the domains have TRUST between each other and relevant service account has necessary privileges to perform FILE COPY activities. Eventually I found that during the log shipping when you register the SQL Server name you have to append the domain name to the secondary server name, such SecondaryServerName.DomanName!
This was the question asked by one of the user in the forums.
Precisely the answer is no, as you cannot restore a log or database from SQL 2005 to 2000 version due to the differences in the design. Moreover for the sake of high availability the method of performing Log shipping between 2005 and 2000 instance will not be valid, as in case if you need to restore the database if any issues on primary server then it is not allowed, as you can restore from 2000 to 2005 but not reverse-way. It doesn't server the purpose of providing high availability to your database, you may be asking this question just for information but if the destination in 2005 theres no point in configuring it with older technology, where you can take advantage from native methods.
Then coming to the differences between SQL 2000 and 2005, Steve Jones has documented the information here.
Within a Database Mirroring session within an automatic failover situation it may fail with the error :
"The server network address "%.*ls" can not be reached or does not exist. Check the network address name and reissue the command".
If you suspect DNS as a culprit for failure to connect, then perform a PING against that Server is a good start for troubleshooting. But there are some other quick tests you can try as well, such as "nslookup", which lets you resolve an address by name or IP. From a DOS prompt, type "nslookup (IP address)" and hit enter. You should first get IP and name of your own DNS server, then the IP and name of the IP you looked up. If you were to do this at FCNY, for instance. Refer to this SQL Server Connectivity article on troubleshooting this error.
Whereas in a database Mirroring session the LMHOSTS file cannot be used for the name resolution, in this case if DNS is an issue then use IP address instead. Name Resolution is only required for adding a Witness server to the Database mirroring session. When you use database mirroring in high-availability mode, the process of determining a failover is based on the network connection. If there is a problem with the network, mirroring will fail over or deny access to the database because of the quorum requirement.
Few FAQs on Database Mirroring: In the case of server having multiple network adapters in the server and if you need to dedicate to mirroring, associate a specific IP address to that adapter. There are no specific restrictions on the network for mirroring, but the network connection between the servers is critical. The network should generally be dedicated, and be of high quality and high bandwidth. As a rough guideline, the network bandwidth should be three times the maximum log generation rate.
Lately we have been getting complaints that database mirroring setup on few of our client's site were failing with TCP related errors. For more information these sites are involved within the firewall setup and active directory too.
Initial workaround is to add a Server name & port record in both networks DNS servers or add both SQL servers IP addresses in local hosts files (e.g. C:\Windows\System32\Drivers\Etc\hosts). I would like to refer the MSDN article for a detailed information on "How to: Configure a Firewall for SQL Server Access http://msdn2.microsoft.com/en-us/library/ms175043.aspx" link. Even then we have had issues in getting port numbers and last resort for me to check whether relevant TCP port numbers for endpoints are enabled in the firewall setup and I was under impression that by querying sys.database_mirroring_endpoints catalog, but it isn't the case. I found that they are in sys.tcp_endpoints catalog view and further a query can be written to join sys.database_mirroring_endpoints and sys.tcp_endpoints to get the important metadata information regarding the endpoints:
SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
e.connection_auth_desc
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON e.endpoint_id = t.endpoint_id
The above error is quite common within the database mirroring setup, you could get to see.
The intial workaround for this error will be to check whether any network issues are causing this error when the failover is intiated from a Primary server to the Mirror server. By default for the Database Mirroring servers you have setup the endpoints to listen on the different ports, which is correct. Also test the connection with a simple TELNET connection test from the command prompt.
I have seen few installation where they are still with Service Pack 1 for SQL Server 2005 or RTM where you have to setup with Trace Flag 1400 in order to initiate the database mirroring session. Further it is always best to check the privileges for the SQL Server services account and ensure they have SYSADMIN privileges. From the error message text it refers "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist", so you have to check by testing that instance and ensure to listen on the 5022 port, take a trip to SQL Configuration Manager for all the 3 instances involved in the database mirroring. In this regard I strongly recommend the users to refer through the "Troubleshooting Database Mirroring Setup" topic in BOL that is specially talks about versions of operating systems you are dealing with.
Say if you have had this error during the failove session then ensure to initialize the mirror by performing a full backup of the database on the princpal and restore the same full backup to the mirror with NO_RECOVERY. For the latest match of LSN on the transaction you must also restore any other log backups that are taken on the principal to the mirror.
Whether you're still in the planning stages, or you're trying to refine your organization's disaster recovery processes, this recently updated FAQ guide has information that can help.
DR-Q&A