Tag Info

Hot answers tagged

20

@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 ...


8

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.


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 ...


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 ...


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

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'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

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

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

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 ...


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 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 ...


4

The main disadvantage of high-performance mode is going to be your possibilities of failover. Because the communication between the principal and mirror server is now asynchronous with high-performance mode, the only way to failover is with a forced failover and possible data loss. If you had to do a forced failover to the mirror server, any transactions ...


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

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

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

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

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

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

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 ...


3

Firstly I should say great question usr. Sean, Robert and Max have both correctly stated that due to the re-negotiation between the Primary and Secondary, that the correct LSN is always resolved so that there is not possibility of any discrepancy in that scenario. In response to the discussion with Shanky, in Synchronous mode, the Commit first has to occur ...


3

As noted it is based on the mode mirroring is configured to, but overall the concept of mirroring you are probably closer with statement one. However, as noted in the documentation for mirroring: Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as ...


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.



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