DR: Replication vs. log shipping vs. clustering vs. database mirroring........

Up to now we have gotten by without having any local DR copies of servers (if a sql server goes down we are usually able to get it back in less than 3 hours). But I want more now. I want to trim the "down" window to no more than 5 or 10 minutes. (Immedate failover would be nice but is not an essential requirement. The essential requirement is to loose no data!)

I have a spec of knowledge in these areas:

SQL 2005 Clustering (requires approved hardware, quorum disk, etc. involved)

SQL 2005 Replicaiton

SQL 2005 Log Shipping.

SQL 2005 Database mirroring. ( needs three servers)

Which approach do you think is the most straightforward, sparing of hardware, yet reliable way to get us back up and running after a sql server failure.

TIA,

barkingdog

All of the above, none of the above, some combination of the above. It isn't a yes or no answer and it isn't a one size fits all. Each option has pros and cons. I teach a 5 day hands on class on High Availability and at the end of about 46 hours of instruction, we cover about 1/4 of the topic. It gives you a basic foundation for understanding the pros and cons of each technology as well as how it fits into an environment. The hard part is the implementation. There are 800+ page books on the SQL Server HA stuff from SQL Server 2000. I'm working on one for SQL Server 2005 that is probably going to weigh in at a two volume set of almost 1700 combined pages. (About 800 pages of it are already done and I still have at least 3 technologies and 17 subjects to cover.) It depends upon your environment, business requirements, any needs for the application etc. If you can be a little more specific than "I want better availability", it might be possible to more closely target something.|||

We want to consolidate several sql 2000 development servers into a single sql 2005 server. BUT if the sql 2005 server goes down, until we can fix it, the developers are going to have a lot of free time on their hands! The goal here is to minimize the amount of down time (say less than 20 minutes) so our developers can get back on the road ASAP. Of course, if the problem is hardware-related we could be down for a lot longer than 20 minutes, implying another server (maybe even a virtual server?) should be part of the environment.

Barkingdog

|||Based on the fact that it is a development environment, I would simply drop in log shipping and be done. It would require developers to reconnect to a different SQL Server instance that might be missing a small amount of data, but that is unimportant in a dev environment. It gives you basic redundancy without going overboard. And you also have to keep in mind that everything in a dev environment should be able to be recreated by a developer, so you are simply looking fdor redundancy to reduce the amount of stuff that needs to be recreated in the event of a failure.|||

I am reading about log shipping. It seems to be very focused on "one database at a time" type operations. One way is to use SSMS, right-click the database of interest, go to Properties, indicate it is the primary database.... and do this for each database on the server.

I want to apply log shipping to ALL the databases at once, not one step at a time. Is there any accomodaiton to do this?

TIA,

Barkingdog

|||

I have just finished my first pass reading about Log Shipping. In point of fact, while the concept is simple, the implementation has lots of details, is error prone, and requires changes to the way things normally work around here.

My greatest concern with all such approaches is that we will set it up, it will run fine on auto pilot, we will forget about the details, and then the emergency hits. Since we are no longer "sharp" it is very possible that we could forget to do something or do the wrong something, undermining the purpose of LS. (I know ... have good documentation....) I have the same issue with other "Disaster Recovery" type products. too.

Barkingdog

|||

Since you mentioned Database Mirroring, my assumption is SQL Server 2005 for all of this. You don't have to click through SSMS. You can script the log shipping implementation. It will require that you connect to two different instances and execute the portions of the script in order, but you don't have to click through a GUI.

As for the other side, no one is going to be able to help with that issue. High Availability isn't technology in a box. It requires people, processes, and technology to work together to achieve your availability goals. Deploying technology without well tested and documented procedures for accomplishing a failover and possibly a failback is just a waste of time. The technology is only as good as the processes that are in place to ensure the technologies can meet your business needs. The processes are only as good as the documentation that you have in place. The processes are only as good as the people implementing and performing the processes. You can't have one without the other.

Implementing a high availability solution is 5% about technology. The other 95% is all about processes and failover procedures.

|||

Excellent answers thanks.

Your advise about failover procedures is sage-like. I do find that mirroring is more to my "liking". And why it may not be needed on a developer box it would be good practice ground for our sql 2005 production servers (which also need falover).

Barkingdog

sql

DR Server using replication

Hi,

I am planning on setting up disaster recovery configuration. It is planned to use transactional replication from the production server to the DR server. Are there any recommendations for replicating changes back to the production server, once it is back online. I suppose that it would be safest to move the database back the other server over a weekend or during downtime. But I am just wondering whether anyone has experience of other practical solutions.

Many thanks
DavidDid you look into log shipping?|||Replication is a poor choice for Disaster Recovery. The cost of maintaining the replication is much higher than using Log Shipping or a home grown solution similar to Log Shipping.

The first questions that need to be answered by the owner of the budget are:
1. How much data can you afford to lose?
2. How much (business hours) downtime can be tolerated?
3. How much money are you willing to spend on Disaster Recovery?

You gotta put numbers on the cost/benefit or you'll be rolling boulders uphill for a long, long time.

Unfortunately, typical, thoughtless, knee-jerk answers are:
1. none
2. 1 minute
3. $9.95

A better approach may be to put together a menu of options to go along w/ the questions above as well as other relevant questions:
A1. Lose no more than 5 minutes of transactions, recover within 20 minutes at a cost of $40K + $10K per subsequent years.
B2. Lose no more than 60 minutes of transactions, recover within 4 hours at a cost of $20K + $5K per subsequent years.
C3. Lose no more than one day of data and recover within 4 hours at a cost of $1K + $1K per subsequent years.

Ninety percent of failures are caused by humans - e.g. accidentally Delete w/o a Where clause. Those are the types of failures from which you need to focus on recovering. For example, your DR/backup server should intentionally be kept out of sync. by however much time is needed to detect and respond to a human error: 4 hours works well.

Finally, unless you are willing to determine every failure permutation and the steps needed to recover from each, the answer to your first question is: stop production, figure out what is missing and copy the data using the most convenient, familiar approach you have.|||Sybase has a replication server product that does exactly what you need. It can replicate many database vendor's data including MSSQL.

http://www.sybase.com/products/informationmanagement/replicationserver|||Thank you for the responses.

Unfortunately the client insists that they cannot afford any downtime, which is why they are going down this route. I had asked them to look at third party tools such as emc, but they decided that this wouldn't be possible for political reasons. Log shipping doesn't suit application either, so we are left with doing transactional rep on all the data.

Thanks|||Why can't companies understand DR is not the same is High Availability? Too bad you can't cluster your production server and use log shipping to a DR solution.

I'm not very familiar with replication but if you need to get changes back to the original production server shouldn't you use merge replication?|||Thank you for the responses.

Unfortunately the client insists that they cannot afford any downtime, which is why they are going down this route. I had asked them to look at third party tools such as emc, but they decided that this wouldn't be possible for political reasons. Log shipping doesn't suit application either, so we are left with doing transactional rep on all the data.

Thanks

Did you look at the Sybase solution? What you are really looking at is Warm Standby, which replicates (transactionally) all of the changes from the "active" connection to the warm standby connection. The repserver is aware which connection is the "active" and which is the "warm standby" at all times. When you switch them, replication flows the other way automatically.

DownloadGenerationsPerBatch and SQLMerge

Using Merge replication.
Publisher is Sql Server 2000 SP4, subscribers are laptops using MSDE SP4.
Using anonymous subscribers that use the SQLMerge object to invoke
replication. We've run into issues where the publisher has deleted subscriber
records so we are changing all of our FKs for NFR and want to set
DownloadGenerationsPerBatch to 2000.
However, all documentation and newsgroups just talk about a command line
argument. How do I set DownloadGenerationsPerBatch on the SQLMerge object.
create a profile for this, and use the profile name option in the activeX
merge object - ie ProfileName
But I think you could solve your problem by using
compensate_for_errors=false in sp_addmergearticle
Consult this kb for more info
http://support.microsoft.com/kb/828637
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Buzz" <buzz@.online.nospam> wrote in message
news:C26C42E3-906B-4CFA-ADBE-81183E43C890@.microsoft.com...
> Using Merge replication.
> Publisher is Sql Server 2000 SP4, subscribers are laptops using MSDE SP4.
> Using anonymous subscribers that use the SQLMerge object to invoke
> replication. We've run into issues where the publisher has deleted
subscriber
> records so we are changing all of our FKs for NFR and want to set
> DownloadGenerationsPerBatch to 2000.
> However, all documentation and newsgroups just talk about a command line
> argument. How do I set DownloadGenerationsPerBatch on the SQLMerge object.
>
|||Thanks for pointing me in the right direction! Both with profiles and the new
article option!
"Hilary Cotter" wrote:

> create a profile for this, and use the profile name option in the activeX
> merge object - ie ProfileName
> But I think you could solve your problem by using
> compensate_for_errors=false in sp_addmergearticle
> Consult this kb for more info
> http://support.microsoft.com/kb/828637
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Buzz" <buzz@.online.nospam> wrote in message
> news:C26C42E3-906B-4CFA-ADBE-81183E43C890@.microsoft.com...
> subscriber
>
>
sql

Double-Take vs SQL Server Replication?

In another thread I noticed that Hilary mentioned Double-Take. I
had forgotten about this product.
I am curious if Double-Take would be a better solution for a
standby server, as wouldn't it replicate both ddl an dml data?
Anyone know aproximate cost of this product for a 2 server
enviornment?
Thanks
Dave
Its expensive, IIRC and I could be way off here it costs between 10 to 20k
for a per server license. This was about 4 years ago.
It works by shoehorning a driver between the file system and the OS, it then
sends the deltas to the secondary. It does use some CPU and RAM. RAM
consumption can be significant if your secondary goes off line. It
replicates/mirrors the entire drive/array so you can't do any form of
filtering which can be a good or bad thing for you. So it does replicate DML
and DDL.
In general it is not really scalable so it really is only a good choice in a
limited set of circumstances.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Gresham" <gresham@.panix.com> wrote in message
news:d5d37l$m4m$1@.reader1.panix.com...
> In another thread I noticed that Hilary mentioned Double-Take. I
> had forgotten about this product.
> I am curious if Double-Take would be a better solution for a
> standby server, as wouldn't it replicate both ddl an dml data?
>
> Anyone know aproximate cost of this product for a 2 server
> enviornment?
>
> Thanks
>
> Dave
>
|||I saw a presentation of double-take and geo-cluster from NSI software last
month...IIRC, the pricing was between $2495 and $5000 per server, but I
don't recall which price was for which product.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eUx8uNYUFHA.1944@.TK2MSFTNGP14.phx.gbl...
> Its expensive, IIRC and I could be way off here it costs between 10 to 20k
> for a per server license. This was about 4 years ago.
> It works by shoehorning a driver between the file system and the OS, it
> then
> sends the deltas to the secondary. It does use some CPU and RAM. RAM
> consumption can be significant if your secondary goes off line. It
> replicates/mirrors the entire drive/array so you can't do any form of
> filtering which can be a good or bad thing for you. So it does replicate
> DML
> and DDL.
> In general it is not really scalable so it really is only a good choice in
> a
> limited set of circumstances.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "David Gresham" <gresham@.panix.com> wrote in message
> news:d5d37l$m4m$1@.reader1.panix.com...
>

Double replication

I have merge replication set up on Server A with the distributor also on Server A. I want to set up transactional replication on this same Server A, but with the subscribing Server B set up as the distributor. Is this possible? I've tried creating a ne
w publication, but it won't give me the option to use Server B as the distributor--I think probably because Server A is already configured. Is there anyway to achieve this? Thank you.
no each Publisher server has a single distributor.
Multiple publishers can share the same distribution database on a single
distributor, or they can have their own seperate distribution databases on a
single distributor, but a single publisher can only have 1 distribution
database which can be on the publisher, distributor or subscriber.
"Andrea Worley" <k_rage@.hotmail.com> wrote in message
news:C05E8AD2-6D42-4CE0-BF50-245381D2E841@.microsoft.com...
> I have merge replication set up on Server A with the distributor also on
Server A. I want to set up transactional replication on this same Server A,
but with the subscribing Server B set up as the distributor. Is this
possible? I've tried creating a new publication, but it won't give me the
option to use Server B as the distributor--I think probably because Server A
is already configured. Is there anyway to achieve this? Thank you.
|||So, is it possible to set up trans replication to another server even though the existing publication is for merge replication? (And by your answer, I would be using the same distributor) And if so, will it be possible to disable the trans replication in
the future without messing up the existing merge publication? Thank you for your answer.
-- Hilary Cotter wrote: --
no each Publisher server has a single distributor.
Multiple publishers can share the same distribution database on a single
distributor, or they can have their own seperate distribution databases on a
single distributor, but a single publisher can only have 1 distribution
database which can be on the publisher, distributor or subscriber.
"Andrea Worley" <k_rage@.hotmail.com> wrote in message
news:C05E8AD2-6D42-4CE0-BF50-245381D2E841@.microsoft.com...
> I have merge replication set up on Server A with the distributor also on
Server A. I want to set up transactional replication on this same Server A,
but with the subscribing Server B set up as the distributor. Is this
possible? I've tried creating a new publication, but it won't give me the
option to use Server B as the distributor--I think probably because Server A
is already configured. Is there anyway to achieve this? Thank you.
|||yes, you can replicate a table that is part of a merge publication using
transactional publication as long as its not transactional publication with
queued updating.
You can drop the table from the merge publication or the transactional
publication without breaking either publication.
The publications are completely independent of each other (except when you
are using queued with merge).
"Andrea Worley" <anonymous@.discussions.microsoft.com> wrote in message
news:C97EA05A-916E-4713-8319-05D794082EF1@.microsoft.com...
> So, is it possible to set up trans replication to another server even
though the existing publication is for merge replication? (And by your
answer, I would be using the same distributor) And if so, will it be
possible to disable the trans replication in the future without messing up
the existing merge publication? Thank you for your answer.
>
> -- Hilary Cotter wrote: --
> no each Publisher server has a single distributor.
> Multiple publishers can share the same distribution database on a
single
> distributor, or they can have their own seperate distribution
databases on a
> single distributor, but a single publisher can only have 1
distribution[vbcol=seagreen]
> database which can be on the publisher, distributor or subscriber.
> "Andrea Worley" <k_rage@.hotmail.com> wrote in message
> news:C05E8AD2-6D42-4CE0-BF50-245381D2E841@.microsoft.com...
also on
> Server A. I want to set up transactional replication on this same
Server A,
> but with the subscribing Server B set up as the distributor. Is this
> possible? I've tried creating a new publication, but it won't give
me the
> option to use Server B as the distributor--I think probably because
Server A
> is already configured. Is there anyway to achieve this? Thank you.
>
>
|||Hilary, I tried creating another publication for trans rep using the same distributor that is set up for the merge rep. I got the error: Error 14294 Supply either @.job_id or @.job_name to identify the job.
Searching on this error, I saw in a different post that you wrote that it is caused by having duplicate agent names (your post is below). How do I resolve this issue if I am setting up trans replication using the wizards? I don't see how I can change th
e names. Do I have to script out the replication for this to work? Thanks.
[vbcol=seagreen]
if you are keeping the existing publications/subscriptions intact and
creating new subscriptions/publications based on the scripts you have to
modify the publication name, the log reader agent name, the snapshot agent
name, and the distribution agent name. Otherwise you will get errors like
this.[vbcol=seagreen]
-- Hilary Cotter wrote: --
yes, you can replicate a table that is part of a merge publication using
transactional publication as long as its not transactional publication with
queued updating.
You can drop the table from the merge publication or the transactional
publication without breaking either publication.
The publications are completely independent of each other (except when you
are using queued with merge).
"Andrea Worley" <anonymous@.discussions.microsoft.com> wrote in message
news:C97EA05A-916E-4713-8319-05D794082EF1@.microsoft.com...
> So, is it possible to set up trans replication to another server even
though the existing publication is for merge replication? (And by your
answer, I would be using the same distributor) And if so, will it be
possible to disable the trans replication in the future without messing up
the existing merge publication? Thank you for your answer.[vbcol=seagreen]
single
> distributor, or they can have their own seperate distribution
databases on a
> single distributor, but a single publisher can only have 1
distribution[vbcol=seagreen]
> database which can be on the publisher, distributor or subscriber.
> "Andrea Worley" <k_rage@.hotmail.com> wrote in message
> news:C05E8AD2-6D42-4CE0-BF50-245381D2E841@.microsoft.com...
also on
> Server A. I want to set up transactional replication on this same
Server A,
> but with the subscribing Server B set up as the distributor. Is this
> possible? I've tried creating a new publication, but it won't give
me the
> option to use Server B as the distributor--I think probably because
Server A[vbcol=seagreen]
> is already configured. Is there anyway to achieve this? Thank you.
|||create another publication perhaps in your pubs database and replicate to
your northwind database. If this works you have a localized problem with
your publication database.
Script out your publication and subscriber and then drop it.
Change the publication name throughout your script to something you have not
used before.
Go through your entire script and remove all references to
@.snapshot_job_name, @.logreader_job_name, @.qreader_job_name if you have one.
Re run your script.
"Andrea Worley" <anonymous@.discussions.microsoft.com> wrote in message
news:F356A1A1-E061-4F90-8905-3D41ECC16DA4@.microsoft.com...
> Hilary, I tried creating another publication for trans rep using the same
distributor that is set up for the merge rep. I got the error: Error 14294
Supply either @.job_id or @.job_name to identify the job.
> Searching on this error, I saw in a different post that you wrote that it
is caused by having duplicate agent names (your post is below). How do I
resolve this issue if I am setting up trans replication using the wizards?
I don't see how I can change the names. Do I have to script out the
replication for this to work? Thanks.
> if you are keeping the existing publications/subscriptions intact and
> creating new subscriptions/publications based on the scripts you have to
> modify the publication name, the log reader agent name, the snapshot agent
> name, and the distribution agent name. Otherwise you will get errors like
> this.
> -- Hilary Cotter wrote: --
> yes, you can replicate a table that is part of a merge publication
using
> transactional publication as long as its not transactional
publication with
> queued updating.
> You can drop the table from the merge publication or the
transactional
> publication without breaking either publication.
> The publications are completely independent of each other (except
when you
> are using queued with merge).
> "Andrea Worley" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
> news:C97EA05A-916E-4713-8319-05D794082EF1@.microsoft.com...
even
> though the existing publication is for merge replication? (And by
your
> answer, I would be using the same distributor) And if so, will it be
> possible to disable the trans replication in the future without
messing up[vbcol=seagreen]
> the existing merge publication? Thank you for your answer.
on a[vbcol=seagreen]
> single
> databases on a
> distribution
subscriber.[vbcol=seagreen]
> also on
same[vbcol=seagreen]
> Server A,
Is this[vbcol=seagreen]
give[vbcol=seagreen]
> me the
because[vbcol=seagreen]
> Server A
Thank you.[vbcol=seagreen]
|||The create publication does not work in the pubs database either. I think for now I am going to just set up some DTS jobs to bulk copy the data to a new database. Thanks for your help.
|||In this case you need to disable publishing (through the tools menu, select
Replication and then click disable publishing).
Then re-install and try again. If this fails try to reapply sp3.
"Andrea Worley" <anonymous@.discussions.microsoft.com> wrote in message
news:F5126947-6463-4AD5-9220-42B2FAE2CB7A@.microsoft.com...
> The create publication does not work in the pubs database either. I think
for now I am going to just set up some DTS jobs to bulk copy the data to a
new database. Thanks for your help.

Double inserts during snapshot posting of transactional replication

Hi,
So, I have setup transactional replication, uses the initial snapshot. When
the snapshot is being posted, it runs the same command, and causing it to
fail on the primary key constraint.
Has anyone seen this before?
Gary,
please can you post up the error message.
Also, are you doing a nosync subscription or is it a full initialization?
Regards,
Paul Ibison

Don't Want to Script Replication Objects

I have a customized scripting procedure that will script all objects in a
given db.
My problem is, on a db set up for replication, I don't want to script all of
the objects that the system creates to manage replication -- just the
original db objects.
I there way to isolate replication objects?
Are they flagged somewhere in the system as objects that were not actually
created by the user, but by the server system?
Thanks
The best way is to remove them from the scripting interface. Its a pain to
do.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:%23OkAsxLAGHA.3924@.TK2MSFTNGP10.phx.gbl...
>I have a customized scripting procedure that will script all objects in a
> given db.
> My problem is, on a db set up for replication, I don't want to script all
> of
> the objects that the system creates to manage replication -- just the
> original db objects.
> I there way to isolate replication objects?
> Are they flagged somewhere in the system as objects that were not actually
> created by the user, but by the server system?
> Thanks
>
|||You might have to base this largely on the object name prefixes eg
MSmerge_conflict_<PublicationName>_<ArticleName> for the conflict tables. I
mentioned this to Redgate because it becomes a pain there (SQLCompare) also,
but they didn't seem to find an easy solution either.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||What do you mean?
What scripting interface?
How do I remove them without isolating them?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OxPHzGMAGHA.3924@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> The best way is to remove them from the scripting interface. Its a pain to
> do.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:%23OkAsxLAGHA.3924@.TK2MSFTNGP10.phx.gbl...
all[vbcol=seagreen]
actually
>

Domain to DMZ Question

How do you setup replication without using window authentication when trying to replicate from a domain to a workgroup in the DMZ, without using trusts?
use sql authentication and FTP.
"JLS" <jlshoop@.hotmail.com> wrote in message news:Ot6P$$MHEHA.3196@.tk2msftngp13.phx.gbl...
How do you setup replication without using window authentication when trying to replicate from a domain to a workgroup in the DMZ, without using trusts?

Domain Change breaks replication

Over the weekend the IT infrastructure guys changed the domain name on all
our servers.
Replication is now failing with a "The agent is suspect. No response within
last 10 minutes". Both the Log Reader and Push subscription have this error.
The SQL Server agent seems to be fine. I have even rebooted the server and
the agent still seems to be running fine.
I have logged in with my new user name under the new domain. I have deleted
one of the old replications and added a new one. Same "The agent is suspect"
error message. Furthermore the "owner" of the new agent is my login under
the old domain.
I tried adding myself (new domain me) as a new user to the system, but it
fails with a "Error 15401: Windows NT user or group 'DOMAIN\username' not
found. Check the name again." Which is of course interesting since I logged
on to the server with that domain and username.
I have tried altering the "owner" of the relevant agents, but it doesn't
want to change to any of the owners set up prior to the domain change.
I have added some new users to the system, but now the message changes to
"Non-SysAdmins have been denied permission to run replication jobsteps
without a proxy account. The step failed."
Any ideas?
Thanks
Derek
After you kill the IT infrastructure guys try the following:
Check the SQL Server agent (and SQL Server service) on both the publisher
and subscriber is starting with an account in the new domain that has all
the necessary permissions (see Books on Line for Agent Login Security under
Replication Security). They may have been set back to the local system
account by the IT guys to get them to start.
Set the ownsership of all the jobs to "sa".
Good luck
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Derek" <Derek@.discussions.microsoft.com> wrote in message
news:4FEADBDF-4A4B-4E7F-8F81-83BD07BC3044@.microsoft.com...
> Over the weekend the IT infrastructure guys changed the domain name on all
> our servers.
> Replication is now failing with a "The agent is suspect. No response
> within
> last 10 minutes". Both the Log Reader and Push subscription have this
> error.
> The SQL Server agent seems to be fine. I have even rebooted the server
> and
> the agent still seems to be running fine.
> I have logged in with my new user name under the new domain. I have
> deleted
> one of the old replications and added a new one. Same "The agent is
> suspect"
> error message. Furthermore the "owner" of the new agent is my login under
> the old domain.
> I tried adding myself (new domain me) as a new user to the system, but it
> fails with a "Error 15401: Windows NT user or group 'DOMAIN\username' not
> found. Check the name again." Which is of course interesting since I
> logged
> on to the server with that domain and username.
> I have tried altering the "owner" of the relevant agents, but it doesn't
> want to change to any of the owners set up prior to the domain change.
> I have added some new users to the system, but now the message changes to
> "Non-SysAdmins have been denied permission to run replication jobsteps
> without a proxy account. The step failed."
> Any ideas?
> Thanks
> Derek
>
|||In addition to Mary's recommendations, you might find
this article useful:
http://support.microsoft.com/kb/q324321/
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks guys
Eventually I took the login that starts SQL Server, gave it all the
permissions under the sun and set it as the owner of the replications.
This got it working, although each replication needed to think for a while
before it started again.
Unfortunately I can't kill the IT Infrastructure guys as I was asked 6 weeks
ago if I thought there would be any problems. I replied that the
replications would need to be set up, but completely forgot about it (even
though I knew they were doing it on the weekend) until I come in Monday and
replications aren't working.
Luckily I'm in Australia and they are in England, so I had a few hours to
get things working.
Later
Derek
"Paul Ibison" wrote:

> In addition to Mary's recommendations, you might find
> this article useful:
> http://support.microsoft.com/kb/q324321/
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

domain account vs local account for SQLServerAgent

Hi there,

BOL notes that in order for replication agents to run properly, the
SQLServerAgent must run as a domain account which has privledges to log
into the other machines involved in replication (under "Security
Considerations" and elsewhere). This makes sense; however, I was
wondering if there were any repercussions to using duplicate local
accounts to establish replication where a domain was not available.
Anotherwords, create a local windows account "johndoe" on both machines
(with the same password), grant that account access to SQL Server on
both machines, and then have SQL Server Agent run as "johndoe" on both
machines. I do not feel this is an ideal solution but I have
circumstances under which I may not have a domain available; my
preliminary tests seem to work.

Also, are there any similar considerations regarding the MSSQLSERVER
service, or can I always leave that as local system?

Dave"Dave C." <metal@.rules.spam> wrote in message news:<Pine.LNX.4.44.0311061247410.17583-100000@.ccrma-gate.stanford.edu>...
> Hi there,
> BOL notes that in order for replication agents to run properly, the
> SQLServerAgent must run as a domain account which has privledges to log
> into the other machines involved in replication (under "Security
> Considerations" and elsewhere). This makes sense; however, I was
> wondering if there were any repercussions to using duplicate local
> accounts to establish replication where a domain was not available.
> Anotherwords, create a local windows account "johndoe" on both machines
> (with the same password), grant that account access to SQL Server on
> both machines, and then have SQL Server Agent run as "johndoe" on both
> machines. I do not feel this is an ideal solution but I have
> circumstances under which I may not have a domain available; my
> preliminary tests seem to work.
> Also, are there any similar considerations regarding the MSSQLSERVER
> service, or can I always leave that as local system?
> Dave

See "Setting up Windows Services Accounts" in BOL. There are a number
of things that can only be done with a domain account; in addition,
LocalSystem is a highly privileged account, so for both functionality
and security reasons, using a domain account is usually a good idea.

I'm not sure about using multiple local accounts if no domain is
available. I would guess that it would work, but password management
and synchronization could be awkward.

Simon

Does transactional replication require DTC?

Hi
Does the DTC service need to function properly if I plan to use
transactional replication between two Microsoft SQL Server 2000 machines?
Many thanks,
Oskar
no, unless you are using immediate updating.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:71A33B88-8AB7-4395-97DC-32F463381F26@.microsoft.com...
> Hi
> Does the DTC service need to function properly if I plan to use
> transactional replication between two Microsoft SQL Server 2000 machines?
> --
> Many thanks,
> Oskar
>

Does transactional replication replicate SPs?

I set up TR on table T1. Works fine.
While it was running, I tried adding an article, mysp.
I couldn't quite figure out what to do to send the initial snapshot of
just mysp, while replication was already running for T1.
Eventually I ... well, I lost track. I think I deleted the
subscription, then pushed it again, then finally told it explicitly to
run the snapshot agent immediately. OK, the SP travelled.
So, do I now have both the table and the SP replicating?
No. I can add rows to the table on the publisher, and gosh if they
don't show up on the subscriber within about a minute. But I can
modify the SP on the publisher, and the changes to not show up on the
subscriber.
So, I'm wondering, is that even supposed to work?
Thanks.
Josh
Josh,
Changes to stored procedures and other database objects do not replicate in
SQL Server 2000. You have to wait for SQL 2005 to do that!
Jon Jahren
"jxstern" <jxstern@.nowhere.com> wrote in message
news:ugj5p0137irl7tvc5oi6nopf3g3u2lkpmf@.4ax.com...
> I set up TR on table T1. Works fine.
> While it was running, I tried adding an article, mysp.
> I couldn't quite figure out what to do to send the initial snapshot of
> just mysp, while replication was already running for T1.
> Eventually I ... well, I lost track. I think I deleted the
> subscription, then pushed it again, then finally told it explicitly to
> run the snapshot agent immediately. OK, the SP travelled.
> So, do I now have both the table and the SP replicating?
> No. I can add rows to the table on the publisher, and gosh if they
> don't show up on the subscriber within about a minute. But I can
> modify the SP on the publisher, and the changes to not show up on the
> subscriber.
> So, I'm wondering, is that even supposed to work?
> Thanks.
> Josh
>
|||Replication of stored procs is intended to be a one time event. If you
modify procs frequently and you wish to replicate them bundle them up in a
script and use sp_addscriptexec to deploy them to all subscribers. The proc
sp_addscriptexec only works for subscriptions deployed via UNCs. If are
using FTP subscribers you will have to create a seperate publication to
deploy your procs. It seems that the procs schemas are cached and if you
redeploy the snapshot after modifying the procs the updates won't be
replicated. You are best to drop the proc publication and recreate it.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"jxstern" <jxstern@.nowhere.com> wrote in message
news:ugj5p0137irl7tvc5oi6nopf3g3u2lkpmf@.4ax.com...
> I set up TR on table T1. Works fine.
> While it was running, I tried adding an article, mysp.
> I couldn't quite figure out what to do to send the initial snapshot of
> just mysp, while replication was already running for T1.
> Eventually I ... well, I lost track. I think I deleted the
> subscription, then pushed it again, then finally told it explicitly to
> run the snapshot agent immediately. OK, the SP travelled.
> So, do I now have both the table and the SP replicating?
> No. I can add rows to the table on the publisher, and gosh if they
> don't show up on the subscriber within about a minute. But I can
> modify the SP on the publisher, and the changes to not show up on the
> subscriber.
> So, I'm wondering, is that even supposed to work?
> Thanks.
> Josh
>
|||On Thu, 11 Nov 2004 11:33:11 -0500, "Hilary Cotter"
<hilary.cotter@.gmail.com> wrote:
>Replication of stored procs is intended to be a one time event. If you
>modify procs frequently and you wish to replicate them bundle them up in a
>script and use sp_addscriptexec to deploy them to all subscribers. The proc
>sp_addscriptexec only works for subscriptions deployed via UNCs. If are
>using FTP subscribers you will have to create a seperate publication to
>deploy your procs. It seems that the procs schemas are cached and if you
>redeploy the snapshot after modifying the procs the updates won't be
>replicated. You are best to drop the proc publication and recreate it.
Thanks to you and Jon.
Since the dialog lets you click tables and procs equally, and BOL is
not terribly clear on the matter, it's easy to think the procs do
replicate.
But, they do replicate in Yukon?
And back here in SQL2K, I thought there was something in BOL about
replicated SP's also executing on the subscriber, ... or is that
another confusion?
http://msdn.microsoft.com/library/de...limpl_0d2m.asp
I'd appreciate further insight on this.
Thanks!
Josh
|||On Thu, 11 Nov 2004 09:55:38 -0800, jxstern <jxstern@.nowhere.com>
wrote:
>And back here in SQL2K, I thought there was something in BOL about
>replicated SP's also executing on the subscriber, ... or is that
>another confusion?
>http://msdn.microsoft.com/library/de...limpl_0d2m.asp
>I'd appreciate further insight on this.
Also, the radio button that is supposed to turn on this SP execution
replication is greyed out on my dialog. Why would this be (because it
is an existing? Yes! Just as the warning box suggests, to enable it
I guess I'd have to drop and reinitialize, since when I add a new SP,
the option is enabled. Guess I don't have a question here after all,
but could still use any further info anyone cares to drop on me.)
J.
|||You could drop the article from the subscription and readd if you don't want
to reinitialize the complete publication.
sp_dropsubscription @.publication = 'northwindxxx'
, @.article = 'region'
, @.subscriber = 'pll-lt-16'
sp_droparticle @.publication = 'northwindxxx'
, @.article = 'region'
sp_refreshsubscriptions @.publication ='northwindxxx'
To Readd, just do the reverse. Running the snapshot agent will snapshot just
the article you need.
To avoid this type of issue, I tend to put all the stored procs in a
separate publication.
rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Does transaction replication modify the DB

Hi,
I have used merge replication for SQL Server DBs before but not
transactional replication. We have a DB with an existing application
attached and I know that merge replication changes the DB and may break the
app (otherwise I would use it). I assume that transactional replication
does not change the user tables in the DB?
Are there any constraints on what DB designs transactional replication
supports?
Grateful for any info
Pete
Pete,
transactional replication will add a guid column if it involves upatable
subscribers, and triggers will be added. For non-updatable subscribers,
there is a schema requirement - PK on tables - but no such schema changes.
This is from the point of view of the publisher.
From the subscriber point of view, there may be some schema changes - eg
identity columns lose their identity attribute, although this is not true
for queued updating subscribers where the identity attribute may be created
with an alternative seed.
HTH,
Paul Ibison
|||Thanks that is really useful.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23yJ4u9caEHA.2812@.tk2msftngp13.phx.gbl...
> Pete,
> transactional replication will add a guid column if it involves upatable
> subscribers, and triggers will be added. For non-updatable subscribers,
> there is a schema requirement - PK on tables - but no such schema changes.
> This is from the point of view of the publisher.
> From the subscriber point of view, there may be some schema changes - eg
> identity columns lose their identity attribute, although this is not true
> for queued updating subscribers where the identity attribute may be
created
> with an alternative seed.
> HTH,
> Paul Ibison
>
|||I published sample 2 DBs with transaction replication as a test.
In one of them it added the msrepl_tran_version column to every table - when
I published another it didn't do this. I think the only difference is with
one I choose "display advanced options" in the replication wizard - then I
choose queued updating.
Can you use immediate updating if you are publishing accross an internet
(512 ADSL) connection?
Thanks
Pete
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23yJ4u9caEHA.2812@.tk2msftngp13.phx.gbl...
> Pete,
> transactional replication will add a guid column if it involves upatable
> subscribers, and triggers will be added. For non-updatable subscribers,
> there is a schema requirement - PK on tables - but no such schema changes.
> This is from the point of view of the publisher.
> From the subscriber point of view, there may be some schema changes - eg
> identity columns lose their identity attribute, although this is not true
> for queued updating subscribers where the identity attribute may be
created
> with an alternative seed.
> HTH,
> Paul Ibison
|||Pete,
imediate updating uses 2PC and DTC, ie it needs a direct and constant
connection to allow subscribers to change data. If you have this set up then
it'll be OK, otherwise you'll need queued updating subscribers.
HTH,
Paul Ibison