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

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

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

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

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


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


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

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.


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


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

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

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

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

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

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

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.


6

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


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


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


5

Joe Sack's answer on ServerFault is as good as it gets: http://serverfault.com/questions/251551/sql-server-2008-mirroring-threshold-alerts-at-database-level/251989#251989 For some background, Joe runs the Microsoft Certified Master of SQL Server program. He knows his stuff. (I can vouch for him personally.)


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?


5

SQL Server Replication can ensure that your data is synchronized. SQLRockstar is correct in saying that you need to determine if policy will allow the data to be stored on your laptop. Questions to think about before moving ahead with replication: How often is your laptop on the same network as your main server? Do data modifications need to be two-way? ...


5

After all that one of the servers didn't have the SQL Server service running under the same user. I'm an idiot.


5

The article linked uses the dreaded WMI middle-man for delivering the notifications. That is so error prone that is not worth talking about. Cut out the WMI middle man (that also looses events...) and go straight to the source: create an event notification for the Database Mirroring State Change Event. Deliver the notifications locally to your [msdb] ...



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