Need help - desperate

Hi,
I am contacting you all for your help in solving a problem with
transactional replication in SQL server 2005. The setup is as follows:
One publisher and three subscribers - all are SQL 2005
Distributor database is in the same server as the publisher database.
I had set it up and it was working fine. unfortunately, the publisher
database was reloaded when I was on vacation. Now the replication is broken.
I have tried the following
Dropped the subscription.
There was no publication to be dropped. so I dropped the database, recreated
it and loaded it with a copy from production. Recreated the publication.
Recreated the subscription. When I insert data into the tables, they are not
getting replicated. The subscription status shows "The concurrent snapshot
to publication <publication name> is not available because it has not been
fully generated or the log reader agent is not running to activate it. etc'.
Can you please guide me as to how to fix this issue. Your help and advise
will be very highly appreciated.
Thanking you in anticipation,
Yoga
Hi Yoga
You contacted me privately via email yesterday about this problem. I emailed
you a response and it bounced due to ip restrictions. I then replied again
via a diferent email address and did not hear back from you. Here is the
response I emailed to you.
This is an automatically generated Delivery Status Notification
Delivery to the following recipient failed permanently:
yoga_r@.sbcglobal.net
Technical details of permanent failure:
PERM_FAILURE: SMTP Error (state 8): 550 5.0.0 flpi096.sbcis.sbc.com Access
Denied. To request removal, send the complete error message, including your
ip addresses, in an E-mail to removeme@.sbc.sbcglobal.net
-- Original message --
Received: by 10.82.153.5 with SMTP id a5mr1813673bue.1165352214286;
Tue, 05 Dec 2006 12:56:54 -0800 (PST)
Received: by 10.82.161.10 with HTTP; Tue, 5 Dec 2006 12:56:54 -0800 (PST)
Message-ID: <10c19b1b0612051256y724d2bdfw4dddd9c10a12c8cd@.mail .gmail.com>
Date: Tue, 5 Dec 2006 15:56:54 -0500
From: "Hilary Cotter" <hilary.cotter@.gmail.com>
To: Yoga <yoga_r@.sbcglobal.net>
Subject: Re: Need your help
In-Reply-To: <028e01c718ac$2da491f0$a302810a@.ipsoft.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <028e01c718ac$2da491f0$a302810a@.ipsoft.net>
wow! you sure have a mess on your hands there.
I am not sure where you are now. I think it would be best to drop the
publication and subscriptions and make sure the jobs are gone as well.
You'll have to look in the jobs folder for them.
Then try to restore the database again and try again, this time with
new publication names.
If this doesn't work use sp_replicationdboption
'databasename','publish','true'
Good luck
Hilary
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
"Yoganarasimha Prasad" <yogakiran@.gmail.com> wrote in message
news:%23$Ng2VWGHHA.3976@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am contacting you all for your help in solving a problem with
> transactional replication in SQL server 2005. The setup is as follows:
> One publisher and three subscribers - all are SQL 2005
> Distributor database is in the same server as the publisher database.
> I had set it up and it was working fine. unfortunately, the publisher
> database was reloaded when I was on vacation. Now the replication is
> broken. I have tried the following
> Dropped the subscription.
> There was no publication to be dropped. so I dropped the database,
> recreated it and loaded it with a copy from production. Recreated the
> publication. Recreated the subscription. When I insert data into the
> tables, they are not getting replicated. The subscription status shows
> "The concurrent snapshot to publication <publication name> is not
> available because it has not been fully generated or the log reader agent
> is not running to activate it. etc'. Can you please guide me as to how to
> fix this issue. Your help and advise will be very highly appreciated.
> Thanking you in anticipation,
> Yoga
>
|||Hi Hilary,
Thanks a lot for the reply. I did what you asked me to do. I made sure that
there were no jobs running with this database name. Still I am not able to
get this to work. Just to make sure, I created a temporary database, created
1 table with primary key, created publication, created subscription and
walla, the replication is working for this database. Bur the actual database
is still refusing to co-operate. Any help will be much appreciated.
Yoga
408-441-5313
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eeplQbWGHHA.3952@.TK2MSFTNGP02.phx.gbl...
> Hi Yoga
> You contacted me privately via email yesterday about this problem. I
> emailed you a response and it bounced due to ip restrictions. I then
> replied again via a diferent email address and did not hear back from you.
> Here is the response I emailed to you.
> This is an automatically generated Delivery Status Notification
> Delivery to the following recipient failed permanently:
> yoga_r@.sbcglobal.net
> Technical details of permanent failure:
> PERM_FAILURE: SMTP Error (state 8): 550 5.0.0 flpi096.sbcis.sbc.com Access
> Denied. To request removal, send the complete error message, including
> your ip addresses, in an E-mail to removeme@.sbc.sbcglobal.net
> -- Original message --
> Received: by 10.82.153.5 with SMTP id a5mr1813673bue.1165352214286;
> Tue, 05 Dec 2006 12:56:54 -0800 (PST)
> Received: by 10.82.161.10 with HTTP; Tue, 5 Dec 2006 12:56:54 -0800 (PST)
> Message-ID: <10c19b1b0612051256y724d2bdfw4dddd9c10a12c8cd@.mail .gmail.com>
> Date: Tue, 5 Dec 2006 15:56:54 -0500
> From: "Hilary Cotter" <hilary.cotter@.gmail.com>
> To: Yoga <yoga_r@.sbcglobal.net>
> Subject: Re: Need your help
> In-Reply-To: <028e01c718ac$2da491f0$a302810a@.ipsoft.net>
> MIME-Version: 1.0
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
> Content-Transfer-Encoding: 7bit
> Content-Disposition: inline
> References: <028e01c718ac$2da491f0$a302810a@.ipsoft.net>
>
> wow! you sure have a mess on your hands there.
> I am not sure where you are now. I think it would be best to drop the
> publication and subscriptions and make sure the jobs are gone as well.
> You'll have to look in the jobs folder for them.
> Then try to restore the database again and try again, this time with
> new publication names.
> If this doesn't work use sp_replicationdboption
> 'databasename','publish','true'
> Good luck
> Hilary
>
> --
> 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
>
> "Yoganarasimha Prasad" <yogakiran@.gmail.com> wrote in message
> news:%23$Ng2VWGHHA.3976@.TK2MSFTNGP05.phx.gbl...
>
|||My mistake I meant for you to try this.
sp_replicationdboption 'databasename','publish','False'
Then run your publication and subscription scripts and try again.
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
"Yoganarasimha Prasad" <yogakiran@.gmail.com> wrote in message
news:OMOu1eWGHHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi Hilary,
> Thanks a lot for the reply. I did what you asked me to do. I made sure
> that there were no jobs running with this database name. Still I am not
> able to get this to work. Just to make sure, I created a temporary
> database, created 1 table with primary key, created publication, created
> subscription and walla, the replication is working for this database. Bur
> the actual database is still refusing to co-operate. Any help will be much
> appreciated.
> Yoga
> 408-441-5313
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eeplQbWGHHA.3952@.TK2MSFTNGP02.phx.gbl...
>
|||Hi Hilary,
I executed the command and recreated the publication and subscription. But
the subscription view detail window still shows the same message "The
concurrent snapshot for publication <publication name> is not available
because it has not been fully generated or the Log Reader Agent is not
running to activate it. If generation of the concurrent snapshot was
interrupted, the Snapshot Agent for the publication m" I made sure that the
snapshot was 100% complete before I created the subscription. I also see
that the logread.exe is running. I am literally against the wall.
Yoga
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ubVJCkXGHHA.420@.TK2MSFTNGP02.phx.gbl...
> My mistake I meant for you to try this.
> sp_replicationdboption 'databasename','publish','False'
>
> Then run your publication and subscription scripts and try again.
> --
> 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
>
> "Yoganarasimha Prasad" <yogakiran@.gmail.com> wrote in message
> news:OMOu1eWGHHA.4112@.TK2MSFTNGP04.phx.gbl...
>
|||I have tried to use push subscription and it did not work either. Any help
will be much appreciated.
Thank you,
Yoga
"Yoganarasimha Prasad" <yogakiran@.gmail.com> wrote in message
news:uKCXbaYGHHA.3976@.TK2MSFTNGP05.phx.gbl...
> Hi Hilary,
> I executed the command and recreated the publication and subscription. But
> the subscription view detail window still shows the same message "The
> concurrent snapshot for publication <publication name> is not available
> because it has not been fully generated or the Log Reader Agent is not
> running to activate it. If generation of the concurrent snapshot was
> interrupted, the Snapshot Agent for the publication m" I made sure that
> the snapshot was 100% complete before I created the subscription. I also
> see that the logread.exe is running. I am literally against the wall.
> Yoga
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ubVJCkXGHHA.420@.TK2MSFTNGP02.phx.gbl...
>
|||Have you tried sp_removedbrepication?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
sql

Need help

Hi,
We have publisher and remote distributor with lot of transactional and
merge publications. (SQL2K SP3)
Recently we migrated our publisher to a new machine.
Today I renamed my old publisher server and tried to disable subscribers on
it (because everything works on a new publisher already).
When I did that via EM, all my replication agents on a distributor failed
(probably the old publisher wrote somethig to the distributor).
Then I marked (enabled) all subscribers back on that server (old publisher).
After that all merge replications became good again. Buh the transactional
ones don't work.
I get an error "The remote procedure call failed and did not execute"
Need help.
Thanks
Hi,
How did you rename the old publihser from EM? I'm not familiar with that capability.
-Matt
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

Need for Distributor server in existing Replication Setup

We have a setup with one Publisher Server and 15 Subscribers with merge replication configured for all subscribers. The current Database size is approximately 3 GB expected to grow to 10 GB in next 1 year. We wanted to know what benefits would we incur if we add an additional seperate distributor server (hardware box). Also, what is the approx. size of a database where a seperate distributor server (hardware box) is recommended.

Thanks in advanceRead up on replication in SQL Books Online (provided with SQL Server). It covers a lot of the trade-offs in detail. Without knowing a LOT more about your configuration, plans, etc. I don't know how to give you a simple answer.

Typically, when you have one publisher with only 15 subscribers, the load isn't too heavy unless the publisher is stressed. The primary advantage comes from improved security, better network performance, and better managability. As your system grows (more data and more subscribers), then the performance benefits will start to come into play.

Again, without a lot more understanding of what you are doing now and what you plan to do in the near future, I really can't give you a straightforward answer.

-PatP|||Sometimes you make me crack up, I swear. ..."only 15 subscribers"... You'll see an immediate benefit even if you have just 1 subscriber...man, how many of those have you set up?|||Nice to know that you find me good for something!

I've set up about six production replication systems, a few hundred training systems, and I'm not really sure how many test systems. As I said in my first post, without knowing a lot more about what the poster wants and what they've actually got, any observation I can offer is only in very general terms.

Based on my experience, if you set up at least one NIC for replication on a relatively beefy box, it can handle a reasonable user load along with the replication... If you try to skimp on bandwidth, RAM, or processing power, you can certainly swamp any box.

It seems obvious to me that having a separate distributor is better than having one box do both, so on that point we certainly agree. I guess the question is where do you see the performance "knee" due to the additional load imposed by distribution. Without knowing a lot more, I don't see how you can offer much insight there.

-PatP|||Hey, I just felt that having 15 subscribers is enough to predict that a dedicated distributor is well due, that's all ;)

Need Datawarehouse

I currently have one server acting as a publisher and distributor. Merge
replication is setup where our shoppes replicate their sales data to the
central server once a week. The home office would like to run consolidated
sales reports on the data. My thought is to keep them off the publisher
database and create a new reporting database. I can then setup the
reporting database as another subscriber. The users can then run reports
against the reporting database. This means my one server will be a
publisher/distributor and subscriber. Is this too much for one server to
handle?
Tina,
it really depends on the overall load. You can make life slightly easier
having a remote distributor, but in your case I'd advise log-shipping to
another server for reporting requirements. This way the report data creation
has no real impact on the production server, as logs are normally created
anyway, rather than having another merge agent active all the time.
HTH,
Paul Ibison
|||Nope. I'd suggest doing what I outlined for several other customers. Setup
a transactional publication against that data on your publisher and then put
a subscriber on another machine where you users can run reports.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Hi Mike,
At this point, I'm limited to just this one server so I need to make the
sure I make the right decisions. There will only be two users running
reports or ad-hoc queries during the day. The shoppes synchronize after
hours between 9:00pm - 2:00am so I don't anticipate the merge agents
struggling with queries for resources.
I appreciate your help!
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:enjxwTvLEHA.556@.tk2msftngp13.phx.gbl...
> Nope. I'd suggest doing what I outlined for several other customers.
Setup
> a transactional publication against that data on your publisher and then
put
> a subscriber on another machine where you users can run reports.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
|||As noted in my reply to Mike, I'm limited to just one server so I need to
make the best of what I have. Since it's a new deployment it's hard for
me to determine the exact load. A year or so down the road I'll have over
250 subscribers and then the whole ball game changes.
Within the next two months I'm anticipating about 35 subscribers on board.
I appreciate your help.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23xspWMvLEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Tina,
> it really depends on the overall load. You can make life slightly easier
> having a remote distributor, but in your case I'd advise log-shipping to
> another server for reporting requirements. This way the report data
creation
> has no real impact on the production server, as logs are normally created
> anyway, rather than having another merge agent active all the time.
> HTH,
> Paul Ibison
>
|||OK, but remember this, everything is hard coded. If you run out of
resources on the publisher and need to move the distributor to another
machine, you will have to completely remove replication from ALL machines
and completely redeploy from scratch. Doing that with 1 - 2 machines is bad
enough, trying to do it with 250 machines when you have an environment
deployed that everyone is using is an entirely different matter altogether.
Not saying it can't be done, but replication is one thing you don't do last
minute planning on and survive to tell about it.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Your point is well taken. Thanks Mike!
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:uCSnfHCMEHA.1192@.TK2MSFTNGP11.phx.gbl...
> OK, but remember this, everything is hard coded. If you run out of
> resources on the publisher and need to move the distributor to another
> machine, you will have to completely remove replication from ALL machines
> and completely redeploy from scratch. Doing that with 1 - 2 machines is
bad
> enough, trying to do it with 250 machines when you have an environment
> deployed that everyone is using is an entirely different matter
altogether.
> Not saying it can't be done, but replication is one thing you don't do
last
> minute planning on and survive to tell about it.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>

Need Clarification - merge replication

Hi,
I need to add new tables to merge replication.
These are the steps I have followed,
1. Executed the table script both in Publisher & Subscriber
2. Add 'rowguid' column in that table (Publisher & Subscriber)
3. In publication properties-->Articles, included the new table and
snapshot was automatically started.
(a) Is it the correct way to add tables to merge replication or is there any
better way?
(b) Should I stop the merge agent during this process?
Please advise.
Thanks,
Soura.
Soura,
unless you're doing a nosync initialization, it is normal to add the
articles to the publication, run the snapshot agent then the merge agent. A
full snapshot is created, but only the new article will get propagated.
There's no need to deal with the guid column - it'll get created for you if
a guid (rowguid) isn't there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)