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


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


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

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


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

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


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

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


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


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

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

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


2

A SQL Server instance can host multiple mirroring partnerships. Mirroring is between databases and databases are hosted on SQL Server instances, so choosing a name like 'DBx' for servers is a rather poor choice. Lets call the instances SRV1, SRV2 and SRV3 and the databases DB1 and DB2. What is possible is: to have the database DB1 mirrored by SRV1 ...


2

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/


2

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


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

Database Mirroring Monitor (Status Page): Time to send log (estimated) Approximate amount of time the principal server instance requires to send the log that is currently in the send queue to the mirror server instance (the send rate). Because the rate of incoming transactions can vary significantly, the time to send log is an estimate. However, the ...


2

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


1

If you have access to a db version control system (ala Red Gate), you can have it create a deployment script fairly easily. If not, just create a file and script any ddl changes you make and store them in a file, then when you are ready (and have backed up prod) you can run the script against prod to make your changes. Be aware that certain changes can ...


1

You should also look at partitioning your data. Log shipping is another option for a reporting database. There are other factors to think about as well, such as how many rows are being retrieved, how big are the indexes? How often do you rebuild them? When you append to a table all the time the indexes get unbalanced and performance suffers. The other ...


1

At first I was expecting your error to be located at the point of retrieving SP_Z procedure. Sorry for being hasty. This link gives an explanation on how to query database mirroring status of your database. So, you do the following: DECLARE @MirroringRole int; SET @MirroringRole = (SELECT mirroring_role FROM sys.database_mirroring WHERE ...


1

Scheduling backups and restores via the SQL Agent isn't very hard. It is only one or two lines of TSQL. Just make sure that the backup finishes before the restore starts. You do not mention which version or edition of SQL Server you are using. Some versions/editions of SQL Server support Backup compression natively. (Here is the page that talks about the ...


1

Those errors are totally unrelated. If you are getting errors with database mirroring being able to connect to an endpoint you need to look into your mirroring configuration and figure out what went wrong because something is very wrong. As for tempdb thrashing that could be any number of things, but usually it's temp table usage, variable usage, or join ...



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