High Availability (SSQA.net)

Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy - Tech-ed Online Panel video available to view

2008-07-22T11:31:00Z

In continuation to Tech-ed North America - what I'm doing there? & Say goodbye to Tech-ed 2008 Developers and say hello to IT-PRO! posts here and to wrap up the earlier posts about Tech-ed Online Panel: Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy the panel discussion video is available to view for the users. 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...(read more)

Tech-ed Online Panel: Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy

2008-05-29T09:11:00Z

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...(read more)

SQL Server 2005 Database Mirroring choosing optimum value for auto-failover timeout?

2008-05-12T09:25:00Z

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...(read more)

SQL Server - Deploying Database Mirroring and Clustering parallely on same machine, performance and failover support?

2008-05-08T08:42:00Z

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...(read more)

Setting up Database Mirroring using Local Account

2008-03-10T14:43:00Z

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...(read more)

Database Mirroring sys.sp_dbmmonitorupdate stored procedure fails with error "Incorrect syntax near '-'"

2007-11-12T09:23:00Z

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...(read more)

Merge Replication - Some Tips and Consideration

2007-10-26T09:33:00Z

* 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...(read more)

Log Shipping error "Unable to copy the initialization file"

2007-10-03T11:49:00Z

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'...(read more)

Can we do logshipping from sql 2005 to 2000? How it is different between 2 SQL versions?

2007-09-27T12:17:00Z

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...(read more)

Database Mirroring: naming resolution issues during failover connectivity

2007-09-24T15:13:00Z

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...(read more)

SQL Server Database Mirroring error with TCP port numbers, firewall involvement

2007-09-13T15:35:00Z

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

Database Mirroring: Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

2007-09-12T14:56:00Z

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.

Disaster Recovery - planning FAQ

2007-09-04T16:45:00Z

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

Data Protection and Disaster Recovery Tips

2007-08-28T10:54:00Z

How to protect and secure data in the event of a disaster?

It's not possible to anticipate every possible disaster your organization may encounter, but you don't have to. Discover a wealth of information about how to protect and secure your data in the event of a disaster. You may not be able to predict the exact details of a disaster, but you can be prepared with a solid response for when one strikes. Disaster can strike anywhere - not just where severe weather can hit - so make sure you're ready when it does.

Download this eBook today!

Renaming SQL Server that hosts Reporting Server

2007-08-23T06:28:00Z

Sometimes business demands or change in naming convention for servers lead to situation where one should change / rename the Server running MS SQL Server, it would not that panic renaming SQL Server as you have sp_dropserver and sp_addserver to help you, but what if this SQL Server box have reporting server too; well microsoft has workaround for it; refer - http://technet.microsoft.com/en-us/library/ms345235.aspx