O Webcast sobre Alta Disponibilidade no SQL Server 2005 foi excelente !! Foi apresentado um overview sobre as novas features do Cluster e um passo-a-passo sobre a configuração do Database Mirror.
Para aqueles que perderam a apresentação, veja abaixo as perguntas e respostas que rolaram durante a apresentação.
Question: Where can I get Technical White Papers for SQL 2005?
Answer: Check out: http://www.microsoft.com/sql/2005/techinfo/default.asp
Question: Can I get more information on SQL 2005 High Availability?
Answer: Sure, check out: http://www.microsoft.com/technet/prodtechnol/sql/2005/maintain/sqlydba.mspx#EHAA
Question: Is On-Line restore supported while the database is replicated?
Answer: yes
Question: can you restore filegroups without reapplying the trans log?
Answer: Only if the filegroup was marked as readonly before the backup was done .. otherwise, to have a consistent set of data, any subsequent logs will have to be applied after restoring the filegroup.
Question: Table partitioning is not available in SQL 2000 below but in SQl 2005, Right ??? Thx
Answer: Yes, table partitioning is new for SQL Server 2005. We had distributed partition views in SQL SErver 2000 .. and still do.
Question: Will it be possible to modify a single object in a publisher set (or remove it)
Answer: yes, you can modify the schema for an object that is published, and in SQL Server 2005 any schema changes are automatically captured and sent to subscribers.
Question: reporting services 2005 and analysis services 2005 are cluster-aware?
Answer: AS is cluster-aware .. and it supports multiple instances as well.
Question: What is the best way to support database object level recovery?
Answer: there are 3rd party solutions to extract the data of a specific objects. Of course you can restore a database backup and extract the data for the object and then replace the existing data with the previous version that you’ve extracted, but due to the mismatch or potential integrity problems, SQL Server doesn’t provide object-level restore itself.
Question: Is load balancing available?
Answer: Yes, it is.
Question: Isn’t one server totally lying unused during this process?
Answer: load balancing and failover clustering are different. yes, in general one server is not doing anything, although you can run in an “active-active” scenario. the only problem with that is after failover you have a single server trying to do the work of multiple servers.
Question: Can we run active,active clusters?
Answer: Yes, that is one of the supported configurations. In the case of failover, the workload on the remaining node will increase.
Question: Will Yukon support mount points in a cluster?
Answer: Yes, in SQL Server 2005 mountpoints are supported.
Question: must all the cluster nodes have the same hardware configuration?
Answer: to be supported, the hardware must be on the HCL, which will probably mean that all nodes are the same.
Question: Can the cluster be active/active and still share the database?
Answer: No, only one instance can be accessing datafiles at a time.
Question: What are mountpoints? thanks
Answer: MPs are the ability to add an entire disk as a subdirectory under another disk .. avoiding the need for drive letters, etc.
Question: How do you set up Active-Passive cluster. How do you define which one is active and which one is passive
Answer: “active – passive’ and “active-active” are poor terms that Microsoft introduced. 🙁 basically you can continue to add Virtual servers to the cluster as you have resources. you can control which node is serving which virtual server.
Question: what is mount point?
Answer: MPs are the ability to add an entire disk as a subdirectory under another disk .. avoiding the need for drive letters, etc.
Question: What editions is cluster failover available?
Answer: Enterprise edition supports as many nodes as the underlying OS supports; Standard edition supports 2-node virtual servers (though there may be more nodes in the cluster).
Question: What is the distance limit between avtive and passive node?
Answer: the distance limit is set by the hardware on the Hardware Compatibility List. i believe that the limit is set by some “round trip” ping time.
Question: Has there been testing with other cluster software such as Veritas?
Answer: No formal testing has been done with 3rd party cluster software.
Question: How can I get a beta copy of SQL Server 2005 which supports replication (as a publisher, distributor or subscriber)? Thanks!
Answer: You have to wait until the public beta is available (will be soon).
Question: HOW is SQL Agent running on node different from on single instance?
Answer: SQL Server Agent is part of the SQL Server resource group, so it fails over with its corresponding SQL Server instance (as does the Fulltext service). Other services, like Analysis Services, can failover independently.
Question: I assume that multiple-instance clusters do not require separate load-balancing equipment?
Answer: ??? Microsoft cluster Services is not a “load balancing” solution.
Question: Is N+1 failover cluster only support on WIN2K3 ?
Answer: N+1 or N+I configuration is determined by how you setup the cluster. It can be configured in any Windows cluster system.
Question: are there any plans to work / certify SQL 2005 on Veritas clusters?
Answer: No.
Question: does load balancing run at the software or hardware level?
Answer: Microsoft has load balancing through Network Load Balancing services .. SQL Server’s failover clustering does not integrate with NLB except for readonly databases where multiple copies might exist. For read-write there is no load-balancing supported directly by SQL Server. Datafiles can be used by only one SQL Server instance at a time.
Question: If multiple-instance clusters keep many nodes active to be adressed in a round-robin fashion, how is this different from load-balancing?
Answer: We are not discussing “load balancing” here. you must be thinking of Windows “Network Load Balancing.”
Question: How do you compare this non-shared disk technology with shared disk technology such as exisits in competing products such as RAC – Oracle
Answer: They are fundamentally differenct beasts: Shared-Nothing vs. Shared-Everything. It is a religous argument as to which one is better. 🙂
Question: With 2000 or 2005 could you use a NAS as your storage for a cluster or would a SAN be the right way to do this?
Answer: typically SAN is the way to go, though you can easily use iSCSI-based storage, which could be NAS. To use non-iSCSI NAS you need to enable a trace flag to allow non-local disk.
Question: is database mirroring different from log shipping? how?
Answer: DB Mirroring is handled inside the server process. log shipping is completely outside the engine
Question: What versions of 2005 can do DB mirroring?
Answer: fully supported in EE. limited support in STD. witness can be anything.
Question: What is the difference between SQL 2000 and SQL 2005 Database Mirroring
Answer: there is no mirroring in SQL 2000
Question: Is there a link with the capabilities/changes of each edition of SQL Server
Answer: There is a copy of a very recent Books Online out on TechNet, it’s got the what’s-in-each-edition information. Sorry I don’t have a link but a search should find it.
Question: I heard the presenter say SQL 2005 EE is limited to 8 GB of memory. Isn’t SQL 2005 memory limited by the OS memory (Windows 2003 supports up to 32 GB on 32-bit)?
Answer: Yes the Enterprise Edition is limited by the OS Memory
Question: Are uncommitted transactions written to the mirror in real time?
Answer: log blocks are sent on the wire as they are being flushed to the log.
Question: Is DB Mirroring replacing log shipping?
Answer: 🙂 we are trying not to invest in log shipping in the future.
Question: Is there a tech net virtual lab for setting up Clustering on SQL Server
Answer: No. It is very difficult to setup failover in a virtual lab environment.
Question: So, auto failover in enterprise edition, manual failover in standard?
Answer: auto failover is availble in STD.
Question: Can I add more nodes after the cluser been build (ie, from 3+1 to 5+1) w/o bring down the current cluster env. ?
Answer: yes, you can. You’d then run SQL Server Setup to expand your SQL Server virtual server to the new nodes if that’s what you want to do.
Question: the mirrored DB shouuld be treated as readonly by apps, right?
Answer: you cannot access the mirror database. it is “in load”
Question: what is the difference between Db mirroring and log shipping
Answer: DB Mirroring is handled inside the server process. log shipping is completely outside the engine
Question: with windows 2003 clustering, is a quorum drive still necessary?
Answer: No, there’s Majority Node Set quorum in Win2003. SQL Server 2005 fully supports MNS clusters.
Question: Will corruption be sent to a mirrored database?
Answer: sql does do corruptions. 🙂 seriously. if there is a bug that causes the wrong log to be generated, then yes.
Question: does s1l2005 standard version support cluster?
Answer: Standard edition will support 2-node virtual servers (though the underlying cluster might have more than 2 nodes).
Question: ls DB mirroring support non-logged operation ?
Answer: the recovery level must be FULL.
Question: Does DB Mirroring require both servers to be within the same subnet?
Answer: no
Question: Can you mirror databases on clustered servers?
Answer: yes. the failover times are a lot different though.
Question: Is licensing information available for the various mirror setups? Do you need licenses for the mirror and or witness?
Answer: answered above.
Question: Is it fair to say that mirroring is closer to DTC transactions than it is to log shipping?
Answer: yes. but it really is like DTC in the “HIGH PROTECTION” mode, which you shouldn’t be running in.
Question: doyou need 2 licensed copies of SS touse mirroring?
Answer: No if the mirror server is not used to run any other workload only to keep the mirror uptodate.
Question: what is meant by endpoint?
Answer: An endpoint is an entry place into SQL Server. In the past this was much less formal .. you could just access your SQL Server via named pipes, TCP or whatever. You still can do this but for security you can now be very specific about who and how the endpoints can be used.
Question: what will happen if the mirror is not avilable? will the primary one still work?
Answer: depends on whether or not you have a witness and what the safety level is. but, in general. the principal will continue if the mirror fails.
Question: Can SQL2005 support more than 1 mirror being written to simultaneously, if so, what is the max?
Answer: there can only be one mirror.
Question: why the transactions are not commited in the mirrored server?
Answer: Not sure what you are asking .. but only the Principal is taking requests .. a commit will occur at the Principal and then get mirrored on the Mirror server.
Question: If the write to the log on the mirror is written before commit, what happens on the mirror if a transaction fails on the primary? Is it subsequently Rolled back on the mirror?
Answer: it is not rolled back. the client doesn’t know what happened until the client reconnects. this is the same with sql today.
Question: Can the partner server be clustered?
Answer: yes.
Question: Automatic mirroring would be a bad option for HIGH volume SQL Servers. True or False?
Answer: “bad option”?? if you generate a lot of log, then mirroring will add a certain overhead with the synchronous setting. this is completely load dependent.
Question: Can a witness be SQL Express?
Answer: yes.
Question: I read the MDAC automagically detects the mirror, is that correct?
Answer: On your original connection, SNAC (the SQL portion of MDAC) detects the mirror so if the principal fails, SNAC can automatically redirect you to the mirror.
Question: how do you setup a database mirror? i.e. use a full backup restore on the mirror and SQL automatically detects the changes and resynchs?
Answer: backup, restore and turn on the switch and mirroring keeps the two in sync.
Question: for the client computers for forward request to the mirrored db, from application site do we have to change our config file informaiton, eg instance/server name
Answer: No, I’m sure he’ll talk about Transparent Client Redirect in a while.
Question: Is the Database Mirroring is same as Transaction Log shipping? (Sorry I was little late to join the Webcast)…
Answer: similar, but competely different. mirroring is completely in sqlservr.exe. lgo shipping in external to the process.
Question: Does the witness SQL Server require a SQL Server license?
Answer: Yes, but it could be the no-cost SQL Express version.
Question: Does the witness actually have SQL Server installed on it or is it just a process / service?
Answer: The witness is actually a SQL Server instance.
Question: What version of SQL needs to be installed on the witness servers? Is the Desktop version enough?
Answer: SQL Express is sufficient.
Question: Is not updated on MDAC then one must put both servers in connect string?
Answer: One of the possibilities is to specify both servers in the connect string.
Question: If in “automatic Failover” mode – is it like a 2PC? i.e. if mirror not available will transaction begin faiingl?
Answer: up until a threshhold. at some point, if the principal doesn’t hear back from the mirror, then it declares a failure and continues to serve the database
Question: Do you have any design principles regarding whether principle and mirror need to be on same service pack, hotfix etc or whether they can run on different SPs/hotfixes
Answer: we are going to do our best to allow you to run in a mixed mode.
Question: How do the clients know to change the server name to the new principal server. Is there code or service on the client?
Answer: he’s talking to this right now.
Question: can mirror servers be located in different lacations? what’s the limit in distance?
Answer: Yes the mirror server can be in a different location. The distance limit is dependent on latency and the network connection between the two locations.
Question: You guys are really ahmmering out some questions, so here is a Poker joke for you: I stayed up all night playing poker with Tarot cards. I got a full house and four people died. 🙂
Answer: 😐
Question: Can you configure the interval at which the principle sends log records to the mirror?
Answer: No. They are sent continuously.
Question: Does the witness provide notification in the case of failover?
Answer: no, one of the partners should notifiy if there is a failure.
Question: does wittness have to be a server?
Answer: yes
Question: On DB Mirroring, how is the server name resolved for DB Connection from the application
Answer: Client specifies server and database to connect to. You can also specify the mirror’s name. MDAC (actually the SQL portion) will try the first, then the second if the connection fails .. all behind the scenes.
Question: Can there be multiple witness servers?
Answer: no. but a witness server can be a witness to multiple sessions.
Question: Will the SNAC resubmit in flight transactions on failure to mirror or is app responsible for this?
Answer: No. The transaction should be re-submit by the app.
Question: what happens when principal server is totally lost if yu have manual failover.. how would you recover?
Answer: I’d stop the mirroring session, replace the server with a new machine, and then start up a new mirroring session.
Question: When using Mirroring, how does the client differenciate between SQL server failure at the principle vs. network issues, or other problems?
Answer: Client doesn
Question: I am not able install SQL 2005 due to unavaliblity of .NET Framework . Where i get latest .NET Framework
Answer: Check out: http://www.microsoft.com/downloads/details.aspx?familyid=a8f5654f-088e-40b2-bbdb-a83353618b38&displaylang=en
Question: Does primary wait for mirror to commit in FULL mode?
Answer: yes, the principal waits until the log is hardened on the mirror. but, if the principal doesn’t hear back after (by default) 10 seconds, the principal continues on.
Question: what is the MAX number of transaction per minute that mirroring can keep up (in automatic mode) – 8way, 8GB RAM
Answer: depends on load. mirroring works GREAT on read only data. 🙂
Question: Can the transactions sent to the mirror be modified? I’m thinking of building a DSS or some other altered-schema mirror db & using the technology to “replicate” only the data columns I want.
Answer: No, the mirror is an exact copy of the Principal.
Question: How would you differentiate mirroring with cluster,Log shipping and Replication?
Answer: carefully. no serious. consider cluster and mirroring for failover solutions. log shipping and repl for more custom solutions. there really is a lot to this which i cannot get into right now.
Question: Once the mirroring switch is turned on, do all clients need to break/reconnect in order to detect the name of the new mirror?
Answer: To have the redirection done automatically, yes. Alternatively you can specify the principal and the mirror in the connection string.
Question: What happens if the Witness server fails?
Answer: the partners continue on… the witness is not a single point of failure.
Question: how do you protect the witness server — is there a witness protection plan :)?
Answer: witness protection plan. i gotta use that. thanks. the witness is not a single point of failure. the partners continue on with out it.
Question: What informs you of a Witness failure?
Answer: the partner servers can be configured to alert you in case of a witness failure.
Question: Would you consider this a shared nothing archietcure?
Answer: yes.
Question: If you used MS OS Clustering could you install principle, mirror and witness as separate cluster groups on same physical server (wouldn’t implment this except in test 🙂
Answer: you can combine failover clustering with database mirroring .. or you can have standalone instances installed on your cluster that only do mirroring.
Question: incorrect – if the witness fails the primary is taken offline to prevent split brain
Answer: if there is no mirror, then the loss of the witness takes the database offline. mirroring will protect you against a single server failure, but not two.
Question: Do we need a separate SQL2005 license for witness server?
Answer: the witness can be express.
Question: Could an automatic failover occur if the witness server failed?
Answer: no, the witness is a requirement for automatic failover .. otherwise you could fall victim to the split-brain problem where both servers think they’re the principal
Question: Outside of a high performance app with no data loss requirement, what are some other reasons to use a cluster rather than mirroring?
Answer: instance failover vs. database failover. BULK_LOGGED mode. VIP for clients.
Question: Can a witness be a high end workstation rather than a seperate server ?
Answer: anything that can run express.
Question: can you mirror the witness db/server also?
Answer: there is no db on the witness.
Question: While SAFETY is set to OFF; will uncommitted transactions be rolled back once the Mirror server is made active?
Answer: yes, or even before (if you do a ROLLBACK) .. but if you failover to the mirror, anything that wasn’t committed will be rolled back.
Question: Is it true that database mirroring requires Enterprise Edition of SQL Server?
Answer: fully featured, yes. it is available in standard, but it is limited.
Question: how many instances can be on a witness server?
Answer: ???? as many as you want.
Question: what tsql DDL option indicates a principle, mirror, witness?
Answer: You just ALTER DATABASE and set up PRINCIPAL, MIRROR, and WITNESS.
Question: How is mirroring limited in Standard Edition?
Answer: safety FULL only. single threaded REDO on the mirror.
Question: Thanks
Answer: Glad to be of help.
Question: SNAC was mentioned for the clients failing over. Is there a location I can find more information about this?
Answer: there is a bit on client failover in the new BOL for DBAs and a place on MSDN for developers. sorry, don’t have it on the tip of my fingers.
Question: so you could have 20 (or more) sql mirrored environments, all handles by one witness machine.
Answer: yes. probably not the best solution, but yes.
Question: what can you do if you have automatic failover happen but the mirrored database locks up. When you check the status of the mirrored database it says that it can not talk to the partner. Now your whole database is locked?
Answer: ??? sounds like a bug. but, to serve the database with automatica failvoer enabled, the partner server must talk to at least one other server.
Question: sql2005 std, can do 2 node cluster, is that active active or active passive only?
Answer: The limit is a 2-node cluster .. you can have several instances installed and cluster them all, but only with 2 nodes per virtual server/instance.
Question: Are there any limits to differences between principle & mirror in terms of trace flags, configuration parameters etc.
Answer: in general no. it will be a best practice to have the partner servers be identical.
Question: is there any more information out on creating mirrored databases than the help files?
Answer: there should be a whitepaper on the CD for CTP June. BOL should be very good. I’ve been working on the documentation for two years. 🙂
Question: to clarify, active/active and active/passive are BAD reference… a database can only be accessed from a single instance at a given time.
Answer: correct. and we at Microsoft are to blame for introducing those terms. 🙁
Question: How do we facilitate fail-back with DB Mirroring?
Answer: Let the new mirror resync (which it will do automatically). Then ALTER DB SET PARTNER FAILOVER.
Question: Do you have to use Full Recovery mode or can you use Simple Recovery mode with database mirroring?
Answer: mirroring requires FULL only.
Question: Is there monitoring built into the mirroring and clustering that sends notifications if something happens. I just read the otherday that sql mail isn’t fully supported on a sql 2000 cluster.
Answer: mirroring is fully incorporated into the eventing mechanism in sql 2k5
Question: What happens if the query is run against the Sydney instance???
Answer: i think he showed that .. the mirror indicated that, since it was a mirror, it could not be connected to.
Question: How would you facilitate making this 100% transparent to the end user application?
Answer: have a middle tier cache and manage the connections to the database.
Question: Does the hardware for primary/mirror pair need to be exactly the same?
Answer: best practice, yes. required, no.
Question: can a mirrored database act as a reporting database? Example, updated every 15 min with static data?
Answer: yes, you would create database snapshots on the mirror.
Question: Where would be the best place to locate the Witness, with the primary or the mirrored server for setting up a hot site?
Answer: for auto failover, put the witness in a third location.
Question: Is SQL Server 2005 currently in Beta 2 or Beta 3?
Answer: Beta 2.
Question: While queries can not be run against the mirrored server/database, can backups be run agaist the mirror?
Answer: no. huge request. looking to make it in future releases.
Question: What will be the biggest mistake DBAs/devs will make with database mirroring?
Answer: 1. run in High Protection mode. 2. have a flackly network that causes auto failovers needlessly.
Question: Can you read a snapshot taken of the mirror?
Answer: yes.
Question: It seems that mirroring is a better solution. When should clustering be used instead of mirroring?
Answer: instance failover, bulk_logged, VIP for clients
Question: Will users from one system be mirrored to the other server?
Answer: no. we do not update any data outside of the database
Question: Does the witness keep a log of how many failures have occurred?
Answer: hmmmmm…. no. use the errorlogs on the partner servers.
Question: What is Part #10 Webcast focussing on?
Answer: Replication
Question: What do you mean under Feature? For Failover Clustering – No data loss And for Database Mirroring – No data loss option
Answer: if you run with SAFETY off, then there is a possibility of data loss on failover. but, the dba is the only one to initiate the failover.
Question: will a current sql2000 based app need any changes for 2005 mirrored set up for Auto FO?
Answer: in general no. you may have to change the connection string.
Question: Is there a good forum to get more information for mirroring?
Answer: the MVP newsgroups. i monitor them.
Question: How do you tell a SQL Agent job to (perhaps selectively) not execute on the mirror?
Answer: we don’t provide any help for you. it is just like maintaining a secondary on log shipping.
Question: Is there going to be SQL Server 2005 Beta 3 or it will be an RTM? Also, is the SQL Server 2005 Express mimic the regular SQL Server 2005 version? Meaning is it now in Beta 2, also?
Answer: there should be a CTP in June, two more CTPs, then RTM. of course, this could change.
Obs: O Webcast estará disponível na internet dentro de 24hs. Outro Webcasts sobre Alta Disponibilidade do SQL Server 2005 também pode ser visto no link https://msevents.microsoft.com/cui/WebCastRegistrationConfirmation.aspx?culture=en-US&RegistrationID=5774094&CountryCode=US