[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Transactional replication on database tables without keys.

Posted on 2005-04-28
11
Medium Priority
?
403 Views
Last Modified: 2012-06-21
Hello Experts,

I am required to make a backup server of a live database server.
Live & Backup are both running SQL Server 2000.

Backup should be completely in sync with live server so that at any time instant we can replace the live with the backup server.

For this purpose i choosed to implement transactional replication, but problem is that when i came to make publication of my database, it does'nt allow tables without keys to be published as articles.

Kindly tell me any way so that i can publish all tables either with or without key while using transactional replication.

Or if its not possible then tell any other way by which i would be able to make my backup server completely in sync with the live server.

Your early reply is required.

Thanx In Advance.
owais iqbal.
0
Comment
Question by:oiqbal
  • 5
  • 5
10 Comments
 
LVL 13

Expert Comment

by:danblake
ID: 13885411
Or if its not possible then tell any other way by which i would be able to make my backup server completely in sync with the live server.

The easiest way for you to achieve this will be with a third-party product for this specific DR scenario :(If your database schema currently doesnt have Keys/especially primary Keys -- are you sure it is a valid database schema ? (I dont know of a table that should not have a PK !))

Look at:
NSI DoubleTake software.

This will at least provide some level of automation for your DR Failover without any major replication changes occuring.
With replication changes there are application issues that may break the application or result in you rewriting how you handle schema updates for instance.

With replicaiton you also have some level of manual switch over; this software is at least automated to handle the failover.
0
 
LVL 2

Author Comment

by:oiqbal
ID: 13917198
Sorry for the late response but i am busy in getting a solution to what i've stated and i need your help danblake,

motive is to achieve something close to realtime on backup server either hook or by crook (sorry that's how i work things out here).

and i found it ( however not entirely what it should be ).

My database is of 130 mb size with not a rapid increase in size, my servers are very fast, with big memory so i planned to implement snapshot (as it does'nt care about keys) replication, with backup as subscriber and distributor, and live server as a publisher , i've implemented pull subscription to publication.

I've scheduled the snapshot engine to start every 15 minutes 24 hr a day every day,
and the Distribution Agent is set to start when sql server starts so that it always waits and as soon as a snapshot is available it implements it on subscriber.

The process of Making a snapshot took 3 minutes and Distribution Agent took 2 minutes to implement so the whole process take 5 miniutes.

This means my backup server is now about 20 minutes behind live server ( which is good as compared to our current scenario)
0
 
LVL 2

Author Comment

by:oiqbal
ID: 13917218
but

I know i can speed up the snapshot process by configuring it to only overwrite data previously present and not to generate databse schema each time but when i configure this it gives error during snapshot generation that it cannot delete record, it is accessed by foreign key constraint.

If u can help me with this it would be great!

Waiting for your reply.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 13

Expert Comment

by:danblake
ID: 13954752
What you are looking for for a speed up is full blow transactional replication (just send the records which have changed); the reason why I've pointed you to NSI Software is this requires no schema or app changes.

Quite often I have found little nuances that must be considered when implementing transactional replication or other replication techniques that if not considered at the initial design stage makes this job a lot harder.

Transactional Replictation will care about Primary keys !; this also requires timestamp columns (for merge repl.) so you really may need to reconsider your design.

Have you tested how to make the failover work from server a -> server b ?
And then work on the data consistancy issues by reversing the replication from server b -> server a ?
(Where server a = live primary server)

---------------------------------------------------------------------------------------------------------------------------------------
I know i can speed up the snapshot process by configuring it to only overwrite data previously present and not to generate databse schema each time but when i configure this it gives error during snapshot generation that it cannot delete record, it is accessed by foreign key constraint.
--> In this case it must know the exact order of the transaction --> hence transactional replication.

As stated previously; if you have not followed the guidance for replication then you will be proving 'replication' works but not quite how you need it to in your scenario.
0
 
LVL 2

Author Comment

by:oiqbal
ID: 13958247
What is this failover from server a -> server b and is it possible in Microsft SQL Server 2000?

Some thing comes in my mind with your comment that is it possible that i make server A and server B to work in a way so that upper applications sees only one database server,they do transactions ,but behind the scene their would be two servers, working together, updating each other, balancing the load and when one crashesh the other works alone .

In this way upper applications would see only one DB server and backup then become something like a HOT backup.
0
 
LVL 13

Accepted Solution

by:
danblake earned 500 total points
ID: 13966222
What is this failover from server a -> server b and is it possible in Microsft SQL Server 2000?

What you asked for in the origional question.  [Server a= live server ; server b = backup server] You wanted failover if servera [live] is down you can use [backup server] somewhere else.

Backup should be completely in sync with live server so that at any time instant we can replace the live with the backup server.
"This will not be possible with snapshot replication as the database grows"


but behind the scene their would be two servers, working together, updating each other, balancing the load and when one crashesh the other works alone .
.... this is not quite possible; its called clustering -- its limited to a single site (normally) unless you have some extortionaltly expensive hardware to give some distance clustering up to about 100 Km between server a + server b.
The problem of this is normally you have one big san sitting needing connection to server a + server b (which is not the cheapest way of giving some level of failover)...
this is accessed via a virtual db server (one db server points to the underlying database servers in the cluster).
What is important to note is that they share the same storage.

As given above in my previous answers; if you want true distance replication of data with near-enough real-time updates and being able to automatically switch from server a -> server b in operation look at the software I have pointed you to.. much more flexible and can be run on a lot lower scale hardware.
0
 
LVL 2

Author Comment

by:oiqbal
ID: 13985677

danblake,

I think you are right i should be using that NSI Double Software , but i did'nt found it free , can u tell me where it can be downloaded for free?
0
 
LVL 13

Expert Comment

by:danblake
ID: 13989384
It is not available for free.. software like this unfortunatly requires extensive efforts on the behalf of the supplier so it can guarantee a good continuation of service for its clients.
http://www.nsisoftware.com/pro/product/
A short application form giving access to the product.


Other free inbuilt alternatives that will not handle an automated failover you could look at; try log shipping.. not perfect, but would send updates to your alternative server on a regular basis by reading and replicating the transaction log re-applying this automatically at the second site .. a manual failover process is needed often and swap back of the data process but it would give you a second server site that would be ready to go with a bit of lag, the problem will always be ensuring that the data lines between the two is okay..

Backup should be completely in sync with live server so that at any time instant we can replace the live with the backup server.
Its the time factor here that needs to be considered and the distance between the two servers... can you afford any of the data to be not uptodate ?

Basically the further you reduce your time from a "backup / restore scenario =snapshot replication on an automated basis to a second site" the more expensive the solution becomes to guarantee this level of consistency.

There are lots of alternatives many costly in some form or another depends on your budget, enviroment and the importance of the data, the 20min automated restoration scenario may be fine for your purposes.  
All aproaches need to be evaluated depending on your available budget, skills, etc..
0
 
LVL 2

Author Comment

by:oiqbal
ID: 13996627
well it is difficult right away to convince my company to buy this tool , however i have studied the microsoft failover clusterring support in SQL Server 2000 which works on top of windows clustering service , i do have two identical servers.

I want to implement it , Any considerations should i take before starting this process ?
0
 
LVL 13

Expert Comment

by:danblake
ID: 14134528
Best to open a new question, failover clustering has lots of requirements:
SAN (usually) -- and thats not cheap.

it also depends on what kind of failover clustering you are going to implement, there are different considerations depending on your hardware, type of failover, speed of response, distance between the servers,  etc..

lets explore this fully in a new thread, .. would be best to get some more experts on the case.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

873 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