Replication Design, Comments?

Ok, I think I have a working solution. I've worked the past
few days finding a lot of issues (not replication related) which
caused my headaches with replication, ie the @.@.servername was
null, and when creating a stored procedure snapshot it
would complain about unqualified inserts. Learned a neat
trick from Paul on how to do verbose logging. The developers
are looking at their stored procedures.
Merge Replication will be used.
I found that they had used identity columns as primary Key values.
Changed the columns to be 'not for replication'.
Tommorow I will be finding out if they have any DRI (Foreign Key
constraints that need to be marked 'not for replication'.
So far, I've created a Merge publication for the data, and it
appears to be functional.
I have also setup a snapshot publication for the stored procedures.
In addition to having the unqualifed insert issue I made the
mistake of checking 'all sp' instead of just selecting the
stored procedures they created (i was adding the replication
added routines by mistake).
If down the road they change a stored procedure, can I just
reinitialize this subscription to get the up-to-date routines?
I am replicating these because the standby server will be
the live server in case of the primary going offline.
I set the schedule on this subscription(sp) to only run
on demand.
Both machines are on the same backbone, so Bandwidth isn't
an issue.
Since I just found out about the possbible Foreign Key
issue (already created a snapshot, but not the subscription)
I am assuming that I will need to re-do the publication once
the foreign key constraints are marked 'not for replication'?
Thanks for the help. It has been an educational week.
Dave
You will have to drop the pub in order to change it to "not for replication"
(at least if using EM).
"David Gresham" <gresham@.panix.com> wrote in message
news:d5ruoa$6ms$1@.reader1.panix.com...
.....
> Since I just found out about the possbible Foreign Key
> issue (already created a snapshot, but not the subscription)
> I am assuming that I will need to re-do the publication once
> the foreign key constraints are marked 'not for replication'?
|||David,
on the changes to stored procedure point, I'd recommend using transactional
replication, which'll then give you the posibility of using sp_addscriptexec
for a change to a specific SP - and will avoid the need to reinitialize all
procedures.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication and security..is it really this UNSECURE!

Hi Guys
I need to create an anonymous pull subscription over the Internet.
I've got everything working but I don't want my subscribers to be able
to connect to the publisher/distributor via Enterprise Manager or
ISQLW or anything else for that matter!! Basically they should only
be able to talk to the Publishing SQL Server through replication
methods. How is this achieved?
I'm currently using SQL Security because I don't want an anonymous
subscriber to impersonate my publishers sqlserver agent account, the
agent on the publisher is used for other scheduled tasks that needs
Domain Admin privileges, am I missing something here!!?
Currently I've got it secure-ish. I've used a specific sql account
and given it access in the PAL and removed the guest account from
other DBs, however you cannot remove the guest account from Master or
Msdb and said account must also be a member of the db_owner role. The
most they can do is issue select statements against tables but I think
that is even too much, for instance they can query sysservers which
would allow them to see other subscribers that we do business with.
Even if I use a secure VPN between publisher and subscriber what's to
stop an inquistive subscriber, using the SQL account that I've told
them to use for their subscription, connecting using EM or ISQLW
through the VPN? Surely there must be a way to give ONLY replication
access but nothing else?
Please help!!
The problem is not with replication and security, but rather a larger one
with SQL Server security in general. Anytime you create an account on that
server, and give that account rights to only one user database, this account
can query system tables on the master database.
I suggest you post this question to the microsoft.public.sqlserver.security
newsgroup.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"greedo" <d8rrylf@.msn.com> wrote in message
news:f0badc7a.0408170010.47baef00@.posting.google.c om...
> Hi Guys
> I need to create an anonymous pull subscription over the Internet.
> I've got everything working but I don't want my subscribers to be able
> to connect to the publisher/distributor via Enterprise Manager or
> ISQLW or anything else for that matter!! Basically they should only
> be able to talk to the Publishing SQL Server through replication
> methods. How is this achieved?
> I'm currently using SQL Security because I don't want an anonymous
> subscriber to impersonate my publishers sqlserver agent account, the
> agent on the publisher is used for other scheduled tasks that needs
> Domain Admin privileges, am I missing something here!!?
> Currently I've got it secure-ish. I've used a specific sql account
> and given it access in the PAL and removed the guest account from
> other DBs, however you cannot remove the guest account from Master or
> Msdb and said account must also be a member of the db_owner role. The
> most they can do is issue select statements against tables but I think
> that is even too much, for instance they can query sysservers which
> would allow them to see other subscribers that we do business with.
> Even if I use a secure VPN between publisher and subscriber what's to
> stop an inquistive subscriber, using the SQL account that I've told
> them to use for their subscription, connecting using EM or ISQLW
> through the VPN? Surely there must be a way to give ONLY replication
> access but nothing else?
> Please help!!

Replication & Reindex

I've read some varying opinions on this topic...
Is it a best practice to stop replicating a database during a maintenance window that is doing reindexing?
What is the effect of stopping / not stopping the distribution agent?
JUDE
I always prefer to avoid these jobs running concurrently. We reindex once a week so it's not too much of a big deal. The reason I separate the executions is to avoid blocking issues which will generally fail one of the jobs.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The merge replication metadata tables in SQL 2000 get highly fragmented. You should defragement them which is an online operation (minimal locking). If you do see this interfering with replication processing you might want to stop the jobs, but it should not be necessary.
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
"Jude" <judes@.email.uophx.edu> wrote in message news:%23o2Cx8ODHHA.4952@.TK2MSFTNGP06.phx.gbl...
I've read some varying opinions on this topic...
Is it a best practice to stop replicating a database during a maintenance window that is doing reindexing?
What is the effect of stopping / not stopping the distribution agent?
JUDE
|||JUDE
"Jude" <judes@.email.uophx.edu> wrote in message news:%23o2Cx8ODHHA.4952@.TK2MSFTNGP06.phx.gbl...
I've read some varying opinions on this topic...
Is it a best practice to stop replicating a database during a maintenance window that is doing reindexing?
What is the effect of stopping / not stopping the distribution agent?
JUDE

Replication - Publisher moving to a different server

My disclaimer: Ive never done this. These are just
theories.
I would have to imagine that as long as the new box has
the same name as the old, and the data files go to the
same location on the destination as they were on the
source, you should be good. You should be able just to
backup/ restore master/ msdb and the user db's should be
alive and in-tact. No matter what advice you get here, do
this in a test environment and get backups before you do
anything.

>--Original Message--
>Hi,
>I need to move my publisher to a different database as
the current server on
>which it is running is slow compared to my current
requirements.
>But I have 2 subscribers to this server. I have merge
replication in SQL
>Server 2k with sp3 on.
>Can someone advice me as to how to have a smooth
transition? I cannot just
>replace the harddisk as the new server has a much
powerful harddisk.
>Is it possible for me to just restore the backup of the
current publisher on
>the new server alongwith the master and msdb, and
replication starts? I will
>take care to name the new server as the old one and
remove the old one from
>the network.
>Can someone please help me with the same?
>Thank you in advance.
>Regards,
>Karthik
>
>.
>
Hi Chris,
Thank you for the advice. I will test it on on a different machine and let
you know as to how things went.
Thank you once again.
Regards,
Karthik
"ChrisR" wrote:

> My disclaimer: Ive never done this. These are just
> theories.
> I would have to imagine that as long as the new box has
> the same name as the old, and the data files go to the
> same location on the destination as they were on the
> source, you should be good. You should be able just to
> backup/ restore master/ msdb and the user db's should be
> alive and in-tact. No matter what advice you get here, do
> this in a test environment and get backups before you do
> anything.
>
>
> the current server on
> requirements.
> replication in SQL
> transition? I cannot just
> powerful harddisk.
> current publisher on
> replication starts? I will
> remove the old one from
>

replication

Hello,
I'm a developer and I've inherited a sql environment that has 1 publisher,
a distributor, and 12 subscribers. Several articles are published using
transactional replication. I need to make a constraint change on a table
that is published. Do I need to drop the publication and recreate it? What
scenario requires dropping of publication?
thanks
Unfortunately in SQL 2000 you need to. In sql 2005 you don't. Make sure you
script out the publications and subscriptions before making your change.
With luck you can do a no-sync subscription.
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
"Troy" <troy@.pushit.com> wrote in message
news:uarP51FHGHA.1088@.tk2msftngp13.phx.gbl...
> Hello,
> I'm a developer and I've inherited a sql environment that has 1
> publisher, a distributor, and 12 subscribers. Several articles are
> published using transactional replication. I need to make a constraint
> change on a table that is published. Do I need to drop the publication and
> recreate it? What scenario requires dropping of publication?
> thanks
>
>
|||Troy,
you don't necessarily need to drop the publication - it is possible to drop
the articles and keep the rest of the publication going:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
constraint change
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Start the snapshot then distribution agents.
Note that the validity of this method depends on the relationships between
these tables and the other articles, and if you can prevent changes to the
system while these changes are made.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

replicating to denormalized db

SQL2K sp3
Howdy all ...its been some time since Ive been around these parts.
I may need to start replicating to a denormalized DB. I tested this a while
back and it was grueling. After spinning my wheels for a couple of weeks my
then emplyer decided he wanted to keep the db as is so I just did normal
replication. Now if memory servers correctly, the only method I found for
successful replication to a denormalized db was to create Clustered Indexed
Views on the Publisher, and then replicate those, not the tables. I recall
trying another method (dont remember what it was), that was totally
unsuccessful. I need to do 1 way, continuous, Transactional replication. So
my questions are:
1; Are there other methods?
2; If so, what are they?
3; Are they in Hillary's book?
4; I may have the luxury of making the Subscriber SQL2K5, will that help
accomplish my goials.
TIA, ChrisR
Chris,
as an alternative to indexed views, you could use transformable
subscriptions, which is a mix of replication and DTS. This applies to SQL
Server 2000 rather than 2005 AFAIR.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replicating structural changes

Hi,
Is it possible to replicate structural changes? I've been reading the
replication docs, and it looks to me that this is only possible using
snapshot replication (ie. not on the fly).
Any products that can do better replication (all additions/changes to
all db objects for a given db) than the natively supported?
Thanks,
Morten
Morten,
sp_repladdcolumn and sp_repldropcolumn can be used for most simple column
schema changes, and such changes can be done on the fly. You can't change an
existing (replicated) column directly - eg change the datatype from int to
varchar, but these 2 stored procedures can be used to make such changes
indirectly. Many other schema changes are not part of the replication
framework - eg adding an index or most constraints but these can be sent to
subscribers using sp_addscriptexec.
HTH,
Paul Ibison

Replicating Stored Procedure Alterations

Hi,
I've created two publications. One publication contains the tables...the second publication contains that stored procedures that work with the tables of the first publication. In another database I subscribe to the two publications...the table publicati
on first followed by the stored procedure publication. I'd like to now make an alteration to the stored procedure on the publisher using ALTER PROC and have that schema change replicated over to the subscriber. I perform the ALTER PROC on the publisher
but the change does not appear on the subscriber. How can I get the ALTER PROC to take affect on the subscriber?
Thanks
Jerry
have a look at sp_addscriptexec for this
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hillary,
Thanks. There is an item that discusses this to some degree at the SQL Mag site instant doc #26344 but I'm out of town and do not know my login info. Looks like it recommends reinitalization. Which sounds ok for the proc subscription but a default of a
drop for the proc would delete the permissions as well...agree? That doesn't sound like the best solution to me. I had thought of using the on-demand script execution as a workaround. Just wanted to know if I was missing something. Guess not?
Also, looks like you have some useful information in your books on replication. What are the costs and how are they obtainable? By the way, I'm a SQL instructor (in replication class this week). Are you willing to provide me with a copy of both replica
tion books no-fee? If so, I'd be willing to share them as a potential purchasable resource for my students in my replication classes.
Let me know.
Thanks again.
Jerry
|||You don't need to reinitialize when running sp_addscriptexec.
it will merely distribute and execute a script to all of your subscribers.
you can reinitialize if you want, but you don't have to
The book will be available on amazon eventually. It will also be available on nswu.com at some point in time as well. I'll contact you offline about your other request.
It has to be printed first
|||Right...wasn't implying that I needed to reinit when using the sp proc. Was comparing two different approaches to resolve the issue. I think the sp proc is the easiest to implement and work with.
Jerry

Replicating Matched MS SQL HR Data to Active Directory's Metadirectory fields

I've managed to do the add linkedserver stuff and pull up of some active
directory information.
I even matched up some data but could never insert anything into the AD Meta
fields.
I used this article,
http://msluder.dk/Resources/ADSI%20S...%20HTML/dq.htm ,which never
promised insert capabilities.
But if this is all it can do is give the ability to run some obscure reports
then it's hardly useful.
What I would like to do is create a view that would dynamiclly update the AD
Meta data from the Human Resources Database using the CN and / or
Primary-Email Address as the linking field.
That way when people change office numbers or as new employees start that
Meta information would be updated once it was entered or altered by HR.
It seems this would be possible via some kind of replication or event based
update through the SQL Server.
Any one have some ideas on this?
-Ethan
Ethan,
have a look at the Microsoft Script Repository
(http://www.microsoft.com/downloads/d...n&FamilyID=B4C
B2678-DAFB-4E30-B2DA-B8814FE2DA5A) there are scripts there to update AD.
HTH,
Paul Ibison
|||Have a look at the Replication Distributor Interface Reference in BOL.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html