Solved

SQL 2005 to -> SQL 2008 database replication

Posted on 2013-06-06
18
434 Views
Last Modified: 2013-06-08
Hello

We are new to database replication, and we have been unable to find the answer to our questions by just using Google.

We are replicating a SQL 2005 database to SQL 2008, in order to have a disaster recovery area.

However, when we setup the SQL 2005 -> SQL 2008 replication, it appears to not be working correctly. Here's the situation:

We have successfully synced up the databases between the two. However, when we look up the size of the SQL 2008 replicated database, it's only 4 MB in size! And the original database (publisher) is actually 20 MB in size.

Should the replication sync the entire database (and transaction logs with it), to match exactly the database that is being replicated? (As it is now, we can't failover to this replicated database because it's now a full databases.)

Obviously we are missing something here.

What should we do to make both databases exactly alike, so that we can failover to the replicated one should we need to?

Thank you!

Rich F.
0
Comment
Question by:TECHSANTA
  • 7
  • 5
  • 3
  • +2
18 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
When you say database replication do you mean replication of all tables or mirroring ?
0
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 500 total points
Comment Utility
How are you checking the DB Size?

It could be the initial size was set differently.  Also what about  the log size files?  Just throwing some things out to check.
0
 
LVL 9

Expert Comment

by:MattSQL
Comment Utility
Replication is not really an efficient disaster recovery solution. Your situation sounds like I would look at either mirroring or log-shipping.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Replication is not really an efficient disaster recovery solution.
Absolutely.  Replication was never intended for DR.  For one how do you propose to fail over to a subscriber?
0
 

Author Comment

by:TECHSANTA
Comment Utility
<<Replication was never intended for DR.  For one how do you propose to fail over to a subscriber?>>

It's surprisingly easy. Once the main database server goes down, just switch the IP/hostname of the subscriber to be the same as the server that went down, (and turn of the subscription), and all the clients work. (We've already tested it.)

Replication, to us, is THE way for an effective DR. We do the same with our Oracle databases.
0
 

Author Closing Comment

by:TECHSANTA
Comment Utility
I checked the physical DB size and they were the same. For some reason SQL 2005 was reporting the DB size as 2 MB! (I guess that was the original size?)

Anyway, your answer made me look at the size on disk of the DB and they were the same. So I guess the replication of the databases are exact!
0
 
LVL 9

Expert Comment

by:MattSQL
Comment Utility
Despite this question being closed I think you should be very wary of using replication for DR:

1. There is nothing preventing changes being made to the secondary database.
2. Schema changes are not replicated. This could account for different size reporting.

I never guess when it comes to DR :)
0
 

Author Comment

by:TECHSANTA
Comment Utility
MattSQL,

<<2. Schema changes are not replicated. This could account for different size reporting.>>

Schema changes are indeed replicated, according to this:

http://msdn.microsoft.com/en-us/library/ms147331(v=sql.90).aspx

<< There is nothing preventing changes being made to the secondary database.>>

Yes, we don't allow anyone to connect to this DR databases. So, no changes are being made to this secondary databases.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Replication, to us, is THE way for an effective DR.
You are joking right?  If you are serious about DR, these are the options you should consider:
1.Clustering
2. Database Mirroring
3. Log Shipping
4. AlwaysOn Availability Groups
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:TECHSANTA
Comment Utility
<<You are joking right?  If you are serious about DR, these are the options you should consider:
1.Clustering
2. Database Mirroring
3. Log Shipping
4. AlwaysOn Availability Groups >>

Oh yes, very serious! Why would anyone do database mirroring (nearly the same as replication) and log shipping when replication works so well?

Clustering is already being done. It's a no-brainer.

AlwayOn Groups is only for SQL 2012. Remember, I'm on SQL 2005 and 2008.

But thanks anyway. I respect that you want to do it one way, but our way obviously works extremely well.

Best

Rich
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
Oh yes, very serious! Why would anyone do database mirroring (nearly the same as replication) and log shipping when replication works so well?
Database mirroring does not have the same purpose than replication, neither it has its Pros/Cons.  Among other things:
> Mirroring is database level, replication is object level.  If you have thousands of tables in your schema, replication can tricky to setup and maintain
> Mirroring allows automatic failover, replication requires manual intervention.
> Mirroring is DDL and DML level, replication is only DML level.  Schema changes have to manually synchronized between replication source and destination.
> If you think mirroring is tricky, try merge replication.

Clustering is already being done. It's a no-brainer.
Just so that you are aware, according to MS, the terms clustering applies only to AG, MSCS.  If you talk about clustering to an MS consultant or to most people who work with SQL, if you mention replication as a clustering technology, they won't understand you.

But thanks anyway. I respect that you want to do it one way, but our way obviously works extremely well.
I guess this is what matters.  I am curious what is your RTO.  Have you estimated how much time it would take you, including human process to bring your service up in case you lose a server ?

Hope this helps.
0
 

Author Comment

by:TECHSANTA
Comment Utility
I'm afraid you just can't accept a good solution, when that solution isn't your idea.

You act like mirroring is difficult. Trust me, it's very easy. Done that, been there. Not applicable for our needs. Try to get over it.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Try to get over it.
Actually you are missing the point.  You have (rightfully or wrongly) reached a solution that you feel works for you in your case and that is fine.  The point you are missing is that this site is about sharing knowledge and in particular this thread is no longer about you or your case.  This is about the many readers in future when reading this thread,  if we do not state the facts accurately and clearly they will be mislead.  So when we ask you something like "what is your RTO" an answer to that question would be more helpful to future readers to make your case than the rather infantile "Try to get over it.", don't you think?
0
 

Author Comment

by:TECHSANTA
Comment Utility
<< if we do not state the facts accurately>>

That's why I spoke up with using replication for DR. It's the absolutely perfect solution IF you're tech savvy enough to know how. (It really sounds to me like you don't' know how to properly use replication for DR. You may now how to use replication, but you don't appear to know how to properly set it up for DR.)

<<more helpful to future readers to make your case than the rather infantile "Try to get over it.", don't you think? >>

Which of course is your way of trying to convince everyone, and yourself, that you are correct, and we are wrong. You are trying to save face. It's disappointing because you're supposed to be helping folks. Instead, you're just another tech who can't believe someone disagrees with them. I'll say it again: please try to get over it.

All right, so now you may reply with your "last word", because it makes you feel better about things. And I will leave it there with your response. Deal? Okay then...

Good luck out there!

Rich
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
All right, so now you may reply with your "last word"
I am afraid not.  Sorry to disappoint you.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
I'm afraid you just can't accept a good solution, when that solution isn't your idea.

Try to get over it.

Which of course is your way of trying to convince everyone, and yourself, that you are correct, and we are wrong.

You are trying to save face.

It's disappointing because you're supposed to be helping folks.

Since you seem to have it all figured out regarding DR, I just have to wish everything goes as you expect.  Good luck.
0
 

Author Comment

by:TECHSANTA
Comment Utility
<<Since you seem to have it all figured out regarding DR, I just have to wish everything goes as you expect.  Good luck. >.

LOL. It's already done. But nice try. :)

I wish you extremely sensitive tech's would just accept it when someone closes a question and awards points to a person that isn't you. It's NOT PERSONAL.
Geez.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I wish you extremely sensitive tech's would just accept it when someone closes a question and awards points to a person that isn't you. It's NOT PERSONAL.
Of course it is not.  Why ever would you think that?  I have earned Premium Services for life, why in the world would I care about points?

This is about this site and sharing good solutions.  Future readers are going to read this and they may want to know if Replication, contrary to standard practices is in fact a valid tool for DR.  "Try to get over it" and "LOL. It's already done" are not exactly good arguments in favor of its use, don't you think?  Yes, I do realize you may not be very interested in what future readers have to say about your comments, then say so and they can judge for themselves.
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

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

9 Experts available now in Live!

Get 1:1 Help Now