Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

SQL 2005 to -> SQL 2008 database replication

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
TECHSANTA
Asked:
TECHSANTA
  • 7
  • 5
  • 3
  • +2
1 Solution
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
When you say database replication do you mean replication of all tables or mirroring ?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Matt BowlerDB team leadCommented:
Replication is not really an efficient disaster recovery solution. Your situation sounds like I would look at either mirroring or log-shipping.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Anthony PerkinsCommented:
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
 
TECHSANTAAuthor Commented:
<<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
 
TECHSANTAAuthor Commented:
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
 
Matt BowlerDB team leadCommented:
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
 
TECHSANTAAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
TECHSANTAAuthor Commented:
<<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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
TECHSANTAAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
TECHSANTAAuthor Commented:
<< 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
 
Anthony PerkinsCommented:
All right, so now you may reply with your "last word"
I am afraid not.  Sorry to disappoint you.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
TECHSANTAAuthor Commented:
<<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
 
Anthony PerkinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 7
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now