SQL 2005 Log Shipping

Last Post 30 Nov 2010 08:13 AM by Dshull01. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Dshull01
New Member
New Member

--
30 Nov 2010 04:43 AM
One of MS recommendations for creating a "Reporting Server" is to utilize Log Shipping to a secondary server. 

When this is initially set up the database on the secondary (reporting) server requires a restore.  This causes orphaned users - sids mismatch.  There are solutions to resolve the orphan issue but all require the "log shipped" database to be in normal status (not Standby/Read-only).  If this is done then there is no way to return the "Reporting" server database back to Standby|Read-only to allow log shipping to continue.

How do you allow users to connect to a Read-only Log Shipped database if you can't re-establish the user logins?
rm
New Member
New Member

--
30 Nov 2010 04:55 AM
You can copy sql logins with ssis copy logins task which copies sid also.
Dshull01
New Member
New Member

--
30 Nov 2010 06:04 AM
Thanks for the response but I already know how to re-establish the user logins.  I did not describe the total problem correctly.  I am trying to keep Log shipping established to have a continous updated "reporting" database with user accounts connected. 

If you re-establish the user logins then you "break" log shipping.  For log shipping to continue to function it apparently has to be in a read-only state, which is the "catch".  Once you re-establish the user logons then you break Log shipping.  I want to keep log shipping running (to have a near real time reporting database) and be able to report against it without having to keep re-connecting the user accounts - which requires a full restore to re-establish log shipping. 

In this scenario I would like to have log shipping occur every 15 minutes while allowing reporting users to still access the database.  I am obviously still missing something with continous log shipping...
gunneyk
New Member
New Member

--
30 Nov 2010 06:36 AM
If they are windows logins you can create them at any time and they should be fine. If they are sql logins they may or may not match up to the ID's in the other instance. You can restore a copy of the db, add the logins and I believe they will be ok for the next time. The problem with using log shipping and reporting is that you need to place the db in standby mode to allow read only access. Then to apply the next log backup you need to kick everyone out and this gets repeated each time you want to restore another log file. So there is no way with log shipping to do continuous or realtime reporting. You can look into DB Mirroring and report off a snapshot of the mirror but that has the same issues when you want to update the snapshot. Replication will allow for realtime access.
Dshull01
New Member
New Member

--
30 Nov 2010 07:20 AM
Sounds like log shipping does not function as it is recommended by MS for a reporting solution. 

We're using transactional replication for a web server solution and it is not recommended by our "vendor" to run multiple transactional replication sets.  Thanks for the info...

Still searching for a solution for "near real time" reporting - that would have little to no impact on production environment.
russellb
New Member
New Member

--
30 Nov 2010 07:53 AM

I have no idea what your vendor means by "multiple replication sets" but if you mean multiple subscriptions, that is rubbish.  You can easily create multiple publications and subscriptions with VERY LOW impact to the publisher.

I do reccomend a dedicated distributor however

Dshull01
New Member
New Member

--
30 Nov 2010 08:13 AM
Good info - Thanks again
You are not authorized to post a reply.

Acceptable Use Policy