.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

implementing Database mirroring for VLDB

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

Hi Guys 

we are thinking to implement databases mirroring for SQL Server 2008 database, Size 550GB,  Average transaction/sec - 250 & Max Transactions/sec -350 (perform counters). This database is Audit database ....Mostly used for the inserts....every action in the application is audited in this database....i mean inserts every fraction of second.

I would like to know if synchronous database mirroring (witness )  is good choice to implement as DR for this database. As far as i know database mirroring is committing of the transaction in the mirror first  and then in the principal. For such a high transactional database how performance can be affected using mirroring on the primary database ???

Also I have a doubt if fail-over occurs to the mirror in this database ....Can it take too much time to get the mirror exposed because of the high transaction rate......Can you please give me some idea on it ...What would happen when fail over would occur in very transactional database......




View Complete Post

More Related Resource Links

Need suggestion on implementing database mirroring


Hi Guys 

We are thinking of implementing the databases mirroring as hot stand by for all the databases in the same site on two powerful servers.

Right now we have two server : 1.  Svr1 with 7 small databases and 1 VLDB (550 GB --1 year data ) ---total 8 db's.

2. Svr2 with 1 100 GB( 1 year data --Crucial database) and 1 small db---total 2 Db's.


We want to migrate databases on these databases on these 2 old Servers to two new powerful servers and also we want to divide 10 databases  on the 2 new server  in such a way that every 1 database on new Svr1 will have a synchronous mirror on the Svr2 and every database on Svr2 will have synchronous mirror on Svr1. Database mirroring is going to synchronous on all the databases and with a separate witness server which will on cluster to prevent the single point of failure for the automatic failure....

What do you guys think of it any suggestions ........

how database maintenance jobs works in mirroring.....if suppose rebuilding an index online takes 20 min....how it will be effected in mirroring ....???

how n/w is going to effect ...how should we configure the n/w in same site ....what do you say if w

TDE key rotation in the database mirroring situation.

I have set up database mirroring for TDE enabled database and they worked fine as far as I got all the key infrastructure identical on both Principal and mirrored SQL servers. Now it is coming to our yearly key rotation practice for compliance.  After I regenerate the DEK on principal server and also encrypt it with a newer certificate,  the mirroring is suspended and I am not able to resume it any more.  (Although I have added this newer certificate to the mirrored server too.) It is understandable because now the DEK is out of sync.  However, what are the correct steps to do the key rotation in the mirroring scenario? The bottom line is:  I DO NOT want to set up mirroring again since our backups are huge and take very long time to copy the backups from principal server to mirrored server. thanks for any input in advance.  

If one database goes down in database mirroring then other will come up but because there is no Virt

In database Mirroring can I have a virtual IP... Please tell fast.. Actually we were trying to install the sql server cluster but thats not possible bcoz it is in production of Domino Server.My confusion is "If one database goes down in database mirroring then other will come up but because there is no Virtual IP type of thing in this then how my application will connect to another without manual interruption of IP change..."     "SQLSERVER DBA" "INDIA"

SQL Server 2008 Database Restore(Database Mirroring)

HI,I tried to impliment Database mirrorin in SQL Server 2008.I have 3 Sqlserver 2008 Eneterprise Edition in 3 diiferent computers with same domain.Pricnicipal server =TCP://SPHDev.sphinet.com.sg:5022.Mirror=TCP://SPHPrimary.sphinet.com.sg:5022 and witness =TCP://wsc211.sphinet.com.sg:5022.But when i click start mirroring button i got an error TITLE: Database Properties------------------------------ An error occurred while starting mirroring. ------------------------------ADDITIONAL INFORMATION: Alter failed for Database 'SPH_MSCRM'.  (Microsoft.SqlServer.Smo) ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The server network address "TCP://SPHPRIMARY.sphinet.com.sg:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418).This is the error message i got from Error Log"2009-06-03 17:01:08.89 spid27s     Database mirroring connection error 2 'DNS lookup failed with error: '11001(No such host is known.)'.' for 'TCP://SPHPRIMARY.sphinet.com.sg:5022"I restored Principal server DB  by using RESTORE WITH NORECOVERY and now this  database in mirror server is now look like as DBName(Restoring....).How i can identify

I have to do it manually or is there any other way...?to transfer the users in database mirroring

One issue in database mirroring: In Primary server every week 2-3 users will additionally added.How will these users gone to Mirror Server database?I have to do it manually or is there any other way...? One more thing is that Primary server is llve  and 24 hours users connected, If I want to migrate users on other server then I have to make mirror server as primary server and then run SP_rev_login , it is automatic failover then  the newly created users will not be at the mirror server??then problem"SQLSERVER DBA" "INDIA"

In database Mirroring:

In database Mirroring: I have found that there is a "mirror Server" option in System DSN ,Sql Native Client configuration but one confusion is here that : Should I have to create the same DSN configuration on Mirror server without giving "mirror Server" name in this case??"SQLSERVER DBA" "INDIA"

Database Mirroring: How To Set Up Servers?

  Hi, I am currently reading into Database Mirroring... A few questions regarding the transaction log came up my mind when trying to find a setup/disaster recovery strategy:   Imagine this situation: I am already running a SQL Server database... Now I want to add synchronized database mirroring by adding a mirror database to the setup. Which prerequisites are required on the mirror database? Is it sufficient to use an empty database? Or do I need to restore a backup of the principal database first?   Same for disaster recovery: Imagine the primary database goes offline and the Witness designates the mirror database as the principal database. After re-installing Windows/SQL Server on the former principal machine, which prerequisites are required to let it become the mirror of the new principal database? Do I need to create a current backup of the new principal database? Or will an old backup of the old principal database do? Or will even an empty database on the new machine do the job?   Finally, this is a more general question I have, regarding the transaction log: The mirror database is fed from the principal database's transaction log... How can I make sure that all necessary transaction log records are still available in the transaction log? I couldn't find information on how records are deleted from the transaction log file... Does the transaction

database mirroring "log records from bulk-logged operations cannot be sent to mirror database"?


does this mean only when bulk recovery model or apply to full recovery model as well? if applys to full recovery model, how the mirror database be synchronized after bulk operation?

Setting up 2 node Active-Active SQL cluster as High availability and database mirroring at database

Hi guys
Please need your inputs and suggestions. I have always worked as junior DBA and have setup the HA but never planned for it. Please need some support here.

We have one main application: Application1 ...which is the sending data to all other application for billing, reporting, processing the orders and other stuff. This application is working right now on two Server.

Server1:  SQL Server 2005 Enterprise Edition  (Total 16 Gb RAM , SQL Server ( 4-12 GB AWE Enabled) and  2Processors (4 core each) and 836 GB of HD )

Database mirroring with multi instance fail over cluster


Hi Guys 

I have a Question about setting up database mirroring on database. This database is on an instance on Active-Active fail over cluster. I want to make this instance as primary Server in Database mirroring.

Will it possible to mirror this database to unclustered server ? What would happen if there is fail over and the primary instance moves to the other node What would the effect on database mirroring ? Any ideas....



I need complete tutorial about MSSQL 2008 Database mirroring for load balancing


I need complete tutorial about MSSQL 2008 Database mirroring for load balancing

i want to load balance my SQL workload

all database insert update and delete queries will be mirrored

all select queries will be load balanced

sql servers will run at windows server 2008r2 at different computers (VPS)

Browser based Pokemon MMORPG Pokemon Craft Developer Used asp.net 4.0 routing at it's Pokedex

Database Mirroring time out error


Dear All,

We have two server 'A'  as primary and 'B' as secondary.

 Two databases 'XXXXX' and 'YYYYY' on same servers

Mirroring is configured on both  databases, 7022 as Endpoint between primary and secondary servers.

In the primary server  log, some times i noticed  following error .can any one Please suggest on the below error.


04/03/2011 00:06:56,spid18s,Unknown,Database mirroring is active with database 'XXXXX' as the principal copy.This is an informational message only. No user action is required.
04/03/2011 00:06:26,spid19s,Unknown,Database mirroring is active with database 'YYYYY' as the principal copy.This is an informational message only. No user action is required.
04/03/2011 00:05:36,spid19s,Unknown,The mirroring connection to "TCP://<Server network adress>:7022" has timed out for database "YYYYY" after 10 seconds without a response.  Check the service and network connections.
04/03/2011 00:05:36,spid19s,Unknown,Error: 1479<c/> Severity: 16<c/> State: 1.
04/03/2011 00:04:36,spid31s,Unknown,Database mirroring connection error 2 'Connection attempt failed with error: '10060(failed to retrieve text for this error. Reason: 1510

Database mirroring trouble configuring 2 connections


I am trying to set up the following. 


I have configured the sql server side no problem.  i am having issue getting the application that uses the database to connect to the mirrored server in the event of  failure.

The application that utalises the database connects through the data sources (ODBC), System DSN, SQL driver.  I see no option in the configuration for the addition of a 2nd IP.  Many of the technical references i have read refer to the data source connection string, i cannot find this in the registry however.  Please help!

I am used to a 3rd party mirroring software where you connect to a logical IP address which then routes through to whichever database is running.

Database Mirroring and shrink log files


Hi everybody!

I have productive database on mirror. The mirror in asynchronous mode. On principal server the log file have size 95Gb. I want to shink log files. Can i shink this log files, or a get problems if i tray do whis?


Thanks a lot!


Multiple database support with Entity Framework

One of the features introduced in Entity Framework is being database independent. Which mean each database provider can support Entity Framework by implementing its provider.

This feature allows you build applications independent from the underplaying database provider. In this post I'm going to show how to build an application that support multiple databases using same conceptual model made by Entity Framework.

LINQ : Implementing IN and NOT IN

I got tried of typing

var result = from s in source
where items.Contains(s)
select s;and so I implemented the IN and NOT IN methods as extension methods:

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend