473,727 Members | 2,028 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need Emergency Advice

Dear All,

We have a big concern in our Database system. We have 2000 transactions
daily in our database. We need to replicate some how the database for
our fail over setup. I tried transactional replication at midnight but
our all systems locked and we had a lot of complaints from the
customers and It was taking a lot of time to snapshot part and I had to
abort it because of these reasons.
I need an advice how I can create a replication or is there any other
way to replicate or is there any way to do this process without
bothering the system? Please give me some advice and help..

Thanks
asa.

May 10 '06 #1
9 1540

"laststubbo rn" <ar*********@gm ail.com> wrote in message
news:11******** *************@j 33g2000cwa.goog legroups.com...
Dear All,

We have a big concern in our Database system. We have 2000 transactions
daily in our database. We need to replicate some how the database for
our fail over setup. I tried transactional replication at midnight but
our all systems locked and we had a lot of complaints from the
customers and It was taking a lot of time to snapshot part and I had to
abort it because of these reasons.
Why do the transactional replication only at midnight? Better off doing it
continually throughout the day.

And btw, 2000 transactions, unless they're huge, is trivial. I have systems
doing that per minute.

The other option is to look into log shipping.

There are scripts out there that let you do it even w/o Enterprise edition,
though it is then admittedly more manual.

I need an advice how I can create a replication or is there any other
way to replicate or is there any way to do this process without
bothering the system? Please give me some advice and help..

Thanks
asa.

May 10 '06 #2

Greg D. Moore (Strider) wrote:
"laststubbo rn" <ar*********@gm ail.com> wrote in message
news:11******** *************@j 33g2000cwa.goog legroups.com...
Dear All,

We have a big concern in our Database system. We have 2000 transactions
daily in our database. We need to replicate some how the database for
our fail over setup. I tried transactional replication at midnight but
our all systems locked and we had a lot of complaints from the
customers and It was taking a lot of time to snapshot part and I had to
abort it because of these reasons.
Why do the transactional replication only at midnight? Better off doing it
continually throughout the day.


The thing is snapshot taking a lot of time to do it. Even at night we
had a problem with customers.

And btw, 2000 transactions, unless they're huge, is trivial. I have systems
doing that per minute.

The other option is to look into log shipping.

There are scripts out there that let you do it even w/o Enterprise edition,
though it is then admittedly more manual.
As you said it is manual we can not afford manual solutions.

Thanks

I need an advice how I can create a replication or is there any other
way to replicate or is there any way to do this process without
bothering the system? Please give me some advice and help..

Thanks
asa.


May 10 '06 #3

"laststubbo rn" <ar*********@gm ail.com> wrote in message
news:11******** **************@ j73g2000cwa.goo glegroups.com.. .

Greg D. Moore (Strider) wrote:
"laststubbo rn" <ar*********@gm ail.com> wrote in message
news:11******** *************@j 33g2000cwa.goog legroups.com...
Dear All,

We have a big concern in our Database system. We have 2000 transactions daily in our database. We need to replicate some how the database for
our fail over setup. I tried transactional replication at midnight but
our all systems locked and we had a lot of complaints from the
customers and It was taking a lot of time to snapshot part and I had to abort it because of these reasons.
Why do the transactional replication only at midnight? Better off doing it continually throughout the day.


The thing is snapshot taking a lot of time to do it. Even at night we
had a problem with customers.


I'm not following, or we're not communicating effectively. A transactional
backup should not be taking that long or have that much impact. How large
are these transactions? Like I say, I have a system doing that many a
minute (though that one we don't backup). The one we do transactional
backups on every 20 minutes probably does 2000/hour or more at peak time.
(plus a very high number of pure select only queries.)


And btw, 2000 transactions, unless they're huge, is trivial. I have systems doing that per minute.

The other option is to look into log shipping.

There are scripts out there that let you do it even w/o Enterprise edition, though it is then admittedly more manual.
As you said it is manual we can not afford manual solutions.


Well, what do you consider too manual. In the case of many of these scripts
the failover itself may or may not be automatic, but can generally be
scripted. The log-shipping itself is scripted.

In our case, we do log backups from our main server every 20 minutes and
restore them to our backup server every 20 minutes, but with either a 4 hour
or 6 hour delay (I forget which). To bring the backup system "up to date" I
can do in about 15 minutes.. either manually or via running a quick script.

The bigger delay (whcih we'd have using Enterprise Server also) is updating
our front end DSNs. And we have that scripted also.

All in all, a failover would probably take us 15 minutes or less to recover
from.
Thanks

I need an advice how I can create a replication or is there any other
way to replicate or is there any way to do this process without
bothering the system? Please give me some advice and help..

Thanks
asa.

May 10 '06 #4
yep I think we had a communication problem. I was asking about the
replication not for a backup (I dont know whether you mean replication
when you use 'transactional backup') Back Up is not a problem as you
said we dont have problem on that however I will create failover
cluster but I will replicate the database first. That is my problem
transactional replication.. That is why I asked all these questions.

Thank you

May 10 '06 #5

"laststubbo rn" <ar*********@gm ail.com> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.com.. .
yep I think we had a communication problem. I was asking about the
replication not for a backup (I dont know whether you mean replication
when you use 'transactional backup') Back Up is not a problem as you
said we dont have problem on that however I will create failover
cluster but I will replicate the database first. That is my problem
transactional replication.. That is why I asked all these questions.

Again, I'm still not sure what you mean here.

In my original post I asked why you weren't simply doing the transactional
replication throughout the day.

Then you brought up snap-shotting. This isn't a term usually used with
transactional replication.

A transactional replication setup really shouldn't lock your system, so I'm
missing something here.

Thank you

May 10 '06 #6
Greg D. Moore (Strider) (mo************ ****@greenms.co m) writes:
Then you brought up snap-shotting. This isn't a term usually used with
transactional replication.


Not that replication is my best game, but doesn't a transactional
replication scheme usually start with the replication of a snapshot?

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 10 '06 #7

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...
Greg D. Moore (Strider) (mo************ ****@greenms.co m) writes:
Then you brought up snap-shotting. This isn't a term usually used with
transactional replication.
Not that replication is my best game, but doesn't a transactional
replication scheme usually start with the replication of a snapshot?


Yes, but it's a one time game. And there are ways around that (not
necessarily clean ones unfortunately).


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

May 11 '06 #8
Thats the thing, I could not pass one time snapshot part. During the
initial snapshot I had problems, Our application was locked and It was
taking so long we had to cancel it. You said that there are ways around
that how ? If you give me some help on that I really appreciate it.

Also any of you did a failover clustering? I will do this first time
and I have some questions.

Thanks

May 11 '06 #9

"laststubbo rn" <ar*********@gm ail.com> wrote in message
news:11******** **************@ j73g2000cwa.goo glegroups.com.. .
Thats the thing, I could not pass one time snapshot part. During the
initial snapshot I had problems, Our application was locked and It was
taking so long we had to cancel it. You said that there are ways around
that how ? If you give me some help on that I really appreciate it.
Ah, now I better understand what you're talking about.

I'm a bit surpised with only 2000 transactions a day you can't fnid a quiet
time (perhaps on a weekend?) to make the snapshot.

Anyway, there's KB articles on MS on various ways of doing this (and I
believe SQL 2005 has options also that help.)

Anyway, if you CAN pause work for a shrot period of time, try something like
the following:

Do a full backup of DB on "Publisher" .
Do a full restore WITH NORECOVERY on "subscriber "
Since this will take some time, you'll need to do the following (and up
until now you users could still use the system.)
Stop all access to the system
Do a transaction log backup from the "publisher"
Restore to the "subscriber ", this time with RECOVERY

Setup replication, but say, "subscriber has data and schema"

Permit access to the system.

This is not guaranteed to solve your problem, but I've done it with success
before.

There's several catches to watch out for:

1) if you know your initial transaction log backup will take a long time, do
it w/o kicking folks out of the publisher and restore with NORECOVERY and
then do the transaction log backup a 2nd time (or 3rd, etc.) Trick is to
get that last transaction log backup/restore cycle as absolutely as short as
possible.

Setup as much of replication in advance as possible, i.e. setup the
publisher/distributer side of things before hand.

Script out as much as you can.

Finally.. be careful of IDENTITY columns. This is where things can break.

Note to, if you decide you need to make schema changes on the subscriber, do
this after replication is setup.

The above is not guaranteed to solve your problem but I've used it in
similar cases with success.


Also any of you did a failover clustering? I will do this first time
and I have some questions.

Thanks

May 11 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
2731
by: Alex Martelli | last post by:
All my mailboxes have been filling up with files of about 130k to 150k, no doubt copies of some immensely popular virus. So, I've no doubt lost lots of real mail because of "mailbox full" conditions (the proliferating fake bounce messages more or less ensure nobody knows their mail to me has bounced, either). As an emergency response I and Anna developed, over the last half hour, a small Python script to be run from cron every few...
8
1437
by: Glenn Kelley | last post by:
Hello Group I am hoping that someone will be able to help me. We have a volunteer Fire company that is in need of a database to track the parking tickets we write for the Firelane violations. We live in a very large community - are non-billing and not funded by the township. We want to be able to track who we write violations to - and if more than one violation is written in a season (say Winter) etc
27
2971
by: Josh | last post by:
I work for a small hotel. They don't want to invest in a good front desk program and the paper system that is being used is not working. It is complete chaos at the moment. I want to make a database or program that will be able to manage the guest register and reservations. It doesn't have to handle payments at this stage. Preventing overbookings is a critical priority at the moment because it is going into high season here in Florida....
3
5621
by: Mohammad-Reza | last post by:
We are writing an application for a specific culture(Arabic or Farsi). This application involves using DataAdapter, OLEDB Connection and the DataSet. We didn't use the .NET data binding, just field TextBoxes with the data retrieved from the DataSet but whole system seems to be unable to update the database (.mdb file) with Unicode characters. Instead of displaying the correct characters the application displays question marks (?). Isn't...
10
5818
by: James Radke | last post by:
Hello, I have an emergency question - it was just determined that our backups were incomplete, and we do not have the source code for an application we developed (due to some system delete, and the bad backups)! We do, however still have the EXE modules.. is there a way I can somehow recover the source code from the exe?!?! Thanks!
0
1111
by: James Hu | last post by:
Hi, Sorry to post this message, but I need emergency help, please help me out! I couldn't get drvmcdb.sys from internet, which I accidentally deleted last night, my laptop (XP) couldn't reboot anymore, but I need to keep my data.
1
1570
by: bwarhold | last post by:
i need to have these links open into a new window and can't figure out how. any help would be greatly appreciated. Thanks, b
5
2199
by: Charles May | last post by:
Anyone have a simple concept for the best way to store timeclock information in a database. I currently have my table set up like this with a typical daily entry. tcID empID Type Date Time 1 37 'Clockin' 1/18/08 7:45:39 AM 2 38 'Clockin' 1/18/08 7:55:42 AM 3 39 'Clockin' 1/18/08 7:55:42 AM 4 38 'BreakOut' 1/18/08 ...
0
1145
by: fr5478bey | last post by:
Spy Emergency 2007 crack http://cracks.00bp.com F R E E
0
8752
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9406
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9261
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9185
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8106
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6012
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4521
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4786
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3229
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.