Solved

SQL Log Shipping Question

Posted on 2015-02-23
11
97 Views
Last Modified: 2015-03-02
Hi Experts,
I have a question about SQL Server Log Shipping:
I have set up a log shipping between two databases on two SQL Server instances (SQL 2008 R2).
It has been working with no issues  - every transaction is being replicated from the primary to the secondary instance in terms of minutes - all log shipping jobs are running successfully and on time.

Then I decided to test this with DDL, namely creating a new table in the primary instance.

I was surprised to see that 3 days later the table is still not showing up in the secondary database!
Log shipping is running and jobs are running as scheduled with no issues.

I was under the impression that log shipping creates an exact copy of the primary on the secondary and every transaction, DML OR DDL (created objects) will also be replicated.

Why then I do not see the newly created table from the primary to be replicated to the secondary?

What am I missing? Is there an option in LS to tell the process to also replicate objects, schema changes etc?

Please help with information or specific sources of information on this mystery :-)

Thanks,
RICUser

P.S. Please refrain from questions like "Why are you using Log Shipping when...." and "Why don't you use this and that instead...". I am aware of the alternatives of database replication, in fact I have tested at least 9 other alternatives (SQL Server Replication, Backup/ Restore, CDC, Database Mirroring, Custom ETL etc). I am using Log Shipping because I have a reason to use it and cannot use any other method in this specific case>
0
Comment
Question by:RICuser
  • 6
  • 4
11 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40627920
I was surprised to see that 3 days later the table is still not showing up in the secondary database!
That's weird. The table still exists in the primary database?
Also, others data modifications has been applied to the secondary database?
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 40628188
I am guessing that you are using ReadOnly/Standby mode if you are looking at the log shipped data.

There is really no magic in Log Shipping because it is the same as backup / restore so there is no possibility that there are transactions that did not make it unless the transaction was rolled back.  If it is in the primary, then it was committed and should show up in the secondary.

If you can confirm that you are using Standby mode then you could right click on the database in Management Studio and choose Properties and look at the last Log Backup date.  You should see a recent date or in this case, a 3 day ago date if the data is not there.

Let me know, I am veteran with Log Shipping and I am happy to take you through all this to see what could be wrong.
0
 

Author Comment

by:RICuser
ID: 40628968
Thank you Ben, yes the secondary is in stand by\read only mode.
I have attached some screenshots of the properties you've asked for.
Also, here are the versions of the two instances (not sure whether this makes any difference):

PRIMARY:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03

SECONDARY:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1734.0 (X64)   Aug 11 2010 12:51:02


Thank you!
RIC User
C--Primary-and-Secondary---PROPERTIES.jp
C--Primary-and-Secondary-DBs.jpg
C--Log-Shipping-Jobs-Running.jpg
0
 

Author Comment

by:RICuser
ID: 40628986
@Vitor:
Yes, Vitor The table is still in the primary but not in the secondary database.
I wonder what will happen if I run some UPDATEs on that table in teh primary (since all other transactions are being replicated...)
I am going to try this now and will post  results.

Thanks,
RICUser
0
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
ID: 40629042
OK, it looks like you either ran the restore on the secondary with a different name or there have been no restores on the other side.

Try this query on the secondary:
select top 100 *
from msdb.dbo.restorehistory
where restore_type = 'L' and destination_database_name = 'YOUR DATABASE NAME HERE'
order by restore_date desc

Open in new window


See if there are any results.  Also you should not have version mismatches especially on the secondary side.  You have a lower version of SQL (service pack RTM) than the primary (SP1) and that is not a favorable situation in the restore process.

Let me know what you find.  It appears to me from what you have posted that there have been no log restores.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:RICuser
ID: 40629336
Ben, the query did not return any results. That tells me that there were no successful restores for some time.
I checked the LS Monitor job and the error confirmed that - see attached.
Where can I find more information as to why the restores are failing and are there any tools (like say, Replication Monitor for SQL replication) that would help monitor the Log Shipping process?


Thanks,
RICUser
C--Log-Ship-Monitor-Job.jpg
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 40629356
The restore information will be in the Event Viewer on the server and should be in the ERROR log for SQL Server.

Let me know.
0
 

Author Comment

by:RICuser
ID: 40629419
...cont from previous comment...
And still the restore job on the secondary  shows a successful restore.
Isn't that misleading? (see attached)
C--Restore-Job-Reporting-Success.jpg
0
 

Author Comment

by:RICuser
ID: 40629436
Tge SQL Server log is flooded with these messages starting from th 17th and still going on:

Date            2/17/2015 1:34:00 PM
Log            SQL Server (Current - 2/22/2015 3:14:00 AM)

Source            spid295

Message
The log shipping secondary database <database_name> has restore threshold of 45 minutes and is out of sync. No restore was performed for 47 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 40629466
Yes, it is misleading as it says that 0 files were restored.

This appears that Log Shipping is not set up correct. What does the dialog look like in the primary database Transaction Log Shipping tab in the Properties?
0
 

Author Closing Comment

by:RICuser
ID: 40640064
The query provided by Ben Miller pointed out an issue with the restores - I found no successful restores to the secondary database.
I resolved this by reconfiguring Log Shipping from scratch and now all works as intnded.
The only difference from my first time setup was that then I used an already generated backup for the secondary initialization.
This time around I let SQL Server to create its first backup  - an option as part of the Log Shipping setup.
With its own initial backup\restore now the secondary is being updated with new transactions and also with DDL as well.
Thanks Ben!

RICUser
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SLQ View not updating 10 47
Group by and order by clause 28 36
SQL Server memory Issue 7 76
SQL Transaction logs 8 11
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now