Hot answers tagged

22

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.


19

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


12

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


12

Step 1: Create a service to receive the notifications and a queue for it: use msdb; go create queue dbm_notifications_queue; create service dbm_notification_service on queue dbm_notifications_queue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]); go create event notification dbm_notifications on server for ...


12

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


11

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 : netstat -an 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 ...


11

It sounds as if you simply want to pause and resume your mirror. Have you seen this link yet? http://msdn.microsoft.com/en-us/library/ms190664.aspx The gotcha with this would be the length of time you expect to have the mirror paused. If you take too long then the principal server could experience a large amount of transaction log growth. if you do not ...


9

I had to buy "Pro SQL Server 2008 Mirroring" after reading chapter 6, I found out the steps to do this are: check if service broker is enabled SELECT CASE is_broker_enabled WHEN 1 Then 'Enabled' ELSE 'Disabled' END FROM sys.databases WHERE name = 'DataBaseName' if not, run ALTER DATABASE DataBaseName set ENABLE_BROKER; create the stored procedure we ...


9

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


8

I recently migrated 15tb across 6 databases using mirroring. Very simple and worked perfectly with just a couple seconds of failover time. Edits: 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 ...


8

Mirroring is a technology that increases the high availability for user databases and creates a copy of the source db that is maintained in real time. This is done by delivering all the active transaction log records to the mirror server. From MSDN: "Database mirroring is a primarily software solution for increasing database availability. Mirroring is ...


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

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


7

Mirroring tempdb, that's a no. Have you checked Step-by-step SQL Mirroring the ASPState database? If the -sstype:p parameter is not specified, then by default, the ASP.NET sessions will be put into the TempDB database and not the ASPState database. This confused me for a while. This is fine for a normal non-mirrored environment because the ASP ...


7

For SQL 2008 you can run either Enterprise or Standard for mirroring: http://msdn.microsoft.com/en-us/library/ms188712.aspx The witness server itself can be Standard, Enterprise, Workgroup, or Express. The witness server is needed for automatic failover: http://technet.microsoft.com/en-us/library/ms175191.aspx


7

Found a website with a comment on it. I added the code to just after where I restore the key and cert --Mumbojumbo to get mirroring to work OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password' ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY GO It works like a charm, it makes a little sense that I had to encrypt the master key that I restored with ...


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


7

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) EXECUTE dbo.DatabaseBackup @Databases = 'SYSTEM_DATABASES', -- when failover, change it to ALL @Directory = 'C:\Backup', @BackupType = ...


6

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


6

I would go with mirroring with a witness, do you want to do the work of a manual fail-over at 3AM when "socks hit the fan"? Check out Paul Randal's MCM prep videos, they might help you out in your decision.


6

After days of searching, I found the answer 10 minutes after this post. The problem was that the admin account did not have read/write rights on c:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ . After changing that I also had to recreate the master keys and certificates to make it work.


6

Sadly mirroring of the distributor isn't supported. I'm not sure if it would work as the server names wouldn't be correct when the agents would log in, not to mention you'd have to manually setup all the agents on the backup box. Currently your only HA option for the distributor is Windows Clustering.


6

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


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

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


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

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


5

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


5

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


5

With an async mirror there should be little to no performance impact on the active system. When you are in full recovery there is more transaction log activity on the system than if the database was in simple recovery mode, but that's about it. Are you seeing specific waits or something that prompted the question?



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