SQL Server Log Shipping – Log Shipping Monitor Server Instance

backupdisaster recoverylog-shippingsql serversql-server-2012

We plan to incorporate Log Shipping for basic disaster recovery with reporting needs. Server A Source Database will log ship to Server B database. We will conduct log shipping every few hours.

Looking at setup, there is an option for "Use a Monitor Server Instance". Currently we do not have budget/time resources to setup third monitor server.

In that case, what is the second best recommendation for Monitor Server to store status/history of log shipping activities? On the source primary server or destination server ?

We are mildly concerned about performance/activity of Source Server. Sometimes CPU can get moderate high. So I think Destination Server is better option. Also, in case primary server fails, we have status/history of when last time transaction logs backup/shipped/restored. If monitor was on Primary server failed, we would not know this.

I am trying to research if there is any missing reason to place monitoring on Source Server.

Best Answer

If you are doing log shipping, I would highly recommend (and the best option I believe) you do have a separate monitoring instance. The monitoring instance could be in Express Edition so you don't have to worry about licensing. You just need to know that after you setup log shipping and you then in the future want to change the monitoring instance or add one, you may need to reconfigure your log shipping. The upside is that you can use the monitoring instance for multiple log shipping and have the view of all the primary/secondary databases status in one location. Once you completed the log shipping setup with monitoring instance, on the monitoring instance, right click on the instance -> Reports -> Standard Reports -> Transaction Log Shipping Status.

For reporting side of things, you need to disconnect the users/clients when doing a restore or wait until no one is connected to the database (delay restore). So plan for it. Another thing to be mindful of is that you can't add indexes or statistics on secondary database during standby mode. Doable but personally i would not use log shipping for reporting (only for DR purpose).

Personally, I would consider and recommend transactional replication for reporting. This gives you more control on your reporting database as you can create indexes, stats, create objects, etc on the subscriber database.

HTH

Related Solutions

Sql-server – What are the advantages of a monitor server for log shipping

You can monitor the process on the primary and secondaries, here's where a monitor comes in really useful though...

Let's say your log backups and copies are running just fine, no problems, but one of your secondaries goes down. You aren't monitoring the secondary and so are not aware of this. Your log shipping restore alerts are based off of the secondary server, so you are none the wiser (server being down then you are not getting restore alerts that things are out of date).

You notice a couple of days later, but by then it's too late and you have to restart log shipping from scratch.

Having a monitor server just helps to keep an eye on things and gives you a warning point not related to the servers involved in log shipping (think of it kind of like the witness server in mirroring, but without the automatic failover)

SQL Server – Creating Users on Secondary Server in Log Shipping

Two options:

On your log shipping primary server, find a user in that database with the permissions you want (or create one). If you create one, log shipping will then transfer that over (since that's a database-level object). Then, to find the SID of the user you want on your primary server: select sid from sys.database_principals dp where type = 'S' and name = '<<YourUserNameHere>>'. (SID is basically a unique ID - it's completely unreadable) Then, on the secondary server, create a login with that specified SID: CREATE LOGIN [<YourLoginNameHere>>] WITH PASSWORD ='<<YourPasswordHere>>', SID = <<the SID from primary server>>. By specifying the SID, that login will have the same SID as the user in your logshipped database - you should then be able to log into that user.
Create a login with server-level permissions (i.e. sysadmin) on your log shipping destination. This is a really bad idea for a multitude of reasons (especially if you have other databases on that server).

Worked example of creating a user: I have a database called LogShippingTest and I want to give a login on my secondary server db_datareader on the logshipped copy so that Marketing can read stuff.

On the primary server:

USE [LogShippingTest];
CREATE USER [DataReaderForMarketing] WITHOUT LOGIN;
ALTER ROLE [db_datareader] ADD MEMBER [DataReaderForMarketing];
SELECT sid FROM sys.database_principals dp WHERE type = 'S' AND name = 'DataReaderForMarketing'

Let's say the SID is 0x70DD0D59ADF3FD4ABB000037826A2888 (I just made that up but we'll go from there). Now, either force log shipping to run, or wait until it runs itself and the user exists on the secondary side. Then, on the secondary:

USE [master];
CREATE LOGIN [DataReaderForMarketing] WITH 
   PASSWORD = 'ThisIsAVerySecurePassword', 
   SID = 0x70DD0D59ADF3FD4ABB000037826A2888

You should then be able to log in as the DataReaderForMarketing login on the secondary server and have db_datareader on the logshipped secondary database. (You can change the permissions on the primary database user and they'll logship down to apply to the secondary, too!)

Best Answer

Related Solutions

Sql-server – What are the advantages of a monitor server for log shipping

SQL Server – Creating Users on Secondary Server in Log Shipping

Related Question