It’s been quite some time that I blog, Sorry about that I was busy with my movement from USA to India and changed my job  part of life ,okay  back to India (Pune).

Lastly I wrote a blog on High Availability techniques here and How to setup simple Mirroring Here.  Continue on that following are some of the common difference of log shipping and Mirroring, actually both uses log and move the transaction “Mirroring used to call Real time Log shipping”. but the way it works makes it difference.

Hope this will help someone.

Thanx.

Vinay

As discussed about Disaster Recovery and High Availbility topic here continue on this. today would like to discuss some more information about mirroring.

As stated in my ealier post about mirroring, today I would like to write about how to conifigure mirroring, mirroring is very simple to configure. before going for it we should deside what time of mirroring we want to go with

High Availability

High Protection

High Performance

depending upon that we have to configure Witness.

I would say there are only two steps for it.

1. Create End points

2. alter database “” set PARTNER

this is the simple procedure I want to discuss there are other several ways to setup the mirroring.

1. need to decide what is your “priceple” database server , “Mirror” database server and “Witness” database server. this can be seperate box or difference named instance on the same server, but should be on differenct instance as on one  sql server you can create multiple “endpoints” which is the way Mirroring works(interacts)

the simple command for creating endpoints could be

CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT =5022)
FOR DATABASE_MIRRORING (ROLE = ALL )
if you are on same server(box) with different instances then we have to give different port number(eg. 5023, 5024)

* Principle and Mirrored database should be having same edition of sql server. witness can be express edition, and should be of “FULL RECOVERY MODEL”

Backup the Principle and restore it on Mirrored database with NORecovery
once you create the “Endpoints” then we have to alter the database PARTNER to listen to that endpoint port like.

at principle

alter database MirrorDB  set Partner=’TCP://MirroredServer:5022′

at mirror server

alter database MirrorDB  set Partner=’TCP://PricipleSrv:5024′

we can check the property of the principle database of option “Mirror” you will see the end point specified and other mirroring related info.

you are done. this is quick and short way of configuring mirror, once priciple is down it will rollover to mirror database server and Mirror server will become priciple and when Priciple is up that will become “mirror” server ie. reverse the setup.

*You cannot mirror the master, msdb, temp, or model system databases.

You can add on the securites and other configuration on it.

This is what I learned today.

Thanx.

Vinay

http://rdbmsexperts.com/Blogs/

http://vinay-thakur.spaces.live.com/blog

twitter\thakurvinay

High Availability and Disaster recovery Techniques in sql server

This blog is the introduction and brief overview for high availability and Disaster recovery techniques available in sql server, and its my understanding on it. I will write detail blog on each technique will later

There are different ways of achieving this is sql server each has its own advantage and disadvantages. following methods are used for HA and DR :

1. Clustering
2. Database Mirroring.
3. Log Shipping
4. Replication
5. Backup and restore
6. Detach and Attach

1. Clustering:-
   1. Cluster is a very high level technique to achieve HA.
   2. Cluster may not be good for DR plan as here both host should be close to each other
   3. Clustering require few seconds to move to other node if one node failed for high availability.
   4. Earlier to SS2005, this can only be possible at “Enterprise Edition” which itself cost expensive.
   5. At SS2005 2 node clustering is possible at standard edition as well.
   6. Different types of cluster are available as one node cluster (active/passive), two node cluster(active/active). Multi node clustering.
   7. Can be configured very easily below the steps to setup clustering.
   8. Clustering requires both nodes to be of same configuration.
   9. Active/active clustering can also be used for load balancing.

Reference:

http://msdn.microsoft.com/en-us/library/ms179530.aspx

http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&displaylang=en

http://support.microsoft.com/kb/254321

http://www.mssqltips.com/tip.asp?tip=1541

http://technet.microsoft.com/en-us/library/cc917693.aspx

http://technet.microsoft.com/en-us/magazine/2007.03.sqlclusters.aspx

1. 2. Database Mirroring:-
   1. Database mirroring is introduced in SS2005 sp1.
   2. This is much cost efficient technique unlike clustering this can be as HA. As here mirror server does not required to be of same configuration.
   3. Here Principle / Witness/Mirror servers, Principle and witness could be on same server.
   4. The setup for Mirroring is here
   5. Mirroring is configured with three ways:

Reference:

http://blogs.msdn.com/suhde/archive/2009/07/13/step-by-step-guide-to-configure-database-mirroring-between-sql-server-instances-in-a-workgroup.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-Performance-boost-for-Database-Mirroring.aspx

http://weblogs.sqlteam.com/tarad/archive/2007/02/13/60091.aspx

http://community.winsupersite.com/blogs/itprotips/archive/2008/02/28/sql-server-2008-database-mirroring.aspx

http://msdn.microsoft.com/en-us/library/ms188712.aspx

http://www.mssqltips.com/tip.asp?tip=1280

http://msdn.microsoft.com/en-us/library/ms177412.aspx

http://technet.microsoft.com/en-us/library/cc917681.aspx

http://technet.microsoft.com/en-us/library/cc917680.aspx

3. Log Shipping:-

1. Log shipping is well known term for HA and DR plan and is available from SS2000 onwards. In fact we can setup the custom log shipping using backup and restore technique.
2. Log shipping is available at enterprise edition on Sql server 2000 and is available at Standard edition for SS2005 onwards.
3. Log shipping is very efficient and easy technique the setup.
4. To use this for HA may require little downtime… manual intervention to make secondary server as primary, move logins and other activity. And same is applied for DR plan.

Reference:

http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx 

http://support.microsoft.com/kb/314515 

http://www.mssqltips.com/tip.asp?tip=1158 

http://www.youtube.com/watch?v=2k003Dj7fHo 

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1309728,00.html

http://www.informit.com/articles/article.aspx?p=331402

http://technet.microsoft.com/en-us/library/cc917705.aspx

http://www.sql-articles.com/articles/high-availability/ten-steps-to-configure-log-shipping

4. Replication:-

1. a. Replication is generally used as a technique to load balance the production server and distinguish the high activity production with reporting server.
2. b. Replication can also be used as HA or DR technique but not especially for it. Can be achievable but like logshipping it also require manual intervention.

Reference:

http://vinay-thakur.spaces.live.com/?_c11_BlogPart_BlogPart=summary&_c=BlogPart&partqs=cat%3dReplication

http://blogs.msdn.com/repltalk/default.aspx

http://www.replicationanswers.com/

1. 5. Backup and restore:-
   1. a. This is a standard way for HA and DR.
   2. b. This requires DBA skill.
   3. c. Time consuming process.

Reference:

http://msdn.microsoft.com/en-us/library/ms177429.aspx

http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups

http://articles.techrepublic.com.com/5100-10878_11-1041267.html

1. 6. Detach and attach database:-
   1. a. This is not a technique for HA or DR but can be possible as now a days things are on SAN and high end storage devices which are redundant and can recovery the data. in such cases if we could recovery sql server data and log file(optional) we can attach the file can be used.
   2. b. Steps are here

Reference:

http://www.mssqlcity.com/Articles/Adm/attach_database.htm

http://support.microsoft.com/kb/224071

http://www.sqlservercentral.com/articles/Administration/spattachdb/169/