SQL Replication, what's required , best advice & practice 1

[1DMF]

Hi,

We are looking at using SQL replication but I would appreciate some input and advice.

1stly can it be done with SQL 2000? i went to this URL to read up on it

http://msdn.microsoft.com/en-us/library/bb500346.aspx

But i cannot find anything on replication in SQL 2000 only 2005/8

Also do both servers (as I'm looking to replicate server 2 server) have to run the same versions of SQL?

finanly is there any resources you could recommend for setting up replication and best practices?

Do you only replicate the tables you need available at both locations, how do you keep them in sync with each other if some data is changed on one server and some data on the other, how does it mary up the changes?

what if one server has a record deleted , while the other server updates the record, how can a change be synchronised against a deleted record?

As you can tell I'm pretty green with all this, so really appreciate some guidance and advice on how it works and best ways to implement it.

Many thanks,
1DMF





"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[dhulbert]

1DMF

I've tried asking a few replication questions here and never yet got an answer so don't hold your breath.

From a personal point, there are a number of differrent types of replication an depending on what you want to do and how your data architecture is it might drive which one you use. Transactional, Log Shipping, Merge etc.

Just did a quick google on "SQLserver 2000 repliation" and got a few useful hits so it look slik eit is possible although I've only really tried it on SQl 2008.

One Article




I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[1DMF]

Thanks for the heads up.

Not knowing which method to use is part of my investigations, it's all new to me and so require advice.

I shall check out the article and see if it answers some of my questions.

Regards,

1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[biglebowski]

Why do you want to replicate?

DR or queries?

Biglebowskis Razor - with all things being equal if you still can't find the answer have a shave and go down the pub.

 

[1DMF]

Sorry , don't quite understand the option "DR or Queries".

I want to replicate for fault tollerance and speed of applications be them online of inhouse.

we were down for three days last week due to incompetent ISP and BT , which if we replicated wouldn't have happened, I'm assuming?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[SQLBill]

The first thing to decide is what will the servers be used for and that is what biglebowski is asking. Will the second server be used for disaster recovery only, for running reports/queries on, or will it be used for actual business?

Disaster Recovery databases are primarly kept in standby and only used if the original server is down.

Query/Reporting servers don't have any data changes, so you can use Transactional Replication from the primary server to the secondary server. This means all data changes have to happen on the primary server and then they are replicated to the secondary server.

If both servers are used as 'primary' servers and changes can happen on both, that requires more work with replication. I've never set that up or used that type of replication.

To answer some of your other questions.
1. all the servers need to be the same version/build.
2. you can replicate the entire database or just the tables you need. It's usually easier to replication the entire database since that way you aren't missing anything on the secondary server.

Look up Transactional Replication for replication that only goes one way (Publisher/Primary to Subscriber/Secondary) and Merge Replication for replication that goes both ways (changes may be done on either server).

-SQLBill

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[1DMF]

If both servers are used as 'primary' servers and changes can happen on both, that requires more work with replication. I've never set that up or used that type of replication.

That is what I am looking to do by the sounds of things.


We have two SQL servers both are production, one sits on the webserver, 3rd party hosted, the other is inhouse on our SBS server, we have a DES encrypted VPN link between the two.

web apps and inhouse apps access both servers for updates / reporting etc..

They hold different data from each other, the idea is we duplicate them both ends, so the web apps can just access its local SQL and our inhouse apps can access our local SQL server, and the servers keep themselves in sync with each other.

So 'Merge Replication' seems like it's what we want.

But it might not be possible if both SQL's need to be same versions and build.

We currenty have SBS2003 so SQL 2000, the web server is SQL 2005, we are about to upgrade our SBS to 2008, and I think that comes with SQL 2008, so either way, they aren't/won't be the same SQL's :-(

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[SQLBill]

You might be able to do the replication if you set your database compatibilities to the 'lower' version. For example: you now have SS2000 and SS2005, so if you set the databases on your SS2005 server to SS2000 compatibility, then it might work. But then you lose the functionality of SS2005.

The main issue with 'non-equal' versions is that you can introduce something to one server that is fully compatible/supported and on the other server it isn't compatible/supported...then you'll have issues with replication. For example: What if you have a column on the SS2005 server of VARCHAR(MAX)? That doesn't exist on SS2000 so you won't be able to replicate that column.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[1DMF]

gotcha, thanks for the info.




"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[dhulbert]

SQLBill.

I'm trying ot set up a second server as a reporting server, I've looked at transactional replication and that won't work as the tables do not all have primary key filds and the supliers won't add them so I've set up transaction log shipping which works ok but if a user is running a report when the logs are being restored either the user gets disconnected or the restore fails and waits for the next sceduled run.
Any ideas how I can get things set up cleanly, I'm looking for as close to real time as possible. Servers are all SQL 2008R1

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[SQLBill]

I'm not really up on log shipping, but it's real purpose/usage isn't for a second usable server. This is from the BOL:

The secondary server in a log shipping configuration is the server where you want to keep a warm standby copy of your primary database.

That isn't to say you can't use it as a reporting server, it just shows that you will have issues since the process is designed for a server that is not being used.

I don't have a good 'near-real' time solution for you. Transaction replication is about the only way to have a near-real time version.

Things to consider:
How much 'time loss' can you have on the reporting server?
What are the down times (no queries being run)?
Can you implement a 'no use' rule, where users won't query the data from the top of the hour for 10 minutes while the data is brought up to date?

If reports can stop once an hour, find out how long it takes to update the server and then add a few minutes. Tell everyone you need that amount of time every hour to keep the data updated.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[wbodger]

@dhulbert

I use Log Shipping on a SQL 2000 box for my DR, but also for some reporting. I pull the logs every 15 minutes, but only copy and load them on the hour. I then use that server for pulling any queries that are not absolutely time sensitive (don't need up to the second data). The copy and load only takes a few minutes at most and so long as I don't try to update the data, I am fine. However, I do not let users onto that server. They know it exists (well, they kind of know), but they don't know where it is (or what it is named). I don't know of any way around the query failing during load as the server puts itself into single user mode and it is the single user (as far as I understand), but this does give you almost 60 minutes at a time to run your queries. Not an ideal solution, but if external sources are limiting your capability to use replication, you may have no other choice.

wb

 

[biglebowski]

Same here I've setup log shipping in "read only" mode so that other teams can run queries without impacting live. Log shipping is designed for this and is recommened by MS for this purpose:

http://msdn.microsoft.com/en-us/library/ms189572.aspx

Biglebowskis Razor - with all things being equal if you still can't find the answer have a shave and go down the pub.