February 15, 2007 at 8:07 am
Hello, we are upgrading our environment from sql 2k standard to 2k5 standard. Part of the task is to set up transactional replication from our domain (BOS1) to our hosting domain (BOS2) servers. And BOS1 is trusted by BOS2. To set up the replication agents, I am thinking of doing the following:
1. create BOS1\RplSSAgent domain account for snapshot agent, make it db_owner in the distribution database (publication db and distribution db reside on the same server), grant it write permissions on snapshot folder
2. create BOS1\RplLRAgent domain account for log reader agent, make it db_owner in publication and distribution db
3. create BOS1\RplDTAgent domain account for distribution agent, make it db_owner of distribution db, grant it read permission on snapshot folder.
Is there anything I am missing here? Is there anything that need to be done on the active directory side?
Thanks for any help.
KZ
February 15, 2007 at 12:16 pm
I would recommend creating one account perhaps something like RplServiceAccount, making the user lodal admin on both the SQL boxes and also make the user sysadmin instad of dbowner. This will keep your configuration simple and tidy.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
February 15, 2007 at 12:41 pm
Thanks for the reply.
I was actually reading 'replication security best practices' on books online. One of the practices is to 'run each replication agent under a different windows account'. Thats why I came up with three accounts. That does seem to be massy though.
And I just realized that, according to the same article, instead of using domain accounts, I should use local windows accounts and use the same user name and passwords on both the publisher/distributor and subscriber machines. So its going to be like:
machine1\RplSSAgent, machine1\RplLRAgent, machine1\RplDTAgent accounts on publisher/distributor server
machine2\RplDTAgent account on subscriber server
with the same user name and password for the distribution agent on both servers.
Does that sound right?
February 15, 2007 at 5:23 pm
You can use different accounts make sure they have appropriate permissions. If the snapshot is being written to a network drive, make sure the account your snapshot agent is using has permissions to write to that folder etc.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
February 16, 2007 at 1:17 pm
How does the mechanism work using the local windows accounts?
We are facing the same issue. Have you opened the 1433 port for both sides?
I’d like to use one either domain or sql account instead of three to do the test. It would make my job easier somehow.
Owen
February 16, 2007 at 3:23 pm
the windows (domain) account is local admin as well as sysadmin. So the user has every right to do what it wants.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
February 20, 2007 at 2:41 pm
Since we have not got the new servers from the hosting domain yet, I tried running some testing using sql 2005 replication security model, and it happened to work out fine for me. I was trying to replicate from one server to another within the same domain, but I think the same should work in two different domains. The two articles I found most useful are:'replication agent security model ' and 'replication security best practices' from booksonline.
So I created three local accounts, for snapshot agent, logreader agent, distribution agent on publisher side, and distribution agent on subscriber side, the user name and password for the distribution agent on both sides have to be exact the same. Use 'replication agent security model' to grant each agents their permissions. Here is my script for reference:
use
master
GO
exec
sp_adddistributor @distributor = N'RAM\test'
GO
-- Adding the distribution database
exec
sp_adddistributiondb @database = N'csn_prod_distribution',
@data_folder
= N'D:\sql\data\test', @data_file = N'csn_prod_distribution.MDF',
@data_file_size
= 500, @log_folder = N'D:\sql\data\test',
@log_file
= N'csn_prod_distribution.LDF', @log_file_size = 100,
@min_distretention
= 0, @max_distretention = 72, @history_retention = 72,
@security_mode
= 0, @login = N'sa', @password = ''
GO
-- Adding the distribution publisher
exec
sp_adddistpublisher @publisher = N'RAM\test', @distribution_db = N'csn_prod_distribution',
@working_directory
= N'd:\sql\data\test',
@thirdparty_flag
= 0
GO
use
master
GO
exec
sp_replicationdboption @dbname = N'csn_product', @optname = N'publish', @value = N'true'
GO
USE
csn_product
GO
exec
sp_addlogreader_agent @job_login = 'RAM\RplLRAgent', @job_password = '' , @publisher_security_mode = 1
GO
use
[csn_product]
GO
exec
sp_addpublication @publication = N'csn_product_FooterImages',
@sync_method
= N'concurrent', @repl_freq = N'continuous', @description = null, @status = N'active',
@allow_push
= N'true', @allow_pull = N'true', @allow_anonymous = N'false',
@enabled_for_internet
= N'false', @independent_agent = N'false', @immediate_sync = N'false',
@allow_sync_tran
= N'false', @autogen_sync_procs = N'false', @retention = 336,
@allow_queued_tran
= N'false', @snapshot_in_defaultfolder = N'false', @compress_snapshot = N'false',
@ftp_port
= 21, @ftp_login = N'anonymous', @allow_dts = N'false', @allow_subscription_copy = N'false',
@replicate_ddl
= 1
exec
sp_addpublication_snapshot @publication = N'csn_product_FooterImages',@frequency_type = 4,
@frequency_interval
= 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0,
@frequency_subday
= 8, @frequency_subday_interval = 1, @active_start_date = 0, @active_end_date = 0,
@active_start_time_of_day
= 0, @active_end_time_of_day = 235959,
@publisher_security_mode
= 1, @job_login = 'RAM\RplSSAgent', @job_password = ''
GO
exec
sp_grant_publication_access @publication = N'csn_product_FooterImages',
= N'BUILTIN\Administrators'
GO
exec
sp_grant_publication_access @publication = N'csn_product_FooterImages',
= N'distributor_admin'
GO
exec
sp_grant_publication_access @publication = N'csn_product_FooterImages', @login = N'sa'
GO
-- Adding the transactional articles
exec
sp_addarticle @publication = N'csn_product_FooterImages', @article = N'tblImageFooter',
@source_owner
= N'dbo', @source_object = N'tblImageFooter', @destination_table = N'tblImageFooter',
@type
= N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop',
@schema_option
= 0x00000000000000F3, @status = 16, @vertical_partition = N'false',
@ins_cmd
= N'CALL sp_MSins_tblImageFooter', @del_cmd = N'CALL sp_MSdel_tblImageFooter',
@upd_cmd
= N'MCALL sp_MSupd_tblImageFooter', @filter = null, @sync_object = null
GO
exec
sp_addarticle @publication = N'csn_product_FooterImages', @article = N'tbljoinImageFooterStore',
@source_owner
= N'dbo', @source_object = N'tbljoinImageFooterStore',
@destination_table
= N'tbljoinImageFooterStore', @type = N'logbased', @creation_script = null,
@description
= null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F3, @status = 16,
@vertical_partition
= N'false', @ins_cmd = N'CALL sp_MSins_tbljoinImageFooterStore',
@del_cmd
= N'CALL sp_MSdel_tbljoinImageFooterStore',
@upd_cmd
= N'MCALL sp_MSupd_tbljoinImageFooterStore', @filter = null, @sync_object = null
GO
-- Adding the transactional subscription
exec
sp_addsubscription @publication = N'csn_product_FooterImages', @article = N'all',
= N'hamster\four', @destination_db = N'csn_product', @sync_type = N'automatic',
@update_mode
= N'read only', @offloadagent = 0, @dts_package_location = N'distributor'
GO
sp_addpushsubscription_agent
@publication = 'csn_product_FooterImages', @subscriber ='hamster\four',
@subscriber_db
= 'csn_product',@subscriber_security_mode = 1,
@job_login
= 'RAM\RplDTAgent', @job_password = ''
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply