remove article from trans replication?

Hi,
Using sql server 2005, we've got transactional replication going between two
servers. if i need to remove a couple of tables from the replication, do i
have to reinitialize the snapshot every time i do that? or is there an
easier way to achieve this?
thanks for any help on this,
Fred
Hi Paul, and thanks for your response,
I've checked out the page your referenced, and because I'm just learning
about replication, it's not clear to me if it applies in our situation, and
also to Sql Server 2005. So, allow me to ask again with more details.
In our next release procedure, we're changing the name of a dozen fields in
a dozen tables. some of them are primary key fields. we're also dropping a
couple of constraints. (I had thought we were dropping tables but just
learned differently.) Can these things be done without reinitializing the
snapshot.
what I've done this time, is to remove all the tables from replication, and
reinit the snapshot. Now the developer can make the schema changes
mentioned above, and then immediately after the release, I'll add the tables
back into the replication and reinit the snapshot again. Not pretty, but it
works. but that's why I'm asking about a way to make changes and have them
propagated thru replication, without breaking the replication.
I hope this is clear, and makes some sense! thanks for your thoughts.
Fred
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:enB1dfLsHHA.3492@.TK2MSFTNGP02.phx.gbl...
> Please take a look here: http://www.replicationanswers.com/AddColumn.asp
> HTH,
> Paul Ibison
>
|||Hi Paul,
If you're still reading this, might it work to turn off DDL replication for
the publication, make the changes, then turn DDL replication back on?
I'm starting to implement via stored procedures now...
thanks,
Fred
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OGPTFsSsHHA.3480@.TK2MSFTNGP02.phx.gbl...
> The way involving the least amount of work for SQL Server would be to drop
> the subscriptions to those tables which you are going to change radically
> (several fields and/or changing the PK fields) then drop the articles.
> Make the changes then add the articles, add the subscription and then run
> the snapshot agent and distribution agent. This effectively is a
> reinitialization of just those articles you are changing. For minor
> changes you'd use sp_repladdcolumn and sp_repldropcolumn.
> HTH,
> Paul Ibison
>

Remote Server / Linked Server

I have transactional replication working and it created a remote server
to be used for replication. I am not able to insert/delete/update
between the two servers through the "remote server" connection. I need
to use a linked server, do I need to set up this linked server prior to
configuring any replication so a remote server isn't created during
configuration? Is there something else I can do instead of this.
Thanks
sp_serveroption 'remoteServerName','data access',true
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
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142538888.208897.59030@.e56g2000cwe.googlegro ups.com...
>I have transactional replication working and it created a remote server
> to be used for replication. I am not able to insert/delete/update
> between the two servers through the "remote server" connection. I need
> to use a linked server, do I need to set up this linked server prior to
> configuring any replication so a remote server isn't created during
> configuration? Is there something else I can do instead of this.
> Thanks
>
|||I changed the data access to true, but now I get the following error
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
The security properties are set to: "Be made using the login's current
security context"
I have sysadmin priviledges so I shouldn't be restricted by that. I
can get it to work and pull data, if I change the security properties
to use the sa login and password, but that isn't secure enough. What
options do I have. Thanks for your time.
|||Enter the sa account and password on the linked server.
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
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142872805.003828.93830@.t31g2000cwb.googlegro ups.com...
>I changed the data access to true, but now I get the following error
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> The security properties are set to: "Be made using the login's current
> security context"
> I have sysadmin priviledges so I shouldn't be restricted by that. I
> can get it to work and pull data, if I change the security properties
> to use the sa login and password, but that isn't secure enough. What
> options do I have. Thanks for your time.
>
|||Maybe I didn't state my question correctly. I am wanting to use the
security property of: "Be made using the login's current
security context" so it can limit the users priviledges to the other
server. When I do this I get the following error.
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection
I was able to use the sa login and password, but that allowed everyone
to have unlimited access to the linked server.
How do I get the security property of: "Be made using the login's
current security context" to work without getting the error. Thanks
|||I don't believe you can as you are logging on using Windows Authentication,
and it appears that the remote server is not part of the domain. Perhaps try
pass through authentication. By any chance is the remote server across the
internet?
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
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142879939.773655.304440@.z34g2000cwc.googlegr oups.com...
> Maybe I didn't state my question correctly. I am wanting to use the
> security property of: "Be made using the login's current
> security context" so it can limit the users priviledges to the other
> server. When I do this I get the following error.
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection
>
> I was able to use the sa login and password, but that allowed everyone
> to have unlimited access to the linked server.
>
> How do I get the security property of: "Be made using the login's
> current security context" to work without getting the error. Thanks
>

Remote Distributor and Subscriber on same machine - is that OK?

Hi,

Server A houses the Production DB which services an OLTP system. Transactional replication is configured on this server which has two subscribers - on two separate servers (servers B and C). The Distribution database currently resides on Server A (which is also the Publisher).

Server B is on the same LAN as Server A (on the same rack). I'm considering moving the Distribution database from Server A to Server B to offload some of the processing overhead from Server A.

Are there any gotchas/performance problems associated with having the Distribution database (remote distributor) and the Subscriber on the same server (server B) in a transaction replication topology?

Please advise or point me to the appropriate documentation - I haven't found anything that addresses this specific question...thanks in advance,

Smitha

This is actually a rather popular configuration (at least the ones that I have seen) inside Microsoft as this allows you to upgrade the Distributor and Subscriber to a new release together and then use the Subscriber as a test bed for the new release while receiving continuous updates from the production system. You do need good network connectivity between the publisher and the distributor\subscriber as pulling snapshot data (or backup\restore for that matter) can easily saturate a 100Mbit Ethernet connection. So to minimize impact on the production system, you may want to get a dedicated connection between the two.

-Raymond

|||

Thanks Raymond. I shouldn't have to worry about the snapshot/backup+restore because I've used this second server as the backup files' location in the past (and still perform copy-only FULL production DB backups to this server). The restore takes a while since it reads from and writes to the same drive but this is OK for an operation that may happen 2-3 times a year.

Thanks again,

Smitha