New to Replication

I have a SQL 2000 database used in a data entry / transactional mode. We
have few users, but they do occassionally run reports which are server
intensive. It is a small business. We would like to begin using the data in
the database on the website. I'm wondering if I should replicate the DB on
the same server. Does that really provide any performance protection for the
data entry folks? We don't think the traffic on the Internet will be intense
to begin with because it will only be for members. Does a snapshot
replication make sense (we only need a refresh each night) or should I just
let the web hit the data entry db? Thanks
Hi,
I understand that some of your users occasionally ran reports on your
server. The operations impacted your server performance. You want to know
if you can replicate the DB on the same server to improve the performance.
If I have misunderstood, please let me know.
Generally producing report is CPU, memory and I/O intensive, so I recommend
that you replicate your database to a different server (dedicated for
report server) and have your web application use this report server for
producing report. Since reports are for history data, snapshot replication
is enough.
For implementing replication, please refer to:
Implementing Replication (Enterprise Manager)
http://msdn2.microsoft.com/en-us/library/aa216618(sql.80).aspx
Implementing Master-Slave Snapshot Replication Using SQL Server
http://msdn2.microsoft.com/en-us/library/ms998561.aspx
Planning for Snapshot Replication
http://msdn2.microsoft.com/en-us/library/aa237481(SQL.80).aspx
If you have any other questions or concerns, please let me know. Have a
nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||That answers my question. Thanks Charles!
"Charles Wang[MSFT]" wrote:

> Hi,
> I understand that some of your users occasionally ran reports on your
> server. The operations impacted your server performance. You want to know
> if you can replicate the DB on the same server to improve the performance.
> If I have misunderstood, please let me know.
> Generally producing report is CPU, memory and I/O intensive, so I recommend
> that you replicate your database to a different server (dedicated for
> report server) and have your web application use this report server for
> producing report. Since reports are for history data, snapshot replication
> is enough.
> For implementing replication, please refer to:
> Implementing Replication (Enterprise Manager)
> http://msdn2.microsoft.com/en-us/library/aa216618(sql.80).aspx
> Implementing Master-Slave Snapshot Replication Using SQL Server
> http://msdn2.microsoft.com/en-us/library/ms998561.aspx
> Planning for Snapshot Replication
> http://msdn2.microsoft.com/en-us/library/aa237481(SQL.80).aspx
> If you have any other questions or concerns, please let me know. Have a
> nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
>
>
>
>
sql

New table added to p-t-p transactional publication does not replicate - why?

Hello,

We are new to replication and are testing it in our development environment. We have a peer-to-peer transactional publication on our three servers. The single table in the original publication replicated fine to the two subscribing servers. We next added a new table (article) to the publication. Adding it to the original publication worked fine but the table did not replicate to the other servers. (We previously had changed the schema of the original table and the schema changes replicated properly.) We attempted to recreate the snapsnot using the "View Snapshot Agent Status" option. Clicking the Start button resulted in the display of this message: "[0%] A snapshot was not generated because no subscriptions needed initialization."

This seems odd because a new table was added to the publication and Microsoft help states that the snapshot must be rebuilt. I have read other topics that refer to a @.immediate_sync property that must be set to zero. I'm not sure if this is our problem or even how to set this value. Meanwhile, the other servers, as viewed through the Replication Monitor, are complaining that their snapshots do not match the publication snapshot.

Can someone point me in the right direction?

Thanks,

BCB

Hi,

P2P replication should not be initialized using snapshot agent. The subscription must be initialized either manually or using backup/restore.

To add a new article into an existing P2P topology, following the instruction in this link (http://msdn2.microsoft.com/en-us/library/ms146867.aspx). Basically you need to ensure all nodes have the same schema/data for the table you would llike to add. And then call sp_addarticle at each node. P2P topology should be quiesced before it.

Thanks,

Peng

New subscription wizard never completes or even errors (SQL 2005 Trans repl with Queued Up

I am testing replication (Transactional with Queued Updating) between SQL
Server 2005 Developer Edition (which is on a domain) and SQL Express (which
is on a machine on same LAN but in a workgroup). I have configured the
publisher as its own distributor and created a publication consisting of 2
views (1 indexed, 1 regular). My problem is that when I run the New
Subscription Wizard, after clicking Finish the process hangs forever on the
first step ("Creating Subscription For ....."). No errors appear, nothing.
Clicking Stop has no effect.
Both views are tiny, only returning half a dozen rows. I have also tried
with a publication containing only one small table, and the same thing
occurs.
The publisher is clearly able to connect to the subscriber because the
linked server is created on the subscriber successfully. There is no sign of
a subscription however on the subscriber.
Here is the configuration for the subscription (following steps in New
Subscription Wizard):
Push subscription
Subscription database is new blank database
Distribution Agent Security: Run under SQL Agent service account, Connects
to distributor by impersonation, Connects to subscriber using a SysAdmin SQL
login
Schedule: runs continuously
Queued updating
Create new linked server using same SysAdmin SQL login as above
Initialize subscription immediately
What could be wrong?
Can you script out your publication creation steps and then run it manually
through Query Analyzer or the SSMS and see where it hangs. This should give
you some indication of where the problem is.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I am testing replication (Transactional with Queued Updating) between SQL
>Server 2005 Developer Edition (which is on a domain) and SQL Express (which
>is on a machine on same LAN but in a workgroup). I have configured the
>publisher as its own distributor and created a publication consisting of 2
>views (1 indexed, 1 regular). My problem is that when I run the New
>Subscription Wizard, after clicking Finish the process hangs forever on the
>first step ("Creating Subscription For ....."). No errors appear, nothing.
>Clicking Stop has no effect.
> Both views are tiny, only returning half a dozen rows. I have also tried
> with a publication containing only one small table, and the same thing
> occurs.
> The publisher is clearly able to connect to the subscriber because the
> linked server is created on the subscriber successfully. There is no sign
> of a subscription however on the subscriber.
> Here is the configuration for the subscription (following steps in New
> Subscription Wizard):
> Push subscription
> Subscription database is new blank database
> Distribution Agent Security: Run under SQL Agent service account, Connects
> to distributor by impersonation, Connects to subscriber using a SysAdmin
> SQL login
> Schedule: runs continuously
> Queued updating
> Create new linked server using same SysAdmin SQL login as above
> Initialize subscription immediately
> What could be wrong?
>
|||The step that hangs is the last step - the execution of sp_link_publication
on the subscriber:
exec sp_link_publication @.publisher = N'LAURENCEN\SQL2005', @.publisher_db =
N'RMIS_QA', @.publication = N'test views', @.distributor =
N'LAURENCEN\SQL2005', @.security_mode = 0, @.login = N'xxxxx', @.password =
N'xxxx'
In the Application Event log the following error appears:
MS DTC is unable to communicate with MS DTC on a remote system. No common
RPC protocol is supported between the two systems. Please ensure that one
or more of the following RPC protocols are common to both systems: TCP/IP,
SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:204, Pid: 904, CmdLine:
C:\WINNT\System32\msdtc.exe
I have checked that the MSDTC service is running on publisher and
subscriber. The publisher server is Windows XP SP2 and from googling it
looks like DTC is not given network access by default, however I have
enabled the required settings and the error persists:
In Admin tools.Component Services or via Component Services MSC Snap-in
- right-click the appropriate computer node and select properties
- on the MSDTC tab click Security Configuration button
- in addition to the Network DTC Access being ticked, tick Allow Remote
Clients, Allow Inbound, Allow Outbound
- restart MSDTC
The subscriber server is Windows 2000 Workstation, and it doesn't look like
there are any DTC settings to configure.
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I am testing replication (Transactional with Queued Updating) between SQL
>Server 2005 Developer Edition (which is on a domain) and SQL Express (which
>is on a machine on same LAN but in a workgroup). I have configured the
>publisher as its own distributor and created a publication consisting of 2
>views (1 indexed, 1 regular). My problem is that when I run the New
>Subscription Wizard, after clicking Finish the process hangs forever on the
>first step ("Creating Subscription For ....."). No errors appear, nothing.
>Clicking Stop has no effect.
> Both views are tiny, only returning half a dozen rows. I have also tried
> with a publication containing only one small table, and the same thing
> occurs.
> The publisher is clearly able to connect to the subscriber because the
> linked server is created on the subscriber successfully. There is no sign
> of a subscription however on the subscriber.
> Here is the configuration for the subscription (following steps in New
> Subscription Wizard):
> Push subscription
> Subscription database is new blank database
> Distribution Agent Security: Run under SQL Agent service account, Connects
> to distributor by impersonation, Connects to subscriber using a SysAdmin
> SQL login
> Schedule: runs continuously
> Queued updating
> Create new linked server using same SysAdmin SQL login as above
> Initialize subscription immediately
> What could be wrong?
>
|||Is this an upgrade from SQL 2000? There are some issues with a remote
distributor in an upgrade. Basically the repl_distributor remote server is
modified to a linked server and there have been cases where you have to
update the passwords to get this to work. AFAIK - MSDTC is not involved in
queued replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:O%237HO7oAHHA.5068@.TK2MSFTNGP02.phx.gbl...
> The step that hangs is the last step - the execution of
> sp_link_publication on the subscriber:
> exec sp_link_publication @.publisher = N'LAURENCEN\SQL2005', @.publisher_db
> = N'RMIS_QA', @.publication = N'test views', @.distributor =
> N'LAURENCEN\SQL2005', @.security_mode = 0, @.login = N'xxxxx', @.password =
> N'xxxx'
> In the Application Event log the following error appears:
> MS DTC is unable to communicate with MS DTC on a remote system. No common
> RPC protocol is supported between the two systems. Please ensure that one
> or more of the following RPC protocols are common to both systems:
> TCP/IP, SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:204, Pid: 904,
> CmdLine: C:\WINNT\System32\msdtc.exe
> I have checked that the MSDTC service is running on publisher and
> subscriber. The publisher server is Windows XP SP2 and from googling it
> looks like DTC is not given network access by default, however I have
> enabled the required settings and the error persists:
> In Admin tools.Component Services or via Component Services MSC Snap-in
> - right-click the appropriate computer node and select properties
> - on the MSDTC tab click Security Configuration button
> - in addition to the Network DTC Access being ticked, tick Allow Remote
> Clients, Allow Inbound, Allow Outbound
> - restart MSDTC
> The subscriber server is Windows 2000 Workstation, and it doesn't look
> like there are any DTC settings to configure.
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>
|||I figured it out after a long search.
MSDTC is involved, at least to set up the subscription. MSDTC in Windows XP
(and Win 2003 also I think) has some network access settings that are set to
prevent any access from outside by default. I mentioned these before.
However, in addition to setting these settings to allow network access,
there is a security setting for RPC, which I missed. This is the registry
key:
HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Win dows
NT\RPC\RestrictRemoteClients
This needs to exist and have a value of 0 (DWORD) if the subscriber is
Windows 2000.
For more info read:
http://blogs.msdn.com/florinlazar/archive/2004/06/18/159127.aspx
http://www.microsoft.com/technet/prodtechnol/winxppro/maintain/sp2netwk.mspx#XSLTsection128121120120
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23tmOUdqAHHA.3536@.TK2MSFTNGP03.phx.gbl...
> Is this an upgrade from SQL 2000? There are some issues with a remote
> distributor in an upgrade. Basically the repl_distributor remote server is
> modified to a linked server and there have been cases where you have to
> update the passwords to get this to work. AFAIK - MSDTC is not involved in
> queued replication.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:O%237HO7oAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>

New PULL setup problems

I have read & read & read and cannot seem to find the answer to my issue.
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?
To use the distribution database on the subscriber you will have to
1) enable the subscriber for replication
2) go to tools, replication, configure publishers, subscribers, distributor on the subscriber and click on the publisher tab. And then click on the publisher tab, and enable the remote publisher to use this distributor. Click on the three ellipses to the right of your publisher and disable the administrative link password.
3) connect to your publisher and disable replication. Then recreate it and configure it to use the remote distributor on the subscriber.
4) recreate your publication and pull subscription.
Your error sounds like your pull subscription does not have rights to access the snapshot share. Either add the account that the SQL Agent account runs under on the subscriber to the local admin group on the publisher, or change the snapshot share to a folder which the SQL Agent account on the subscriber can access.
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23a3jTrxnEHA.648@.tk2msftngp13.phx.gbl...
I have read & read & read and cannot seem to find the answer to my issue.
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?
|||# 3 - connect to your publisher and disable replication.
What if I have multiple publications of 1 table going here & 1 table going there and want these publications to continue to use the distributor on the publishing server?
Is that ok?
For instance; 1 publication publishes only 1 table from database a to database b on the same server and uses this server as the distributor.
Then the publication I am now trying to Pull & have the distributor be remote, will pull multiple tables from database a to the database on the remote server.
Any problems with that?
The disable replication concerned me, I don't want to affect any of the other publications by setting up the new publication to run at the remote distributor & pull subscription.
I sure hope I am making sense....
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:e%23ah21xnEHA.3680@.TK2MSFTNGP10.phx.gbl...
To use the distribution database on the subscriber you will have to
1) enable the subscriber for replication
2) go to tools, replication, configure publishers, subscribers, distributor on the subscriber and click on the publisher tab. And then click on the publisher tab, and enable the remote publisher to use this distributor. Click on the three ellipses to the right of your publisher and disable the administrative link password.
3) connect to your publisher and disable replication. Then recreate it and configure it to use the remote distributor on the subscriber.
4) recreate your publication and pull subscription.
Your error sounds like your pull subscription does not have rights to access the snapshot share. Either add the account that the SQL Agent account runs under on the subscriber to the local admin group on the publisher, or change the snapshot share to a folder which the SQL Agent account on the subscriber can access.
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23a3jTrxnEHA.648@.tk2msftngp13.phx.gbl...
I have read & read & read and cannot seem to find the answer to my issue.
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?
|||Basically you are hosed. Existing publications on the publisher will have to be migrated to the new distributor.
Why do you want to use a remote distributor? If it is to offload the extra processing from the new publication subscription you might want to consider remote activation.
For plain pull subscriptions that distributor on the subscriber will only contain historical information. It won't contain the transactions and commands to be replicated.
Perhaps at this point you could tell us exactly what you are trying to accomplish so we can consider all the options available to you.
"JLS" <jlshoop@.hotmail.com> wrote in message news:O%23kH290nEHA.2300@.TK2MSFTNGP10.phx.gbl...
# 3 - connect to your publisher and disable replication.
What if I have multiple publications of 1 table going here & 1 table going there and want these publications to continue to use the distributor on the publishing server?
Is that ok?
For instance; 1 publication publishes only 1 table from database a to database b on the same server and uses this server as the distributor.
Then the publication I am now trying to Pull & have the distributor be remote, will pull multiple tables from database a to the database on the remote server.
Any problems with that?
The disable replication concerned me, I don't want to affect any of the other publications by setting up the new publication to run at the remote distributor & pull subscription.
I sure hope I am making sense....
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:e%23ah21xnEHA.3680@.TK2MSFTNGP10.phx.gbl...
To use the distribution database on the subscriber you will have to
1) enable the subscriber for replication
2) go to tools, replication, configure publishers, subscribers, distributor on the subscriber and click on the publisher tab. And then click on the publisher tab, and enable the remote publisher to use this distributor. Click on the three ellipses to the right of your publisher and disable the administrative link password.
3) connect to your publisher and disable replication. Then recreate it and configure it to use the remote distributor on the subscriber.
4) recreate your publication and pull subscription.
Your error sounds like your pull subscription does not have rights to access the snapshot share. Either add the account that the SQL Agent account runs under on the subscriber to the local admin group on the publisher, or change the snapshot share to a folder which the SQL Agent account on the subscriber can access.
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23a3jTrxnEHA.648@.tk2msftngp13.phx.gbl...
I have read & read & read and cannot seem to find the answer to my issue.
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?
|||I have a new server which will serve as the reporting server. I want to replicate all the transactions from my ERP database to a database on the reporting server to offload this activity on the production server.
I don't have to use a remote distributor. I am new to this and basically feeling my way around to find the absolute best solution. (I, for one, am anxiously awaiting the book release)
From what I have read thus far, it was suggested that running the Distributor & Pulling the subscription would result in better performance for both boxes.
Overall my goal is to enhance performance on the production server, the OLTP system. I thought replicating to a reporting server would be a significant step in accomplishing this task.
If you had 2 servers, 1 for production transactions and 1 for reporting, what would be your choices?
The other thing is that I am still experiencing the 8198 error message.
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
I added the accounts on both boxes to the local admins group, therefore, they have access to everything on that box. I'm really stuck on this one!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:%23Rt9Vx3nEHA.1296@.TK2MSFTNGP09.phx.gbl...
Basically you are hosed. Existing publications on the publisher will have to be migrated to the new distributor.
Why do you want to use a remote distributor? If it is to offload the extra processing from the new publication subscription you might want to consider remote activation.
For plain pull subscriptions that distributor on the subscriber will only contain historical information. It won't contain the transactions and commands to be replicated.
Perhaps at this point you could tell us exactly what you are trying to accomplish so we can consider all the options available to you.
"JLS" <jlshoop@.hotmail.com> wrote in message news:O%23kH290nEHA.2300@.TK2MSFTNGP10.phx.gbl...
# 3 - connect to your publisher and disable replication.
What if I have multiple publications of 1 table going here & 1 table going there and want these publications to continue to use the distributor on the publishing server?
Is that ok?
For instance; 1 publication publishes only 1 table from database a to database b on the same server and uses this server as the distributor.
Then the publication I am now trying to Pull & have the distributor be remote, will pull multiple tables from database a to the database on the remote server.
Any problems with that?
The disable replication concerned me, I don't want to affect any of the other publications by setting up the new publication to run at the remote distributor & pull subscription.
I sure hope I am making sense....
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:e%23ah21xnEHA.3680@.TK2MSFTNGP10.phx.gbl...
To use the distribution database on the subscriber you will have to
1) enable the subscriber for replication
2) go to tools, replication, configure publishers, subscribers, distributor on the subscriber and click on the publisher tab. And then click on the publisher tab, and enable the remote publisher to use this distributor. Click on the three ellipses to the right of your publisher and disable the administrative link password.
3) connect to your publisher and disable replication. Then recreate it and configure it to use the remote distributor on the subscriber.
4) recreate your publication and pull subscription.
Your error sounds like your pull subscription does not have rights to access the snapshot share. Either add the account that the SQL Agent account runs under on the subscriber to the local admin group on the publisher, or change the snapshot share to a folder which the SQL Agent account on the subscriber can access.
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23a3jTrxnEHA.648@.tk2msftngp13.phx.gbl...
I have read & read & read and cannot seem to find the answer to my issue.
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?

New Publication Error

Hi All,
A couple of months ago, I setup a transactional replication between 2
servers. In the last week, the publisher stopped replicating to the
subscriber for some unknown reason. When I was troubleshooting the problem,
I tried to add a new publication and I received the error attached in the
screenshot.
I ran the DBCC CHECKDB on that DB and the results were ok.
Any help would be greatly appreciated.
Regards,
JN
Can't see the screenshot using the web interface to the newsgroup. Can you
post up the error message for us...
Cheers,
Paul Ibison
|||Sure thing...Error as follows:
"New Publication Wizard encountered one or more errors while retrieving the
list of databases on the 'servername'. The list of databases may not be
complete.
Additional Information:
An exception occured while executing a T-SQL statement or batch
(Microsoft.SqlServer.ConnectionInfo)
The operating system returned error 21 (The device is not ready) to SQL
server during a read at offset 0x000000614d2000 in
file F:\TST\TSTDAT.mdf (not sure what this DB is?). Addtional messages
in the SQL Server error log and system event log my provide more detail.
This is a severe system level error condition that threatens database
integrity and must be corrected
immediatly. Complete a full database consistency check (DBCC CHECKDB).
This error can be caused by many factors; for
more information, SQL Server Books Online (MS SQL Error: 823)"
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:D538F184-0AC3-4CD2-AC4D-0029B4E0ED7E@.microsoft.com...
> Can't see the screenshot using the web interface to the newsgroup. Can you
> post up the error message for us...
> Cheers,
> Paul Ibison
>
|||I'd look to see what database the file F:\TST\TSTDAT.mdf corresponds to, then
run DBCC CHECKDB on this database. The info should be in sysaltfiles.
HTH,
Paul Ibison

New Publication - Snapshot Error

Help!
Some genius cancelled a replication job that was very close to completing the Initial Snapshot of a database being replicated (Transactional) from one server to another.
Is the only thing necessary to do a 'Reinitialize' of this publication?
Is there any way around having the tables locked when the Snapshot is being generated?
(This is why the doofus killed the job)
Any help is greatly appreciated!
You might be able to get away with a concurrent snapshot if you are using transactional replication. On your publication properties, clcik the snapshot tab, and select concurrent access during snapshot generation.
Another Snapshot will be generated but there will be far less locking.
If you can kick all your users off the database you can DTS the data over. This is faster than a snapshot.
To get this to work you should
1) create a new database.
2) script out the tables you are replicating, and any other objects you are replicating, and run this script in the new database.
3) publish this database and objects to a second new database on the same server.
4) script out the objects and the stored procedures and then run this in the subscriber.
This way your objects will be the way replication wants them and you will have the necessary replication stored procedures. Then do your DTS and then do a no-sync subscription.
DTS can be twice as fast as a snapshot generation and distribution.
It is critical that you be able to prevent any updates from happening to your publication database while you do this.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JLS" wrote:

> Help!
> Some genius cancelled a replication job that was very close to completing the Initial Snapshot of a database being replicated (Transactional) from one server to another.
> Is the only thing necessary to do a 'Reinitialize' of this publication?
> Is there any way around having the tables locked when the Snapshot is being generated?
> (This is why the doofus killed the job)
> Any help is greatly appreciated!
|||Thanx!
Any way to buy a copy of your book before August 15?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ED801206-889D-42F6-A590-663BC63F3BBA@.microsoft.com...
> You might be able to get away with a concurrent snapshot if you are using
transactional replication. On your publication properties, clcik the
snapshot tab, and select concurrent access during snapshot generation.
> Another Snapshot will be generated but there will be far less locking.
> If you can kick all your users off the database you can DTS the data over.
This is faster than a snapshot.
> To get this to work you should
> 1) create a new database.
> 2) script out the tables you are replicating, and any other objects you
are replicating, and run this script in the new database.
> 3) publish this database and objects to a second new database on the same
server.
> 4) script out the objects and the stored procedures and then run this in
the subscriber.
> This way your objects will be the way replication wants them and you will
have the necessary replication stored procedures. Then do your DTS and then
do a no-sync subscription.
> DTS can be twice as fast as a snapshot generation and distribution.
> It is critical that you be able to prevent any updates from happening to
your publication database while you do this.[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "JLS" wrote:
completing the Initial Snapshot of a database being replicated
(Transactional) from one server to another.[vbcol=seagreen]
being generated?[vbcol=seagreen]
|||no. There may be some proofs out in a month.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message
news:%23qw9qmIWEHA.1380@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanx!
> Any way to buy a copy of your book before August 15?
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:ED801206-889D-42F6-A590-663BC63F3BBA@.microsoft.com...
using[vbcol=seagreen]
> transactional replication. On your publication properties, clcik the
> snapshot tab, and select concurrent access during snapshot generation.
over.[vbcol=seagreen]
> This is faster than a snapshot.
> are replicating, and run this script in the new database.
same[vbcol=seagreen]
> server.
> the subscriber.
will
> have the necessary replication stored procedures. Then do your DTS and
then[vbcol=seagreen]
> do a no-sync subscription.
> your publication database while you do this.
> completing the Initial Snapshot of a database being replicated
> (Transactional) from one server to another.
publication?
> being generated?
>