I recommend that you have the jobs enabled and scheduled on both servers and with the same configurations. Databases that are in a mirror role will automatically be skipped. There are checks for that in the script.
Straight backup and restore is obviously out. I also wouldn't consider replication of any kind.
Database mirroring is relatively simple to set up, but requires real-time connectivity between the two servers, setting up of partners and endpoints, etc. Availability Groups could be an option, but on top of the networking complications you also have to have ...
@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:
Connection management in Microsoft SQL Server 2005 and later versions is based on endpoints. An endpoint is a SQL Server object that enables ...
I recently migrated 15tb across 6 databases using mirroring. Very simple and worked perfectly with just a couple seconds of failover time.
I had two new virtualized SQL Servers. The databases were coming from 3 servers that they had just plain outgrown, and were impacting performance on the smaller databases hosted on them.
The process was very ...
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 ...
Try some basic connectivity tests.
Verify that 5022, 5023 and 5024 are listening.
Verify that the server name you are using is correct.
From the command line :
On my server, you can see that 5022 is listening.
Next make sure that you can connect to those ports via telnet
telnet fully-qualified-server-name 5022
As it mentions in the Note ...
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 differential ...
If you want to ensure you minimize the amount of time you are without HA during your migration, here's a trick I recently used:
Set up the AG with a "dummy" database so that your AG is ready in advance.
Log ship the database(s) to both your primary & secondary AG replicas--at this point, the log shipped database is not part of the AG, you're just doing ...
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 ...
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, ...
For now just run it on the Primary database server.
When you do a failover, you can run it on the mirrored server.
e.g when you deploy it on mirrored server, you (refer to: http://ola.hallengren.com/sql-server-backup.html)
@Databases = 'SYSTEM_DATABASES', -- when failover, change it to ALL
@Directory = 'C:\Backup',
@BackupType = ...
Sometimes restarting the mirroring endpoint on the principal server will resolve this.
To do this use the following T-SQL.
ALTER ENDPOINT Endpoint_Name STATE = STOPPED
ALTER ENDPOINT Endpoint_Name STATE = STARTED
To get the name of the endpoint.
SELECT * FROM sys.endpoints
WHERE type = 4
See KB 2490051 for more details.
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;
Create the same database from a backup with NORECOVERY:
restore database MirroredDb
from disk = '\\backupdir\MirroredDb.bak'
Setup your mirroring ...
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 ...
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 ...
I think that mirroring is not the the best solution in this scenario, and discontinuation announcement is not even the main reason, especially if you're planning to maintain the test environment only for limited amount of time.
Mirroring is a high-availability solution that was designed to provide hot/warm standby db in case of failure, or offload ...
For automatic failover, you need a tie-breaker.
Otherwise, what would happen when the two servers had a network split and couldn't see each other? You wouldn't want them both automatically promoting themselves to primary, and both accepting writes. Think about what would happen on a table with an identity field, for example: both servers could quickly end ...
Express Edition doesn't include database mirroring, but you can do something similar with log shipping. Log shipping is a technique, not a feature - it's just a matter of frequently taking backups on your primary server, and then restoring them on the secondary.
The normal way of doing this requires SQL Server Agent to run the backup & restore jobs - ...
Well, its took months but i finally found the problem!
The sys.dm_os_performance_counters wasnt being populated at all on the primary server.
Turns out that the counters where not installed, i was able to do this manually as follows:
C:\Windows\system32>lodctr "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSS
Based on what you have described Log shipping is going to be the way to go. Log shipping is old school, tried and true.
Replication is pretty fragile, provides massive amounts of room for failure especially when it comes to adding new tables and getting those tables into the replication topology.
As for 2012 upgrade costs they may not be as bad as you ...
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 ...
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 recreate ...
For a weak network I wouldn't personally use Mirroring. But if you really want mirroring for the specific case when there might be a slow connection an option would be to use the Asynchronous mode (or High Performance mode). This would imply some data loss would be possible, but you will be able to finish transactions faster.
Details on MSDN: Async ...
As noted, mirroring is not dead. (At least not yet.) The general population doubts that Microsoft would totally abandon HA for Standard Edition.
However, there are other methods. In the SQL Server 2012 documentation when referring to mirroring it says: "If your edition of SQL Server does not support AlwaysOn Availability Groups, use log shipping."
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 ...
A SQL Server 2008 R2 mirror is not itself readable.
If you are using Enterprise Edition, you can create a named snapshot database from the mirror. This allows you to create a point-in-time readable database that can be used for pretty much any query that does not do any updates. (Though you could use another database (tempdb or a workdb) to do auxiliary ...