Question and Answer LogQuestions and Answers: Tiru Asked: Mark, What type of Mirroring options are you going to talk today ? High Availability OR High performance ? Answered: All three. :) sanjay Asked: how to setup the witness? Answered: alter database dbName set witness='tcp://WitnessName:portNumber' For instance alter database dbName set witness='tcp://myMachine1:5022' Of course before you do this you have to create endpoint on witness which listens on port 5022 RJ Asked: What is a corner case bug? Answered: A corner or edge-case bug is one which is only hit in very rare/hard to hit circumstances. Silaphet Mounkhaty Asked: Does Mark have blog? Where Can I get the scripts? Answered: No BLOG. Which scripts? There should be examples in BOL. RJ Asked: I have seen some Internet posts about SSIS not restarting after SP1 is installed, is there anything we need to be aware of before applying SP1? Answered: Sorry don't know anything about that... a Asked: does georgraphical location of mirrored servers matter, and how it is address in mirroring? Answered: Geographical location matters in terms of the bandwidth and latency of your network. If you have geographically seperated partners, you may find that a setup depending on automatic failover may see false failures resulting in unneeded failovers. Remember, just because you can implement something, doesn't mean you should. Noravia Asked: Can be the instance of sql server 2005 that will be mirror in a server with other sql instances (2000,2005)? Answered: DBM is only supported in SQL Server 2005, you can not use SQL Server 2000 for this. Peter Asked: Can you have more than one mirrored copy Answered: No, in SQL 2005, only one mirror is supported. We have heard many requests for multiple mirror support. Riley Schumacher Asked: what happens if the witness goes down? Answered: If the witness goes down, the mirror will not failover automatically. As long as the mirror and principal stay up, service will be unaffected. The witness is used only for automatic failover. It is not a single point of failure. mjanzou Asked: Can you mirror multiple user databases by creating multiple mirror sessions on the server? Answered: Yes. But keep in mind that databases failover individually and not as a group. This is mainly an issue in the case of manual failovers, but can also come into play with automatic failovers in cases where the network is marginal. marchesi consulting Asked: can you mirror from a cluster? Answered: Yes. Mike V Asked: If the mirroring session is interrupted, is a full re-synch required? Answered: No. Upon reconnection, the mirror continues to catch up from where it left off. sanjay Asked: how to setup the witness i am not able to understand witness?what is the req for witness Answered: Witness is a third server (other than principal and mirror) for forming a quorum in case of one of the parties is unavailable. There is no data stored on witness and transfered from/to witness. Witness can be any of SQL Server 2005 SKU, it can be Express Edition or Enterprize. Also check BOL for more information. Micah Asked: He mentioned you can turn it to Asynchronous if you like. However, that's only with Enterprise edition, correct? Answered: That is correct. btary Asked: Does Data Mirroring work between domains? If so, does it have to be a full 2 way trust or can it be a 1 way trust? Answered: Yes, you can mirror between two untrusted domains. It requires certificates for security. See BOL for more information. Noravia Asked: Sorry the questions is: If I can have an instance of sql 2000 running in the same server with another instance 2005 (that will be use for mirror) Answered: Yes, you can have sql 2000 and 2005 running as seperate instances. Sue Asked: A a witness server act a a witness for multiple mirror sessions? Answered: Yes. Mike V Asked: If the mirroring session is interrupted, is a full re-synch required? Answered: Normally no. The partner will come back and resync automatically only the changes that are needed. Mark Asked: If network goes down, does mirror automatically catch-up when network is re-established? Answered: Yes. Vipul Shah Asked: Does client on a priciple have to wait for the tran to be commited on mirror as well? If yes, isn't a huge performance hit for a client? Answered: In synchronous mode, this is the case. How big an impact this has in real-world scenarios depends on both the network and the transaction mix. In many scenarios, the impact of this is negligible. Mark gave some examples of what the performance hit would be. Salvador Flores Asked: Can you use mirroring to one server and use log shipping for another server? Answered: Yes. This is a means of having a second or more "mirrors". Salvador Flores Asked: If we failover to the mirrored server do we have to re-setup Log Shipping to the remote servers? Answered: Yes, you would have to do that manually. John Asked: Is DDL operation working with database mirroring Answered: Yes, DDL operations work the same with or without DBM. There are several operations which can not be performed on a mirrored database, they will fail saying that they can not be performed on a mirrored database. dennis Asked: What is the RECOVERY model of PRIMARY and/or MIRROR databases? Answered: The recovery model of all mirrored databases always has to be FULL. Michelle Asked: Can the witness be a VM on the same machine? Answered: Yes. However such a setup mostly negates the reason for using a witness. If you do use a witness, then you should have it at least on a different server. Bob Asked: Can the witness server be clustered? Answered: Yes. a Asked: any timeouts can be increased if there is any network glitch Answered: You can increase the timeout values for the pings, but if the network gives an error, then database mirroring declares an error. Vipul Shah Asked: How about the recovery model for mirror (not the principle), can that be in simple? Answered: The mirror is setup using a backup from the principal. As such, it's recovery model is the same as the principals. The recovery model cannot be changed while mirroring is configured, so the databases will always be in FULL recovery model. Pete C Asked: If I have 100 databases mirrored on an instance, how much latency could I expect during an instance fail over? Answered: There is no concept of instance failover in SQL 2005. Failovers are on a per-database level. If your server crashes, failover should be detected very quickly, but failover time would depend on how much log was in the redo queue of the partner server. If everything is synchronized, I would guess it would be on the order of 10s of seconds. Noravia Asked: If master is not mirror how we will syncronize linked servers, logins, etc? Answered: master can not be mirrored. You have to transfer your logins, and other data outside of database mirroring. ian Asked: Can you implement logshipping from SQL2005 DB to SQL2000? Answered: You can implement backup logshipping from SQL2000 to SQL2005, but it is not tested and you have to set it up manually with your scripts. Aco Asked: What's new for mirroring in sp1 for sql 2005? Answered: There are no significant or externally visible functionality changes in SP1 from what was in the RTM release. Don't forget that there is the database mirroring monitor. a Asked: can witness monitor more then 1 mirroring serve, kinda like target server Answered: Yes, a witness can monitor multiple mirroring partnerships. The overhead of acting as a witness is a minimal load on a server. Jimmy Asked: non-logged trans can be mirrored as well? Answered: You have to use full recovery model for database mirroring, nothing else is supported. And in this configuration all transactions are logged, you do not have to worry. Non-logged transactions are transactions that update the boot page, etc. and mirroring does take care of those pages by sending them out of band. Minimally logged operations are fully logged when using mirroring. Aco Asked: Is it now mirroring recommended and supported by Microsoft? Answered: Yes mirroring is now fully supported. There are already many deployed production systems using mirroring both inside and outside of Microsoft. Peter Miller Asked: is the mirror server a part of the same cluster and the sending server Answered: I think you are asking about mirroring within a cluster. You can but I would not recommend it. AJ Asked: Once you fixed the problem on Server A, how can you switch it back to be the principle server? Answered: You can initiate a manual failover using the ALTER DATABSE mydb SET PARTNER FAILOVER command. Please see Books Online for more details. Se-In Park Asked: Books Onine reads "To connect to a database mirroring session, a client can use either SQL Native Client or .NET data access provider for Microsoft SQL Server as the data access provider." Can I should any third party oledb or odbc or prior verision of mdac to connect to principle or failover server Answered: You can use prior versions of mdac to connect to database with database mirroring it will work. However prior versions do not support new feature of this release - automatic client redirection, so you will miss this but for the rest it will work just fine. And because we do not know what third party oledb or odbc providers you talking about we can not tell you for sure. My take is - if you able to connect to SQL Server 2000 you will probably be able to connect to SQL2005, but once again - it will not support new features of the latest MDAC. If after a failover with database mirroring, the client is responsible for connecting to the new principal. Pshah Asked: I am assuming that a physical database structure at the mirror does not need to be a replica from mirror. Answered: Disk layout does not have to be identical between primary and mirror machines. When preparing for mirroring you can specify "with move" clause to your restore statement and put files into proper location on the mirror. nanda Asked: can the mirrord database be on a remote site Answered: Yes, keeping in mind that this may come with more network latency, potentially making synchronous mirroring unusable. See earlier question on geographical seperation. Michelle Asked: Does the witness require a SQL SERVER license? Answered: Witness can be any SKU of SQL Server 2005. If you install Express edition which is free you do need to have license for witness. Anthony Asked: If a change in table schema takes place, does DB mirroring need to be suspended and a new "snapshot" / backup used Answered: No. The changes will be replayed on the mirror. DB Mirroring is a physical copying of the data. Replication is a logical view of the database. Jimmy Asked: even DB is full recovery mode, a table can still be truncated, which is not logged. Would the truncation on mirrored copy? Answered: There is enough logging done such that any operation can and must be replayed on the mirror. Praveen Asked: Can i mirror from 64 bit Enterprise edition SQL 2005 to standard SQL 2005 edition 32 bit Answered: Yes, absolutely. You should be able to work in this configuration. Just keep in mind in case of failover your mirror will become principal and should be able to cope with workload of original principal. John Asked: what happened on the mirrored DB when primary DB marked 'suspect' ? Answered: If there was no automatic failover due to the event that caused the principal database to go suspect, then the mirror will continue sitting there as a mirror. The admin can force the mirror to take on the principal role by issue a ALTER DATABASE mydb SET PARTNER FORCE_SERVICE command. See Books Online for more detail. Pshah Asked: Could you please clarify if the transaction commit in a synchronized database mirror happens only when the final acknowledgement of hardening at the mirror is done. Answered: Correct. In synchronous mirroring, a commit guarantees that the log has been hardened on both partners. JayK Asked: What happens to Full-Text Catalogs when a DB fails over? Are they still in sync or does a rebuild and repop need to be executed? Answered: FT catalogs will not be fully in sync, but will continue to rebuild on the mirror. Vipul Shah Asked: Does client on a priciple have to wait for the tran to be commited on mirror as well? If yes, isn't a huge performance hit for a client? Answered: Depends on the workload. There will be a performance hit. See the webcast for solutions. Danial Asked: do these pings have to be continually? Answered: Yes. These pings are the "keep alive" that are used to determine if the other partner or witness is still healthy. Peter Asked: How much impact is it to run in synchronous vs asynchronous? Answered: This will depend on your netwrok and your workload. In the worst case - which is a single connection running lots of short transactions coupled with a high latency connection between principal and mirror - the impact can be significant. In most cases however, the impact of synchronous mirroring can be minimal. Also, Mark gave some examples during the talk. A K S Asked: what if i am getting the ping reply in a range of 35 ms to 148 ms, is it healthy Answered: Yes, but I don't think it would support a high performance application using database mirroring. Mark Asked: Log Shipping has the overhead of taking a periodic 'log backup'. Does data mirroring require a 'log backup' or will I be able to get away from those dozens of log backups I generate each day? Answered: Mirroring itself does not require periodic log backups. However if the log is not backed up on the principal, it will grow indefinitely as truncation will be held up by the un-backed-up log. Yes, you need to continue to take log backups. Aco Asked: Can we improve performance having principal and mirror attached on SAN? Answered: No. The principal and mirror do not share any storage. Well maybe. The bottleneck on the system generally has to do with the log drive on the mirror. Make sure that has the highest performance. dennis Asked: Does database FILE PROPERTIES have to be the same for both parties in terms of file growth? Answered: Database on mirror is in recovering state, you can not change anything on mirror. File properties in terms of file growth or max size will be exactly the same Pshah Asked: Is there any situation where in the database corruption would propagate to the mirror Answered: The only conceivable corruption that could propagate to the mirror is a disk corruption of the principal's log that was not detected when the log was read in for sending to the mirror. This is unlikely to occur. Or if there is a bug in the code. This is unlikely to occur. ;) RJ Changes Question To: When I installed SP1 the GUI reported all Success but when I checked the Report Log all but 1 Product said "Not Applied", is this expected? Answered: Sorry, I don't know about setup. Peter Asked: How much impact is it to run in synchronous vs asynchronous? Answered: Depends on how much you log. See the talk. Kyme Asked: How does client change to the old Mirror new Principal? Answered: See BOL on client failover for database mirroring. Vipul Shah Asked: During what you are just showing, what happens to the client transactions? are they lost? Answered: In flight transactions are rolled back on the new principal. Noravia Asked: In case of failover to B. Will all the transactions br mirrored to A after A come up? Answered: Yes. A comes back up and starts to synchronize again. Noravia Changes Question To: Can we change the default value of 10 (for pings)? Answered: Yes, you can change the ping values. Praveen Asked: Can i mirror from 64 bit Enterprise edition SQL 2005 to standard SQL 2005 edition 32 bit Answered: Yes, but it is not supported. Tiru Asked: Can we have reference of those customers ? Answered: The link to the referenced customers is: http://download.microsoft.com/documents/customerevidence/26366_ServiceU_CS_final.doc Calvin Lee Asked: Is there any tool can be used to synchronize login account and jobs between principle and mirror servers? Answered: Check out kb 246133 at Microsoft.com nanda Asked: can we setup mirrord database using native restore db command Answered: Yes, just remember to use the NO_RECOVERY option JayK Asked: What happens to Full-Text Catalogs when a DB fails over? Are they still in sync or does a rebuild and repop need to be executed? Answered: The Full-Text Catalogs will internally replay the changes. a Asked: can mirroring be done throught double firewalls Answered: Yes, you need to configure firewalls to allow data communication between primary and mirror (and witness if you have it). Any numbers of firewall can be used they just have to allow data transmission sanjay Asked: what are the default ports to select for prinicpal witness and mirror Answered: There are no default port numbers. Brent O. Asked: The question was can mirroring be DELAYED, not RELAYED. Meaning, can it be delayed a few minutes? Answered: It can be suspended. "alter database dbName set partner suspend". To resume "alter database dbName set partner resume". If you want to have the mirror be delayed, then use log shipping. nanda Asked: is there any impact on transaction log size with the mirroring. log.will it grow if the mirrord server is not available,or transactions applied on mirrord database Answered: There is no impact on transaction log size in terms of extra logging occurring. The truncation of the log itself will be held back by database mirroring if there is unshipped log. So for instance, if mirroring is SUSPENDED for some time, log truncation will not occur on the principal until either mirroring is resumed and the log shipped or mirroring is removed entirely. Micah Asked: Where can we get the document he just showed? http://download.microsoft.com/documents/customerevidence/26366_ServiceU_CS_final.doc Vijay Asked: Does this mean that performance penalty is different for different states of Mirroring Answered: On a synchronizing system in a syncrhonous mirroring configuration, mirroring will attempt to slow transactions to attempt a final switch into SYNCHRONIZED state at which point all commits will carry the double-written guarantee. Michelle Asked: Is the log transfer itself faster here than in previous log shipping mechanism? Answered: Log shipping used log backups as a means of log transfer, the backups then being restored continually on the destination server. Database mirroring sends log directly from the principal to the mirror server. d Asked: Do log back-ups on the primary get mirrored? Answered: Log backups themselves are simply sent to the storage destination specified by the backup statement. The history of the log backups which is stored in MSDB does not get mirrored. The database state connected to the log backup does propagate so after a failover, a log backup on the old mirror (now the principal) will coninue the same log backup chain. d Asked: How do you manage the log file on the mirrot from growing too large? Answered: You have to do backup log (on primary) and do "dbcc shrinkdatabase" to keep database size under control. When the log is truncated on the principal, the mirror also truncates its log. nanda Asked: can the mirrod database can be setup with a native backup (primary) and restore Answered: To setup mirroring you do backup database on primary and restore database with norecovery on mirror. read_jackie@hotmail.com Asked: You can have both Database Mirroring and Log Shipping running for a single database, right? Answered: Yes. Anthony Asked: How does the log data / metohd differ from replication to mirroring, I presume replciaiton takes the changes and mirroring the enitre log Answered: Replication is transactional. It replicates the actions from the source to the destination. Mirroring is physical and results in what is effectively an identical second version of the database. Mirroring does move the entire log as it is being generated. You are basically correct. Micah Asked: Let's say I rebuild a clustered index on my primary database, does it get rebuilt on the mirror? Answered: Yes. Please see earlier questions about other operations and whether they propagate to the mirror. Kyme Asked: Client will automatically reconnect to the mirror incase of failover with witness Answered: In case of any failover client will be disconnected. And when (if) your application decides to reconnect it will be automatically redirected to the server which is principal at this moment. Ramesh Kancharla Asked: I missed this part - where is mirroring monitor? I am not able to find in the management studio. I already have a mirror up and running. Answered: Right click on the database that is being mirrored. Go To Tasks. "Launch Database Mirroring Monitor." RJ Asked: When I installed SP1 the GUI reported all "Success" but when I looked at the Log Report it said 8 of the 9 Products where "Nat Applied", is this expected? Answered: I don't know about setup problems with SP1. sanjay Asked: i have SQL SERVER 2005 can i use this version to install as witness instance on the principal server Answered: You can install several instances of SQL Server 2005 on the same box and use them as partner in database mirroring. However we do not recomend using more than one instance on the box in database mirroring. In case of the failure you losing several instances at once which defeats purpose of database mirroring - extra data protection. However this is perfect configuration for development A K S Asked: then why it is showing too much of fluctuations Answered: Your network is not reliable. A K S Asked: what if i am getting the ping reply in a range of 35 ms to 148 ms, is it healthy Answered: I would not consider it healthy. A K S Changes Question To: then why it is showing too much of fluctuations, i.e respond time from 35 ms to 148 ms Answered: Your network is either not reliable or some other application is competing for bandwidth. Pshah Asked: What is the minimum network ping time that must be sought as a guaranteed network response time ? I mean good practices... Answered: As fast as possible. ant Asked: If I want to mirror the db which is published for trans replication and the distributor is a physically different server, what is the recommendation/thoughts about making this distributor also a witness ? any best practices db mirroring white paper? Answered: Yes, the distributor could be the witness. Bob Asked: Can you have "Principal A" running on Node1 and "Principal B" on Node 2: An "Active/Active" environment? Answered: Yes, but it is not recommended. nanda Asked: is there any impact on transaction log size with the mirroring. log.will it grow if the mirrord server is not available,or transactions applied on mirrord database Answered: If you suspend mirroring or the log records cannot be sent to the mirror, then the principal will not truncate the log. You need to fix mirroring or turn off mirroring. Chris Changes Question To: What if the ping reply is greater thatn 148ms. What is the maximum before failure. Anwered: Mirroring does not have a maximum limit. You will see very poo performance with such a high ping. Janet - Webcast Team Asked: How do I see a full screen view? Answered: To maximize your viewing area, select the F5 key Noravia Asked: Please, When the recording of this webcast will be available for download? Answered: http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032290576&EventCategory=5&culture=en-US&CountryCode=US Aco Asked: After the "Authomatic Failover" do we need to map logins? Answered: You will have to, or your clients will not be able to connect to the new principal Michelle Asked: Who clears the Transaction Log on the secondary server? Do we need to backup this database or its Tran Log? Answered: The transaction log on the secondary requires no attention. The principal will trunacte it's transaction log as log backups are taken and those same truncations will propagate to the mirror. Pshah Changes Question To: What is the minimum network ping time that must be sought as a guaranteed network response time ? I mean good practices... Answered: As fast as possible. d Asked: Not sure if you're anser may have been truncated. I think you said that on the mirror, you use DBCC shrinkdatabase periodically, to keep log file from growing endlessly? Answered: No, this is not required. The log file on the mirror will be truncated as the principal's is truncated. dan Asked: Hello Mark - I've arrrived late and hope this has not been asked yet. Do we need a witness? Answered: A witness is not require in a mirroring configuration. It is however, require if you wish to have automatic failover. The witness is used to prevent both partners assuming the principal role in cases where communication is lost between partners. nanda Asked: is mirroring available for 64 bit versionof sql2005 any limitation with sql2005 64 bit to a mirrord database in sql2005 32bit Answered: Mirroring is fully supported between 64bit and 32bit versions of SQL Server 2005, there is no limitation in this area jay Asked: Can you use Mirror for Reporting & web service application? Answered: A mirror database is not directly accessible. However you can take snapshots of the mirror for reporting purposes. Calvin Lee Asked: When principal server send data to mirror server, can the data be encrypted? Also, is there any tool can synchronize the login account and jobs between the servers? Answered: Communication is done via endpoints. You can set endpoints to support encryption if you need. dennis Asked: Can you configure database mirroring on servers that reside to different domains? Answered: Yes, see answer at top about certificates. Se-In Park Asked: Which mirroring with snapshot or transaction replecation would be faster to initialize Mirrro or replicated databases? Answered: A mirror can only be initialized using a full database backup of the principal. Michelle Asked: In log shipping, though, you can use the secondary database for read-only operations. Here you can't, right? Answered: Wrong, database snapshots can be created on mirror and used for read-only operations. See BOL for details Sue Asked: Which mirroring option is available on standard edition? Answered: Synchronous operation only. Increased REDO speed on the mirror. Micah Changes Question To: Where can we get the document he showed regarding one of his customers? Answered: see above. Peter Asked: Is there plans in the near future to support more than one mirrored copy? Answered: Multiple mirrors will probably not be in Katmai, the next version of SQL. Vipul Shah Asked: What will be the state of mirror database (slave one), will that be readonly? Can user run read queries? Answered: The mirror database itself is inaccessible to queries directly. However snapshots of the database can be created on the mirror for read-only access. Ramesh Kancharla Asked: Question: How does client change to the old Mirror new? Answered: There is a 'failover partner' key word in the connection string that has to be specified so that the client/application will automatically talk to the new principal. Mike Watson Asked: Can you asynchronously mirror one database while synchronously mirroning another database on the same server? Answered: Yes, you can. Database mirroring is per database, you can have as many databases as you want although we do not recomend too many because of memory and performance impact Prasad Asked: How does the mirroring work for the new objects(tables)created in principal Answered: The same changes are automatically applied to the mirror database when the log has been replayed on the mirror. This is the same mechanism for all changes to the principal. Ramesh Kancharla Asked: Question: How does client change to the old Mirror new? - There is a 'failover partner' key word in the connection string that has to be specified so that the client/application will automatically talk to the new principal. Answered: SqlConnection dbmConn = new SqlConnection("server=server1;Failover partner=server2;uid=sa;pwd=xxx;Initial Catalog=dbName;Pooling=false;"); Vipul Shah Asked: What is the basic difference between Clusters and Failover? I think I missed that one Answered: The biggest basic difference is that clusters are at the instance level while mirroring is at the database level. Chas Asked: I had a problem with the SP1 installation. One of the upgrades was not installed. However, I reapplied the SP1 and it worked just fine. Moral of the story: try again. Micah Asked: Let's say I rebuild a clustered index on my primary database, does it get rebuilt on the mirror? Answered: Yes. All the rebuild changes are logged and they go to the mirror. Mike Rael Asked: Does a failover require any DNS changes? Answered: No.