Tag Info

Hot answers tagged

18

@mrdenny's answer is accurate that failing over one database will not result in all the other databases failing over as well. However just to give more overview of what a database mirroring Endpoint is: From BOL, Connection management in Microsoft SQL Server 2005 and later versions is based on endpoints. An endpoint is a SQL Server object that enables ...


11

There's a few questions you need to ask first. Are you using SQL Server Enterprise Edition? Mirroring can be done with either Standard or Enterprise, but snapshots can only be done with Enterprise. Are you already paying for licensing on the mirror? There's a lot of fine print, but you basically get the mirror for free - only as long as you're not ...


7

Database mirroring questions from TechEd: failovers and partner timeouts ... when the cluster is failing over and the principal instance is unavailable for 60-90 seconds, the other instances in the mirroring session will time-out after ten seconds and initiate a mirroring failover... The solution to the problem is to change the partner time-out ...


6

No it will not. The databases are failed over independently from each other. There's no harm is failing over the test database, the production database will stay where it is.


5

We are doing log backups on the principal database, and my question is what are the gotchas with doing a log backup on a mirror? You can't do a log backup on the mirror database. At least one full database backup from the mirror has to be completed before doing a log backup, in that case are there special options that need to be used because it is a ...


5

The DBCC SHRINKFILE command will be mirrored from the principal to the mirrored database. Here's some proof. Create a sample database on the principal: create database MirroredDb; go Create the same database from a backup with NORECOVERY: restore database MirroredDb from disk = '\\backupdir\MirroredDb.bak' with norecovery; go Setup your mirroring ...


4

Yes, that is a good and popular approach to offloading reporting, especially since you already have database mirroring in place. Please see this BOL reference for more specifics: http://msdn.microsoft.com/en-us/library/ms175511.aspx As for when you should create the snapshots, that is completely up to you and your business requirements, depending on how ...


4

This is happening at parse time, and there is little you can do to change the actual behavior. SQL Server does some checking of the statements within the batch, and for all intents and purposes, pretends that your conditional logic does not exist. For the same reasons, you can't use conditional logic to determine which of two possible #temp tables to create ...


4

I've just read your entire post and it matches exactly with the symptoms we are seeing in our smaller environment. The only difference is that we are running SQL 2008 R2 on Windows 2008 R2 Standard. We have no errors on any of the switches and no errors from the network stack suggesting that the reason for the timeout is valid. The only common theme here is ...


4

It sounds like you might be running out of TCP ports on the SQL Server. How many connections are you seeing to the server at a time? Timeouts like that would definitely be causing the problem.


4

One way to prevent the mirror failover is: Pause Mirroring with ALTER DATABASE XYZ SET PARTNER SUSPEND Move the SQL instance Resume mirroring with ALTER DATABASE XYZ SET PARTNER RESUME The instance is failing over to the mirror because both the witness and the secondary can no longer see the primary instance. It sounds like you are attempting to ...


4

I don't trust mirroring 100%, but I do trust it about 99%, same with everything else. It never hurts to periodically check your mirror database and DR scheme. We test our DR plan across the board several times a year, and it would not hurt your scenario to set aside a DR test time and simulate a failure of your primary database to fully test the mirror if ...


4

I found the problem. SQL Native Client and the .NET SQL Data Provider should seamlessly connect to a failover partner even if the primary is down; however, the default configuration of both providers is to try both TCP/IP and Named Pipes. This configuration is fine, and works appropriately, when the servers are still both available but the primary has ...


4

I appreciate that you are looking for anecdotes about the experience of using mirroring; however this site is not really the place for opinion. Having said that, you may find the following excerpt from Microsoft.com regarding database mirroring helpful: Database mirroring is a simple strategy that offers the following benefits: Increases availability ...


3

The 180 day evaluation version from Microsoft is Enterprise Edition. AFAIK there is no ability to "trial" Standard edition from Microsoft outside of a licensing agreement you might have. From the point of view of overhead between editions, there's negligible difference. If you want something longer than 180 days, get the developer edition as its feature ...


3

The sys.dm_exec_query_plan has a column returned for dbid, you could just filter on that for the database that is being mirrored. Or, you could get fancy and nest a sub-query that filters for any dbid that is in a mirrored state by going out to the sys.database_mirroring DMV.


3

Define the data is getting to be a bit much? I've got clients with 1B+ rows in a table and no performance problems at all. You could use SQL Server replication to replicate inserts and updates to a remote machine, but basically have it ignore the deletes. Before I'd start looking at hacking together something like that I'd probably look at index tuning or ...


3

No witness server has to be SQL 2012, you can use SQL Express 2012 though All server participating in database mirroring should be running same version of SQL server. Taken from Microsoft SQL Server BOL Except during an upgrade from an earlier version of SQL Server, the server instances in a mirroring session must all be running the same version of ...


3

After spending a portion of the day dealing with moving mirrored databases from server to server in our DEV environment in preparation for our move to SQL Server 2012, I came across this MSDN document that explains that ENDPOINT security is determined solely based on the type of account SQL Server is running under. If SQL Server is using a domain account, ...


3

I just happen to have set this up for a database in production and was wondering the same thing cause I needed to compare the configuration to another mirroring session to make sure I matched it correctly. sys.dm_db_mirroring_connections should provide you with you want. It contains the principal_name which is: Name of the login that was validated for ...


3

The partners must be the same edition and the same version. Please see BOL's reference on Database Mirroring Overview For a new mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server. Since SQL Server 2005 Service Pack 1 (SP1), database mirroring partners and witnesses have ...


3

Using mirroring alone won't be sufficient since the mirrored secondary is not available for querying. You have to create and maintain snapshots, which can be annoying. Your options are, in no particular order: Mirroring with snapshots : effective but has management overhead. Backup and restore : Are you restoring from full backups rather than ...


3

Sorry for the previous useless response - skipped over the fact that you are setting up a mirror without a domain. You will need to use certificates for this. I found an article that might help you with that part. Check it out: http://mssqlnet.wordpress.com/2012/06/20/microsoft-sql-server-database-mirroring-in-a-workgroup-without-domain-with-certificate/


3

Suggest you to PAUSE mirroring and then patch the windows. Ideally, I would follow below approach : If you have a witness configured, turned it OFF as during the patching, if sql server service is restarted or the server reboots, then a witness will initiate a failover. ALTER DATABASE [db_name] SET WITNESS OFF Always patch the current mirrored server, ...


3

As per this BOL reference, here are the only options you would have with Standard edition: Log Shipping Database Mirroring (sync/safety only) 2-node Failover Cluster Instance Because you don't want to use shared storage between different participants, that would automatically rule out the failover cluster instance, leaving you to choose between log ...


2

http://colleenmorrow.com/2011/03/14/database-snapshots-part-2-offload-reporting/ may prove to be helpful for using synonyms to report on snapshots without disruption when generating new snapshots.


2

Can you check you sys.dm_os_schedulers ? Specifically, does work_queue_count deviates from 0 for any significant time? This would indicate worker starvation and would explain many of your symptoms.


2

See the Books Online documentation for sys.database_mirroring_endpoints For server object permissions you look at the usual place, sys.server_permissions. As with every case when Windows security is involved things are more complicated due to windows groups membership, and you also have to consider the permissions hierarchy.


2

This is happening because the server is responding but the mirroring endpoint is down. The partner and witness get a response from the OS that the port that the endpoint is on is closed and immediately starts failover. The timeout applies to when no response is received from the host as opposed to, "the port you're looking for isn't listening anymore," ...


2

The problem is that the principal became disconnected from the witness server before it went offline. In this case, the witness cannot agree that the principal is offline because it was disconnected while the principal was still connected. As far as the witness knows, the principal may still be up. In this scenario, it would require that you force service ...



Only top voted, non community-wiki answers of a minimum length are eligible