Tag Info

Hot answers tagged

20

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.


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


10

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


8

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


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

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


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

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

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


6

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


6

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


5

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.


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


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


4

Recovering is the correct state for the database to be in at the stage you've got to. First, script the endpoints on both the mirror and principle and check that the ports are those you expect and that you can telnet to both ports from both servers. Double check that the endpoint state is 'Started'. Second, if the endpoints are as expected and you've ...


4

Mirroring doesn't work like that. You can't bring multiple databases into a single database. You need to look to Replication, specifically Merge Replication as a solution for this. It can be set up across domains and it's specifically designed to take multiple different databases and bring them back to a single location. Mirroring is for creating a copy, a ...


4

For a cluster failover you can use a startup procedure. Assuming you have xp_cmdshell enabled and the service account has adequate permissions to start services: USE [master]; GO CREATE PROCEDURE dbo.StartService AS BEGIN SET NOCOUNT ON; EXEC master..xp_cmdshell 'net start servicename'; END GO EXEC [master].dbo.sp_procoption 'dbo.StartService', ...


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

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

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


4

I would implement Log Shipping, which is just a process of taking transaction log backups at one site, and restoring them at another site. This operates at the database level, and it will inherently handle all the schema changes because backups operate at a level above that. There's really no other sane solution on Standard Edition because the requirement ...


3

From MSDN - Database Mirroring Overview: "You cannot mirror the master, msdb, tempdb, or model databases."


3

There are many ways to accomplish this, and each has its advantages and disadvantages. Before you sync anything you should talk to your DBA first, as there could be policies in place at your company that disallow having data be placed on portable devices such as a laptop.


3

"Witness only" refers to the role that Express edition can play in a mirroring setup between higher editions. It can not be a principal or mirror. There is no "Witness only" mirroring setup


3

Situations that come to mind are: Having to Restore the Primary due to a bad code release / user error Loss of Network Connectivity for extended periods of time or due to maintenance activities DNS Issues / Misconfiguration of Aliases in SQL Server Configuration Manager Microsoft also has quite a bit of information in an article appropriately named ...


3

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



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