Skip to main content

SQL Server 2005 Database Mirroring - sponsored by PASS

October 27, 2004

Published: November 1, 2004

Please note:Portions of this transcript have been edited for clarity

Introduction

Stephen_MS (Moderator):
Welcome to today’s chat. Our topic today is SQL Server 2005 Database Mirroring. Questions, comments, and suggestions are welcome.

We are pleased to welcome our Experts for today’s chat. I will have them introduce themselves now.

Mark Wistrom (Expert):
Hi, I'm Mark Wistrom, the Program Manager for Database Mirroring.

MarkA [MVP] (Expert):
Hi, my name is Mark Allison, SQL Server MVP based in UK. I've been working with SQL Server since 1996, version 4.21 on OS/2.

VitalyA (Expert):
Hi, I'm Vitaly Akulov, the tester of Database Mirroring feature here at Microsoft

Start of Chat

Mark Wistrom (Expert):
Q:
How do we configure database mirroring on two servers? Do we need identical servers like same version for os and database lever?
A: Configuration of db mirroring is described in detail in Books Online Beta 2. it is a best practice to have the same OS and sql version on all of the servers involved in mirroring. OS really doesn't matter, but there may be some corner cases where things don't work as expected. keep things simple; have everything be the same.

Mark Wistrom (Expert):
Q:
Is bi-directional mirroring supported, if so how are conflicts resolved.
A: No, not for the same database. The mirror database is not accessible. You can mirror two DIFFERENT databases "bi-directionally"

Steve Lindell (Expert):
Q:
I want to mirror a database for reporting only. I don’t need the HA features. 1. Can I turnoff the need for the witness server? 2. Does the mirror database automatically become the primary in the event of a failure on the primary database? Can this automat
A: For reporting only run with safety off and no witness. Either one is enough to disable auto failover, but quorum (2 of 3) is required to run with safety on so with no witness both systems must be up to update the principal server. Safety off allows running with the mirror down and no witness. To use the mirror as a reporting server you can create database snapshots on an active mirror. This allows point in time read only access to the database.

Stephen_MS (Moderator):
Q:
I want to know, if Microsoft will look a little more for Brazil on sql 2005 news. Here we don’t have so many information about like new features, data types, xml extensions. Do you know some think about this?
A: I'm not a marketing or sales person, and I think that question would have to be addressed by them. My take on it is that we don't target specific countries from corporate headquarters and the folks you need to direct this question to would be the local Microsoft subsidiary. In the meantime, you could have a look around http://www.microsoft.com/sql/default.mspx to see what's available.

VitalyA (Expert):
Q:
Is it possible to mirror just one database or a part of then on the server without using replication or logshipping process
A: Yes, it is possible. Database Mirroring works on database level, not on server level. It is not required to use replication or backup logshipping process together with Database Mirroring.

Steve Lindell (Expert):
Q:
Are there plans to be able to mirror a single database to two secondary severs, one that could be opened in read mode (potentially) and one for recovery -- or both for recovery to be save?
A: Not in this release. Log shipping supports this currently.

Steve Lindell (Expert):
Q:
What is about mirroring through firewalls? Mirroring over HTTP or HTTPS?
A: To mirror though a firewall you will need to enable the mirroring ports on the firewall. The endpoint configuration allows you to limit the connection to list of allowed IP addresses which would be a good idea for this case.

Mark Wistrom (Expert):
Q:
How are you positioning db mirroring vs. clustering vs. transactional replication. Why would one choose to use one of these technologies vs. another? Could you compare and contrast each of them? Thanks.
A: Quickly, mirroring is at a database level, has fast failover and duplicates all hardware and storage. Clustering has shared storage, duplicates the entire instance, and has longer failover times. Consider replication for scaleout reporting.

MarkA [MVP] (Expert):
Q:
Can you please explain me how the client redirections works?
A: There are two options here, you can embed the partner server name in the connection string in your app, or you can allow the .NET provider to do the redirection for you automatically. If the principal goes offline, the client tries to connect to it again, if it fails, it attempts a connection to the mirror. If that is successful, the mirror server becomes the principal and is brought online.

VitalyA (Expert):
Q:
Can the mirror target database then be logshipped to a tertiary server? And if not, where do database snapshots fit in for making the secondary accessible for a short time (running a report, say)?
A: Database on mirror server is not accessible because it is in "recovering" state, so it is not possible to logship it. However you can create a database snapshot on top of it and use it for as long as you need, let say for reporting purposes. You are able to logship from the principal (live) database if you desire multiple reporting servers.

Mark Wistrom (Expert):
Q:
As mirroring works at the database level what as the suggested solutions for keeping logins, etc in step?
A: ah, yes, data outside of the database is not kept in sync between the two servers automatically. you will have to use the same techniques that you use today for log shipping.

Michael Zwilling (Expert):
Q:
Now on EMC products can use NAS without fiber channel? using 2005.
A: It's important to understand the difference between database mirroring and traditional disk mirroring

Steve Lindell (Expert):
Q:
Let's say you have several app servers and a mirrored database. Your server centre is wiped out and you fail over to you DB mirror. Is there a parallel technology to fail over the app servers?
A: The app servers can use the network redirect code to automatically connect to the active server, use a connection string with both servers or build in logic to virtualize the server name and do the failover in the app server code.

Mark Wistrom (Expert):
Q:
We would like to field 400 SQL Express 2005 database, one to each of our field locations. These locations are connected by a 128k link. Can these SQL Express Databases be mirrored back to a single server in HQ for reporting purposes?
A: No, SQL Express cannot be used as one of the partner servers. You cannot mirror a SQL Express database. A 128k link? Depends on the load and how much log is being written. Consider replication for reporting.

MarkA [MVP] (Expert):
Q:
Would database mirroring be a better alternative than hardware mirroring? Pros and cons of using hw vs db mirroring.
A: It depends! :-) The only hardware mirroring technology I'm aware of is using SAN technology that mirrors the servers at the disk block level. Database mirroring is not dependent on any special hardware, and will be faster to fail over, typically 3-5 seconds. It is also much cheaper. The fast failover is not possible with hardware mirroring at the disk block level.

Stephen_MS (Moderator):
Q:
Can I have more than one virtual server enabled? and if yes, all the virtual servers can access the same storage?
A: That sounds like a clustering question and we don't have any clustering experts here. However, I can tell you that our implementation of clustering is shared-nothing. In other words, only one node can own the storage and have access to it.

Steve Lindell (Expert):
Q:
What kind of performance difference (I/O, memory and CPU) have you seen between mirroring and, say, transactional replication?
A: Mirroring tends to be lower CPU overhead as it is plugged in like a log backup instead of a more logical level like replication. If everything is caught up both can read the log from the cache, but mirroring allows for a slightly larger cache before going to disk.

Michael Zwilling (Expert):
Database mirroring is independent from the underlying storage system. It does not rely on any disk mirroring technology.

Stephen_MS (Moderator):
Q:
DB mirroring needs identical Server Config. ?
A: It's a best practice, but it's not required.

Mark Wistrom (Expert):
Q:
The down time between the primary server down and the secondary server up was decreased? On fail over environment?
A: ?? Is this a question? The failover time for mirroring should be less than 3 seconds for most scenarios.

Stephen_MS (Moderator):
Q:
Not sure if this has been asked, but will Mirroring be available in SQL Server Standard Edition?
A: That decision hasn't been made yet.

Mark Wistrom (Expert):
Q:
Since mirroring is done at the database level, how do you keep user ID's in synch with logins?
A: you would have to do the same thing you for log shipping. There is nothing that we provide to keep the metadata outside the database in sync between the two servers.

VitalyA (Expert):
Q:
How does DB mirroring differ (excluding HA features) from doing a two-phased commit? Does the mirror db have to acknowledge a completed transaction before the client can proceed?
A: In HA mode when primary issues "COMMIT TRANSACTION" it is guaranteed that mirror has information written to the log at the end of this statement. Client does not need to do anything special, committing transaction will guarantee data is written both on principal and mirror.

Steve Lindell (Expert):
Q:
Follow-up to my app server failover question - I'm more interested in how to failover the clients. If the app servers are in the same data centre as the database server and the entire centre gets wiped out, how will, say, a browser-based client failover?
A: If the app servers go down then it is a straight app/client issue and mirroring does not affect the situation.

Mark Wistrom (Expert):
Q:
Wow, 3-5 seconds for a failover seems like an awfully short/aggressive amount of time. Can you elaborate on the testing you've done (i.e. distances, transaction amount) to arrive at this estimate please?
A: We are basically running REDO constantly on the mirror server. ONCE WE DECIDE TO FAILOVER, all the mirror server does is switch to UNDO mode and open up the database. This takes advantage of the fast recovery work done in SQL Server 2005. We are targeting special scenarios for 3 second failover for release criteria. Testing on SQL Server 2005 is in progress.

Michael Zwilling (Expert):
Q:
Do you have an idea of what type of transaction volume db mirroring will support while in synchronous mode without suffering from performance degradation?
A: Synchronous mode primarily affects transaction response time (by adding a delay to transaction commit) but assuming you have sufficient bandwidth, it does not affect throughput. if you want to get an idea of the bandwidth requirements, look at the size of your transaction log backups. In mirroring, transaction log blocks are continually sent to the secondary. We are shooting for a goal of not more than 5-10% degradation of latency and throughput on a TPCC load running at 50% workload. We will publish actual numbers closer to the release of SQL Server 2005.

Mark Wistrom (Expert):
Q:
As mirroring works at the database level what as the suggested solutions for keeping logins, etc in step - Are there any plans to try and help out in this area?
A: No plans yet. We are looking at this problem, but likely you will have to do the same thing as log shipping.

Steve Lindell (Expert):
Q:
How fast is the recovery time from the primary server to the secondary server during a failover and what kind of events would cause the database to failover to the mirrored database?
A: The mirror should come online within a few seconds as we are constantly doing rollforward recovery with the log being shipped. The database will failover when the partner is unresponsive (timeout) or the connection is broken.

Stephen_MS (Moderator):
Q:
With mirroring failover, (potential downtime being 3 seconds) will there be a break in client connections as in clustering failover?
A: Just as in clustering, the client has to reconnect and if they have the new stack, they can be automatically connected.

Stephen_MS (Moderator):
Q:
Can I specify which CPU should be use for mirroring?
A: CPU as in which processor in the machine? No.

MarkA [MVP] (Expert):
Q:
If I configure mirroring in safe mode, what happens when the link between the two database servers goes down. When I get it back, my secondary is out of whack -- how do I get it back -- is there a reintegration feature or do I have to rebuild it from scratch?
A: It depends whether you have set up a witness server or not. If you have a witness and you lose connectivity only between the principal and mirror, and the witness does not lose connectivity between the principal and mirror (a rare event), then the principal stays online and the mirror waits until connectivity comes back, then resynchronizes automatically. If the mirror goes offline and cannot contact the witness then the principal is set to DISCONNECTED (if it can still see the witness) and when the mirror comes back, it will attempt to resynchronize automatically. If the principal goes offline and cannot see the witness, then automatic failover will occur if the mirror can see the witness. When the principal comes back online, it takes the role of mirror and begins resynchronising. If the mirror AND witness go offline, then the principal takes its database offline.

Steve Lindell (Expert):
Q:
On high level of transactions environment with a low link like 64k, can I schedule the time synchronization ?
A: The synchronization takes place when the log is written and the servers are connected. If you want to only synchronize at certain times of day log shipping allows you full control of backup and restore times.

Mark Wistrom (Expert):
Q:
Could you provide guidance on the prescribed physical location of the witness in a geographically separated dual datacenter environment. I'm presuming the principal & secondary servers would each be in one of the two data centers.
A: Well, it depends on what you are trying to solve. Normally, the witness would be in a different location. Mirroring is designed to survive the loss of one server and keep going. If you put two of the three servers in the same location, then if that location is destroyed/offine, mirroring cannot failover automatically.

Michael Zwilling (Expert):
Q:
We are using NSI double take to mirror our data and sql server database files. Would it be a better choice to use db mirroring. The double take solution has seemed to work find with sql server 2000.
A: I'm not familiar with Double Take, but assuming it is software based disk mirroring technology, I'll try to answer this. Database mirroring has an advantage over disk mirroring in that it sends only deltas from transaction log and thus should require lower bandwidth than disk mirroring which must send the deltas for all database files. However, disk mirroring technology has the advantage that multiple databases on the same mirrored volume are all kept in complete synchronization. Another advantage of Database Mirroring over disk mirroring is the fast failover time provided by database mirroring.

VitalyA (Expert):
Q:
Wow, 3-5 seconds for a failover seems like an awfully short/aggressive amount of time. Can you elaborate on the testing you've done (i.e. distances, transaction amount) to arrive at this estimate please?
A: We are doing our best to ensure this goal will be met in final release of SQL Server 2005. Different user scenarios are being used to generate workload (transaction amount). In addition to that we test that it works under different network conditions, i.e. fast/slow connections, high/low latency, etc. One of example of workload we are using is TPCC benchmark.

Steve Lindell (Expert):
Q:
when connection is lost to the mirror database and a transaction is committed does the issuing process have to wait for the actual mirror write or just the mirror log entry?
A: If you are running with the mirror down then you only wait for the local IO. The mirror will get the log when it reconnects.

Stephen_MS (Moderator):
Q:
Re client connection. You said that you can let the client just connect to the server and it works out where the primary is...how does that work?
A: You can specify the mirror in the connection string and also when you connect using the new MDAC stack, it will persist the mirror information in memory. So if you're disconnected and attempt the reconnection, it will know which server to attempt the connection to.

Mark Wistrom (Expert):
Q:
If I have 2 server on mirroring and there was on transaction opened and the second mirror server down, the transaction will be committed, rollbacked or will go to a list waiting for response of second server up?
A: Question is: if I open a transaction on the principal and the mirror goes down, what happens to the transaction? The transaction will remain open and complete. There may be a small delay in response time while the server tries to figure out what is going on.

Steve Lindell (Expert):
Q:
(meant this as a submitted question...) When the mirror database is "recovered" (database snapshot), what happens to the tran logs on the primary? Do they keep building in size?
A: The database snapshot on the mirror only uses the log when it is being created so log truncation is allowed once it is up and running. The log truncation point with mirroring will depend on the oldest active transaction and log backup on the principal and the redo progress on the mirror.

Mark Wistrom (Expert):
Q:
What is the timeout period required for a failover to happen?
A: in beta 2, there is a set timeout of 10 seconds. if the server misses 4 pings (at 2.5 seconds each) then the connection is presumed down. It will be configurable in beta 3 via an ALTER DATABASE statement.

MarkA [MVP] (Expert):
Q:
Who/what determines when a failover to the mirror is needed? Client or third party observer?
A: The client never determines this. There are three operating modes: 1. Synchronous with a witness 2. Synchronous without a witness 3. Asynchronous without a witness. If you are talking about automatic failover, this only occurs in mode 1. The other modes can have manual failover, with 3. having the potential of data loss. So, the witness server determines an automatic failover only in synchronous mode.

Michael Zwilling (Expert):
Q:
Is there any performance evaluation w.r.t. transactions when mirroring is in effect?
A: With safety-on (commit not returned until hardened on the mirror) there is response time impact on transactions, but assuming you have sufficient bandwidth, there should be only minor impact on throughput. With safety-off, even the response time impact should be very minor. We are shooting for a goal of not more than 5-10% degradation of latency and throughput on a TPCC load running at 50% workload with safety on. The degradation is in the 1-2% range with safety off. We will publish actual numbers closer to the release of SQL Server 2005.

VitalyA (Expert):
Q:
Will db mirroring require an upgrade/change to the MDAC client (or its equivalent) on the app/client connection. Could you elaborate please?
A: No, it is not required. You can use your existing MDAC client and it will work just fine against server with Database Mirroring. However new functionalities (client redirection for instance) will not available for you.

Steve Lindell (Expert):
Q:
Will you be able to "mirror" *all* databases on an instance, i.e. system databases - to help get past the issue of keeping logins in sync, jobs and job schedules updated, etc.?
A: No - the mirror needs access to the system databases to run so the instance cannot run if the system databases are acting as a mirror.

Michael Zwilling (Expert):
Q:
When you are running the redo on the secondary, I assume it is a log replay so the data caches are hot for query if the event of a failure?
A: It is a log reply and thus the data caches are hot at failover time for the updated data. Future work to make it hot for readonly data :)

MarkA [MVP] (Expert):
Q:
Why are you saying that the mirroring by soft was better than hard base mirroring?
A: Because software mirroring does not require any special hardware, it depends what you mean by better! ;-) What hardware mirroring solution are you thinking of?

Mark Wistrom (Expert):
Q:
With the high setting out there for mirroring, if my secondary is down, will my primary fail to commit changes as it needs to write to secondary as well?
A: You are talking about safety FULL? Depends on whether or not you have a witness and it is in contact with the principal. If the principal can contact the witness, then it will go on as if nothing has happened. If the principal cannot contact the witness, the principal takes the database offline.

Stephen_MS (Moderator):
Q:
re connection. Does it cache both primary and secondary. What happens when you reconnect to the 'cached' primary and it is no longer the primary?
A: Both are cached and they won't both be available. When you attempt that reconnection and the cached primary isn't the primary anymore, you'll be connected to the mirror that is now the primary.

Steve Lindell (Expert):
Q:
Steve Lindell said "To mirror though a firewall you will need to enable the mirroring ports on the firewall". As I see there are special ports for mirroring. Are these ports safe for intranet attacks?
A: The mirroring port will be as secure as we can make any port. The IP filtering is done at a high level in the connection code which limits the exposure.

MarkA [MVP] (Expert):
Q:
When the mirror goes off-line and the status of principal changes to DISCONNECTED, where are the transactions stored that still need to be sent to the mirror when it becomes available again? Do I need a huge transaction log to handle this?
A: Yes, you need to have enough log space to accommodate this. The transactions are held in the transaction log on the principal.

Mark Wistrom (Expert):
Q:
Can you guys give us some pros and cons of dbmirroring vs other tools out there like NSI DoubleTake or Xosofts wansync or Neverfail ? What does Data Protection server do ?
A: Too much information to discuss here. Keep in mind that sql sends the same information to the log as to the mirror server. Of course I would always recommend my feature. :D

Michael Zwilling (Expert):
Q:
Scenario: Client A and mirror are on one side of network fault site. Client B and principal are on the other side. Network fault occurs. What happens for transactional integrity for client A and client B transactions?
A: Transactional integrity is always maintained. There is a third server involved - the witness. The mirror, primary and witness vote on which server becomes the primary.

MarkA [MVP] (Expert):
Q:
Are there database configr parms you suggest for really fast failover times (like 3 seconds) -- do I need to do something to the logs or something like that?
A: No, you need a well-connected network for a fast failover. There is nothing special you need to do.

Steve Lindell (Expert):
Q:
Could you provide suggestion on the initiation of the mirroring server if mirroring needs to be configured? Does the mirroring site need to have an existing copy of the database?
A: The mirror needs to have the database restored "with norecovery" with enough log to overlap with the active log on the principal server. See the documentation for more discussion on this.

Michael Zwilling (Expert):
Q:
If I've an Oracle connected using linked server and one distributed transaction opened, and the link between the server goes down in the middle of process can sql manage the commit or rollback of this schema?
A: Yes, SQL Server fully supports atomic distributed transactions and this extends to mirroring.

Mark Wistrom (Expert):
Q:
Follow-up to physical location question please Mark Wistrom. So will the best practice be to have the three db mirroring resources in 3 separate data centers?
A: depends again on what you are trying to solve. if you want to automatically solve the problem of a datacenter disaster, then yes, put the three resources on different power grids, locations.

VitalyA (Expert):
Q:
Scenario: Client A and mirror are on one side of network fault site. Client B and principal are on the other side. Network fault occurs. What happens for transactional integrity for client A and client B transactions?
A: From what you described client A will loose connection to principal and last transaction will be lost. What happens with Client B depends from a) what is the safety level b) is there a witness present.

Steve Lindell (Expert):
Q:
what prevents direct connection to the mirrored database by a client application while it is in the process of mirroring, did you say that the database is in recovery mode? If so what tells it to leave recovery mode when the primary goes down?
A: The database is in restoring mode on the mirror. When the mirroring code decides to switch it moves the database out of restoring mode into normal operation which allows the client in.

Mark Wistrom (Expert):
Q:
Does DB mirroring also mirror anything outside but related to the DB like FullText Indexes?
A: We don't mirror the fulltext indexes, but it does work "seamlessly" after failover. The index is generated automatically after failover, just like a restore (new in 2k5).

VitalyA (Expert):
Q:
Scenario: Client A and mirror are on one side of network fault site. Client B and principal are on the other side. Network fault occurs. What happens for transactional integrity for client A and client B transactions?
A: If safety set to full and there is no witness then database will go to "disconnected" state, all connection to this database will be lost and last transaction will be aborted. If safety set to OFF then last transaction will be lost but all connections to database will be kept alive.

Michael Zwilling (Expert):
Q:
What are the limiting capacity factors when determining how many mirroring sessions a single witness can host?
A: The workload and network traffic for the witness is very, very light. A witness machine managing hundreds of mirror pairs is reasonable.

Steve Lindell (Expert):
Q:
What's the best/recommended way of knowing/alerting when an automatic failover occurs?
A: Mirroring fires events that can be monitored with sql trace... on all major state changes. For this you would want to look for the database going into principal mode.

Mark Wistrom (Expert):
Q:
What's the best/recommended way of knowing/alerting when an automatic failover occurs?
A: the mirroring code is plugged into the standard alerting/eventing infrastructure of 2k5. you can see the events in profiler.

Stephen_MS (Moderator):
Q:
On sql 2005 ver. can I use log shipping thought the standard version or just with enterprise like 2000?
A: That decision hasn't been made yet.

MarkA [MVP] (Expert):
Q:
When you have full safety, does the principal contact the witness before committing every transaction?
A: Yes, before committing a transaction, the principal sends the log information to the mirror, when the mirror enters the transaction it acknowledges this to the principal, then the principal commits the transaction. The witness just tracks configuration and role changes so is not involved in transaction commits.

VitalyA (Expert):
Q:
Can I have the witness on the same server as the principal?
A: It has to be different instance of SQL Server. You can install another instance of SQL Server 2005 on the same server as principal and let it act as witness. This configuration supported but not recommended.

Mark Wistrom (Expert):
Q:
Mark Wistrom - timeout period followup. You said that in Beta 3 the timeout will be configurable, does this occur at the witness or the primary database? Also, could an application override this default setting using the upgrade MDAC client?
A: The timeout is configurable from the principal with changes coming in Beta 3: ALTER DATABASE db SET PARTNER TIMEOUT x. The application would have to log into the server to issue the above command.

Michael Zwilling (Expert):
Q:
can I have the witness on the same server as the principal?
A: The purpose of the witness is to determine which of the two servers should be the primary thus it is important for it to have failure conditions independent of the principal and mirror. Putting it on the principal means the witness practically has no value - if the principal server goes now down the mirror will not be allowed to take over since the witness is down also and cannot vote to bring the mirror up.

Mark Wistrom (Expert):
Q:
Followup to the "secondary is down" question -- is that also based on a 4-ping (10-second) response time? Taking the primary offline is a pretty drastic step! <g>
A: don't understand the question. if you never want the principal to go offline if you have problems with the other servers, then turn the safety OFF.

Steve Lindell (Expert):
Q:
so - the principle can't talk to the witness, it takes the database offline? Then that means my application is broken because the db is offline?
A: The db goes offline if it loses quorum (2 of 3) so you would need to lose contact with the witness and the mirror. In this case it is possible that the other 2 systems are in contact with each other and would failover at that point so you do not want both sides claiming to be active.

Michael Zwilling (Expert):
Q:
If my recovery model is set to simple can I use mirror feature?
A: No. Database Mirroring is supported only with the full recovery model.

MarkA [MVP] (Expert):
Q:
About hard x soft mirroring. When I asked with is better the main point that I'm confused if is the cost or performance think? When you say that soft is the best solution?
A: As database mirroring is a software solution it will consume slightly more resources than a hardware one. The main benefit is cost, and ease of administration. I don't think you will notice and performance difference in most systems. This may not be true for high transaction throughput systems, testing can only show this.

Mark Wistrom (Expert):
Q:
Since the load for a witness is very light, will the witness machine require a license for SQL Server or will it depend on the licenses for the principal/mirror?
A: :) We haven't made the final decision for licensing yet. Thank you for the input.

Michael Zwilling (Expert):
Q:
When a reintegration occurs (if there is a witness), and the secondary goes to the live server to get the logs to reintegrate, if you are archiving the logs, can it go and get them?
A: Yes, the mirror can get them because even though you are archiving (backing up) the logs, the log will remain online (i.e. not truncated) until the mirror has had a chance to grab it.

MarkA [MVP] (Expert):
Q:
It seems to be that collocating a witness is a very bad idea -- so if I put this on a separate server, do I have to buy a SQL Server license for that?
A: Yes, you need a separate license for the witness. Bear in mind that the witness server can be engaged in other work too, so you can make full use of the server (and the license) with other DBs. However, there may be cases where the customer has no useful work for the witness. The licensing issues in this case are being worked out.

Mark Wistrom (Expert):
Q:
Not sure if I select Submit a ? -- again (sorry): It seems to be that collocating a witness is a very bad idea -- so if I put this on a separate server, do I have to buy a SQL Server license for that?
A: :) Licensing has not been determined yet. I understand your concern. Thank you for the input.

Steve Lindell (Expert):
Q:
This question concerns DTS packages and analysis services. If one server goes down, are the packages deployed to the mirrored server or was that accomplished in the initial deployment?
A: If the packages are stored in the database then they will failover otherwise they need to be moved manually.

VitalyA (Expert):
Q:
Will there be a facility (perfmon counter, etc) for monitoring the latency between the primary and mirrored databases?
A: There will be performance counter(s) that will tell you about the difference between primary and mirrored databases, i.e. how far behind mirror is. We are very open to the ideas of what people really want to see here.

Steve Lindell (Expert):
Q:
Do the SQL2005 client tools work around this MDAC redirection? For example if server "ABC" has failed over to "DEF", in SQL Workbench I still want to be able to connect to both explicitly.
A: The failover in the client only works if you specify the mirrored database in the connection string. To make sure you end up on the specified server connect to master or tempdb which cannot be mirrored.

Mark Wistrom (Expert):
Q:
"A: we haven’t made the final decision for licensing yet. Thank you for the input." - I'll expect this to be a big fat no please! :)
A: Not necessarily. :) We just don't want to make any promises that we can't keep. It has change 2 times in the past month!!!!!

VitalyA (Expert):
Q:
Which recovery model should be used for the db when enable mirroring?
A: full recovery.

MarkA [MVP] (Expert):
Q:
Mark_A - Can you clarify your Ans to the Qu about Full Safety having to talk to Witness for every TX? Your answer mentions Secondary, not the witness?
A: I thought I was pretty clear! :) When a transaction is entered on the principal, it is sent to the mirror. The mirror enters it into its transaction log, and sends an acknowledgement back to the principal. When the principal gets this message, the transaction is committed.

Michael Zwilling (Expert):
Q:
Will the witness software be part of just the client tools or the full RDBMS. If I have a low-end machine as a witness, I shouldn't have to install much.
A: A low end machine is sufficient. The witness is just part of the sqlserver process.

Steve Lindell (Expert):
Q:
Valmatison, you don't have to rely on mirroring for this, do you? You just need to keep a copy of your jobs and packages on the mirror server...and probably a script to enable the jobs after failover
A: This should be the case and hopefully the packages are not changing rapidly enough to make this an issue.

Mark Wistrom (Expert):
Q:
re connection. If you specify primary and secondary on connection then same dialogue takes place and you end up at the primary....always ;)
A: Yes, you always end up on the principal, because principal/mirror is a role for the server. You cannot ever to login to the mirror database.

Mark Wistrom (Expert):
Q:
Would Express be allowed to act as a witness -- it isn't the mirror capabilities per se (wrt to function) -- just monitoring which I assume are some sort of Tx'al alive requests and feedbacks?
A: Licensing has not been determined yet. :) Thank you for the input.

Stephen_MS (Moderator):
Q:
re connection. Has there been any thought of publishing primary/secondary information to the AD? Could be used by connection to find where the primary really is if it has never connected before (only 1 server specified on connection)
A: That's a great suggestion, and you could build it into your own application...

Mark Wistrom (Expert):
Q:
Just a suggestion for you consideration Mark W. It would be great if you got together with your fellow PM's for trans. rep., log shipping & clustering and put out a detailed comparison guide of each. Would love to see the results of the bake off!
A: SO WOULD I!!! :) We do have a set of slides that gives the pros/cons of clustering/mirroring/log shipping/replication. basically there are a lot of trade offs that you have to decide what you want.

Michael Zwilling (Expert):
Q:
So in the client A-mirror/client B-principal/ network fault scenario with full-safety and witness is on principal side of network fault, mirror would never pick up but furthermore, because mirror is gone, no transactions would commit for client B/princip?
A: Perhaps there is some confusion here - clients must always connect to the server that is hosting the database acting as the principal - whether that is server S1 or server S2. A client cannot connect to the mirrored database.

Steve Lindell (Expert):
Q:
Combining failover clustering and database mirroring sounds complicated and tricky. Is it recommended? What are the special considerations?
A: It is not too bad and fully supported. They work independently with their own failover detections. It gives an added level of redundancy, but is an added level of administration and indirection to keep track of.

Stephen_MS (Moderator):
Q:
That's a great suggestion, and you could build it into your own application... but it's always better if you guys do ;)
A: We know. :)

Mark Wistrom (Expert):
Q:
Mark -- are those comparison slides available for download?
A: hmmm... use the Microsoft search (not Google :D ) and look on the web. I have done webcasts where the breakdown is listed as a grid.

Steve Lindell (Expert):
Q:
If you manipulate a login's SID, couldn't you login to the mirror - same as with log shipping no? If true, would a user logged into the mirror prevent synchronization - again, same as with log shipping?
A: The users cannot log into the mirror (it is always restoring) so a client cannot block log application. If you need client access on the mirror you can create a snapshot which does not impact the state of the mirror.

Mark Wistrom (Expert):
Q:
Mark - I don't understand your answer of : licensing has not been determined yet. ' speaks to the question of ' Would Express be allowed to act as a witness -- it isn't the mirror capability per se (wrt to function) ' can you explain pls?
A: we have yet to determine the different SKUs for the different roles; everything can change. We may allow the partners to be on standard; we may not. We may require the witness to be enterprise edition; we may not.

Michael Zwilling (Expert):
Q:
whether that is server S1 or server S2. A client cannot connect to the mirrored database. - understood; so Mirror never becomes principal and client A (prior conn. to principal) is down but no transactions commit and client B is down for transactions?
A: If the principal and witness are on one side of a network "fault" (i.e. mirror cannot talk to either) and the principal machine is down, the mirror cannot takeover for the principal.

Mark Wistrom (Expert):
Q:
Datatypes like binary, blob´s can be mirrored?
A: Yes, if it is written to the log, then it is sent to the mirror database.

Mark Wistrom (Expert):
Q:
those clustering/mirroring/log shipping/replication comparison slides available for download = "DAT431 High Availability Technologies in SQL Server 2000 and SQL Server 2005 (code named Yukon) A Comparative Study.ppt"???
A: Sounds like a talk I gave in July....

MarkA [MVP] (Expert):
Q:
We store spatial data in our SQL database (ArcSDE from ESRI); can we use mirroring for this database?
A: Yes, if it is contained within the database transaction log then it will be mirrored.

Steve Lindell (Expert):
Q:
Has there been any performance testing done on mirroring? I'm thinking of the far away data centre solutions which are being required in the US financial institutions.
A: We have not published performance numbers for mirroring, but will have numbers for RTM. In general the overhead for a remote site will make running with safety on (wait on commits) less attractive.

VitalyA (Expert):
Q:
If network connectivity is available between primary and developers but connectivity is lost between primary and witness. Can developers continue to alter databases on primary?
A: If there is connection between primary and mirror then yes, developer (client) can continue to use database on primary.

Stephen_MS (Moderator):
Q:
Okay, This is my first time here so bare with me. My system recently went down on me. What happened was my windows came up blank. I could see the screen saver but nothing else was there. It also gave me a message saying "Windows Explorer has terminated
A: This is a SQL Server chat, not a Windows chat. Sorry. You might ask it in one of the Windows newsgroups. Start at http://www.microsoft.com/communities/newsgroups/default.mspx.

Mark Wistrom (Expert):
Q:
..the mirror cannot takeover for the principal. - right. But since the mirror is now unavailable to the principal due to network fault does that mean that all transactional work comes to a screeching halt for client B on the principal?
A: depends on the safety level and the connection status to the witness. if the safety is OFF, the principal continues on, regardless of the mirror. if the safety is ON and the principal can connect to the witness, the principal continues on. IF THE SAFETY IS ON AND THE PRINCIPAL CANNOT TALK TO ANY OTHER SERVER (WITNESS/MIRROR), IT TAKES ITS COPY OF THE DATABASE OFFLINE. this is correct behavior. it prevents "split brain." you need to think about it for a bit.

VitalyA (Expert):
Q:
Is there any way to allow the secondary server to be set as a read-only reporting server, similar to 'RESTORE DATABASE db WITH STANDBY = file_name'?
A: You can create database snapshot of mirrored database and it can be used for read-only access.

Steve Lindell (Expert):
Q:
If you are having issues around primary, secondary and witness seeing each other and you need to get a database back online - emergency - can you?
A: If communications between the servers is down you can remove mirroring and reestablish when things come back on line. If running with safety off there is a alter database command that forces a failover.

Mark Wistrom (Expert):
Q:
Since a witness sounds like a quorum disk (as in shared quorum cluster) and you thinking of a majority witness (node) solution in the future?
A: Correct, the witness acts like the quorum. We have talked with the windows team about our solution and they have taken some of our requests. We want to be out of the quorum business.

Stephen_MS (Moderator):
Q:
Can you have multiple Witnesses for redundancy?
A: No.

Mark Wistrom (Expert):
Q:
to think about it for a bit. .. So, with Safety on and client B/Principal/Mirror on the same side of a network fault (i.e. able to talk to each other) client B would still be able to commit transactions to the principal even though mirror is not there?
A: Where is the witness? If the principal can contact the witness, then everything is OK. If the witness is not there, safety is on, and the mirror cannot be contacted, then the principal takes the database offline. The reason is that the principal cannot tell if the witness and mirror have gotten together and failed over.

Michael Zwilling (Expert):
Thanks for all of your interest in this feature! It was great talking to you. Goodbye.

MarkA [MVP] (Expert):
Thanks for your questions, see you on the newsgroups!

Steve Lindell (Expert):
Thanks for the questions.

Mark Wistrom (Expert):
Hope we answered everything!!

Newsgroups: microsoft.private.sqlserver2005.relationalserver.dbmirroring_clustering and http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx

Website: http://www.microsoft.com/sql/default.mspx