Hi
By default, the Log Reader Agent and Distribution Agent use the account the starts SQL Server Agent to logon into the Publisher SQL Server when performing SQL Server Transactional replication. Is there a way of configuring them so they use a different account.
Thanks
Tariq
Guys,
I get the error message: Queue reader aborting. The steps failed...in queue reader agents of SQL 2000 replication. Could any one give me a hint of how to troubleshoot this problem? Thanks a lot in advance.
Hi all replication experts...
I have created transactional replication on my servers (production server and backup server) and created pull subscription at the backup server. On my production server that also is a distributor and publisher there exist two agents, Snapshot agent and Log reader in the Publishers folder under replication Monitor.
There are also another agent with the backup server name and database name: BackServ:DBName having the type Pull and 'No replicated transactions are available' as Last Action. My question is: What is this? Must it be there?
Grateful for answers
Best,
/M
We have Lots of databases wich typically have three Trasactional Publications and two subscribers. Some DB's use a shared distribution Agent for all three Pubs whilst some DB's use Independent Agents. The reason for this set up seems to be purely by chance.
BOL says "Sharing the same Distribution Agent reduces the resource demands and increases performance at the Distributor". Whilst some resources on the net say "IF you use independant Agents, you can get performance improvements while replicating your data". This seems to be conflicting information.
I know I should go with BOL but having seperate distribution streams does make more sense (although I now have three jobs per subsciber instead of one using this method). Also more importantly, if one publications fails with dodgy data then this does not affect the other Publications. What do other people think?
Thanks in advance
hi !
I am using sql server 2005 sp1 standard edition.
I have a transactional replication set up from one server to another.
From last couple of days i am noticing two distribution agents running for the same subscription in the replication monitor. Before this only one distribution agent was running. Is there any problem having two distribution agents running in parallel for the same subscription on a server?
Any help would be appreciated.
Thanks
As you see in the images the connection is closing. During the read it counts 5 columns which is correct. When I step through the code it closes the connection when it hits dt.Load(reader) and nothing is loaded into the datatable.
------------------------------------------------------------AS I STEP THROUGH -----------------------------------------------------------------------------------------------------------------------
Please help,
Thanks,
Tom
During install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services. I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.
View 6 Replies View RelatedOn SQL Server 2005 SP2 for Publisher and Distributor on the same instance, my old snapshots are not being cleaned up.
The following error is in the agent history:
Executed as user: DomainMyUser. Could not remove directory '\vmsql01ReplDatauncPublication_TRANSACTIONAL20070702104416'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.
xp_cmdshell is enabled and I can run commands like :
exec master.dbo.xp_cmdshell ' md c:TestFolder'
The permissions to the snapshot share and file system are that DomainMyUser has full control.
I have logged into the machine as this user and can remove snapshots so it does not seem to be a permission issue.
On other machines I do not get any errors but the snapshot folder still is not cleaned up.
Any suggestion as to what the problem could be?
Thanks,
Amy
I have been running a script in SQL Server 2000 as sa also as a Active Directory user who has administrator rights (I tested both approaches SQL Server then Windows Authentication) in Query Analyser which grants execute rights to the stored procedures within the database instance and Query Analyser does not give any errors when I run the script. I have made sure that each transaction has a go after it. I then return to Enterprise Manager, check the rights (I apply them to roles so that when we create another SQL Server user we just grant him/her rights to the role) and discover that the role has not been granted the rights. I seems to be occurring only with 2 of the procedures. Is there a known bug that might be causing this?
yours sincerely
Craig Hoy
I have several DTS jobs that runs well as a job with my nt login account for the SQL agent service startup account, but if I use the System account
they fail with this error.
" Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider"
The data has change access to the System account under the NT security.
Thank you in advanced.
Jorge
Hi all, i hope you can help me.
Basically a dts package has been setup that pulls in data from another companies server, this data requires to be on-demand i.e individual users can pull in updates of the data when they require it.
I am using xp_cmdshell and dtsrun to pull in the data. This obviouly works fine for me as i am a member of sysadmin.
Books online quotes " SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell"
So i went to the SQL Server Agent Properties 'Job System' tab and unchecked 'Non-sysadmin job step proxy account' and entered a proxy account.
The proxy account has been setup as a Windows user with local administrator privilages and even a member of the sysadmin server role - just in case.
Now when i log onto the db with my test account - a non-sysadmin - and attempt to run the stored proc to import the data i recieved the message 'EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo' '
hmm... so basically i have either misunderstood BoL or there is something not quite right in my setup.
I have search the net for a few days now and yet i can find no solution.
Can anyone help?
Hi there,BOL notes that in order for replication agents to run properly, theSQLServerAgent must run as a domain account which has privledges to loginto the other machines involved in replication (under "SecurityConsiderations" and elsewhere). This makes sense; however, I waswondering if there were any repercussions to using duplicate localaccounts to establish replication where a domain was not available.Anotherwords, create a local windows account "johndoe" on both machines(with the same password), grant that account access to SQL Server onboth machines, and then have SQL Server Agent run as "johndoe" on bothmachines. I do not feel this is an ideal solution but I havecircumstances under which I may not have a domain available; mypreliminary tests seem to work.Also, are there any similar considerations regarding the MSSQLSERVERservice, or can I always leave that as local system?Dave
View 1 Replies View RelatedI am using MS SQL 6.5 SP5, NT4.0. running Tivoli Service Desk. Two of the four agents on the Applications server were not running so I recycled the server and now NONE of the agents are coming up. Generally the agents show up on the application server control panel but they have not started. The status of the server is 'initializing' and the Watchdog has started but only the 'dispatcher'started. I have hard booted the aps server and recycled the aps and db servers both. I know that there is a way to suspend the agents but that has not been done and the 'resume' button is not available to use. I tried restarting the dispatcher but it didn't do anything. I have also gone into 'config' on the control panel and it shows that '4' agents are set to start up.
We run a Web version also so I have my help desk analysts using it but they cannot refer back to tickets that they have just opened, etc.
ANY SUGGESTIONS - I have been waiting on Tivoli to call me back for almost an hour and a half.
Thanks - Mary
812-485-7876
mkeown@dc-is.org
I have around 25 sql servers(sql server 2000) all on windows server 2003. i would like to know if anyone has a script that will poll all the servers and check to make sure the agent is running.
thanks for your help
Which replication agents are available in Express edition?
abdul
Is it possible to obtain the status, running or not running, of the distribution agents that appear under Replication MonitorAgentsDistribution Agents in Enterprise Manager using WMI? If yes, can you please tell me the WMI namespace and objects that can be used?
Thanks
I have a case of SQL Server 2005 production db's publication which does not have either Log reader, snapshot or distribution agents.
I need to modify primary key in one of the tables, and existing publication does not allow this.
When I try to start snapshot job in GUI I am getting of course error:
"Could not find the regular snapshot job for the spicified publication. Supply either @job_id or @job_name to identify the job, error 20678."
Is there any way to erase it with system st procs or SQL?
Help is greatly appreciated.
I have the case where a SQL Srv 2K5 publication has no agents - agents were accidentally erased during backup/restore. I was abel to erase subscription.
The error is "The specified @job_name <jab name> does not exist. (Microsft SQL Server, Error: 14262)
Is there any way to remove publication?
Help will be greatly appreciated!
Vladimir
Hi all,
I have encountered some problems in scheduling the replication agents but before explaining that let me give a small background of the current DB landscape.
It is implemented as Star topology or re-publisher topology with the publisher and the subscriber configured on SQL Server 2000 and it consists of ShareDB on SQL Server 2005 which acts as the re-publisher of data. All other databases publish articles to ShareDB which in turn re-publishes to the databases which subscribes for the article. The topology is using a remote distributor on SQL Server 2005 to offload the replication agent jobs processing from the OLTP environment. The distributor job agent is running continuously and it is shared for a specific database in case of Snapshot replication. And all the Transactional publications will have a separate distributor agent. At present the replications agents are scheduled on both the publisher and ShareDB.
Now, I am trying to enforce one way scheduling on the publisher side which will start the snapshot agent for the corresponding publication on ShareDB after the distributor agent transfers the data from the snapshot folder to the subscribers. This approach is effective and more reliable in the sense that data synchronization will happen immediately after the distributor agent transfers the data to ShareDB.
The following are the issues related to scheduling the replication agent on the publisher only.
1. I have included sp_start_job stored procedure after the run agent step in the shared distributor job agent steps to start the snapshot agent of the corresponding publication on ShareDB after the distributor agent transfers the data to ShareDB. Since the distributor agent is running continuously in our scenario, the next step is not getting executed and so the job agent of the corresponding publication on ShareDB is not started.
2. It is also necessary to specify either the job name or the job id in sp_start_job stored procedure to start the job agent. With the replication setup configured in the aforementioned fashion, how is it possible to invoke the snapshot job agent of the corresponding publication on the second link (from ShareDB) dynamically after the distributor agent on the first link is completed?
Thanks a lot,
Barry
Hi all,
I have encountered some problems in scheduling the replication agents but before explaining that let me give a small background of the current DB landscape.
It is implemented as Star topology or re-publisher topology with the publisher and the subscriber configured on SQL Server 2000 and it consists of ShareDB on SQL Server 2005 which acts as the re-publisher of data. All other databases publish articles to ShareDB which in turn re-publishes to the databases which subscribes for the article. The topology is using a remote distributor on SQL Server 2005 to offload the replication agent jobs processing from the OLTP environment. The distributor job agent is running continuously and it is shared for a specific database in case of Snapshot replication. And all the Transactional publications will have a separate distributor agent. At present the replications agents are scheduled on both the publisher and ShareDB.
Now, I am trying to enforce one way scheduling on the publisher side which will start the snapshot agent for the corresponding publication on ShareDB after the distributor agent transfers the data from the snapshot folder to the subscribers. This approach is effective and more reliable in the sense that data synchronization will happen immediately after the distributor agent transfers the data to ShareDB.
The following are the issues related to scheduling the replication agent on the publisher only.
1. I have included sp_start_job stored procedure after the run agent step in the shared distributor job agent steps to start the snapshot agent of the corresponding publication on ShareDB after the distributor agent transfers the data to ShareDB. Since the distributor agent is running continuously in our scenario, the next step is not getting executed and so the job agent of the corresponding publication on ShareDB is not started.
2. It is also necessary to specify either the job name or the job id in sp_start_job stored procedure to start the job agent. With the replication setup configured in the aforementioned fashion, how is it possible to invoke the snapshot job agent of the corresponding publication on the second link (from ShareDB) dynamically after the distributor agent on the first link is completed?
Thanks a lot,
Barry
Ideally I am looking for a procedure that I can call in T-SQL to verify that the Log Reader, Snapshot, and Sql Agents are all up and running on a given server. I found the undocumented sp_MSrepl_distributionagentstatussummary proc and got it to work for the distribution agent. I also found sp_MSrepl_agentstatusummary, but I've yet to get this to work. Overall, I'd really like to have a better way to do this.
This absolutely must be something callable from the sqlcmd interface. It can not be through a UI nor anthing more complicated than standard T-SQL.
Any suggestions?
Before I started with this employer the server support staff had planned a backup strategy that included using database agents for Oracle and SQL Server. Nothing is really set up yet for SQL Server so I can still change this direction. Has anyone seen a definite benefit to using database backup agents? If so, what benefits have you seen? There doesn't seem to be much value added by paying for and using an additional product when the database's own utilities are so easy to use, and all the backup files can be backed up to tape with the basic backup software. I've not worked with it, though, so perhaps I am missing something. These are small databases so space is not an issue. Any opinions/comments are appreciated.
View 2 Replies View RelatedCan I have multiple SQL Server Agents running concurrently?
I would like to have 10 instances of database running on my local SQL Server - each of these are Merge Replication to a unique database located offsite.
Do they all run through one SQL Server Agent?
If they do would this be a slow process as, I think, the SQL Server Agent queues the replications pending.
Thanks,
Bianca
Any thoughts or experiences (good or bad) on using the SQL agents from Arcserve or BackupExec for backups as opposed to standard dumps to disk?
I have always used dumps and taken the position that these tools work well, reliably and do not depend on another device ( tape device) for backups. Also they should be quicker and not cause any problem with table / log concurrency or delays in updates while the backup takes place.
Also, does anyone know how these products actualy maintain concurrency? Has anyone ever used one of these backups as a recovery?
Thanks in advance.
Hi all,
I have 5 laptops, 1 laptop with SQL Server 2000 and the other 4 with SQL Server MSDE Rel A. I had a "Merge Replication" scheme that I had to set up. The laptop with 2000 will be the main Publisher/distributor and publishes/merges to two of the MSDE laptops(these laptops have pull subscriptions that pull from the 2000 laptop). These 2 MSDE laptops in turn Re-publish their data to the last 2 laptops and my scheme ends there. I have succesfully been able to Script this entire "Merge Replication" scheme, and I ran it. It creates all Components needed for my scheme to work.
1 issue though. It doesnt kick start the agents :eek: , so the replication scheme is just dormant.
Does anyone have any examples of scripts that I could use to kick off all the agents (snapshot,merge agents etc) and maybe to stop them too. I seriously need this, because we will need to have the entire process scripted.
Thanks
'Wale
p.s.
Also does anyone have sample scripts to alter settings for the SQL SERVER AGENT, for example altering its "Log On" parameter to allow a particular user with Admin privileges and also to switch its start mode to "Automatic"
All:
I am aware that I am raising an issue/question that has quite a number of ancestors in this forum. In reviewing some of the threads I still believe my situation has a bit of a twist; but that could just be me.
The process I used until a change I made recently worked just fine. A handful of my packages connect to our ERP system that only supports an ODBC connection. I set the Protection Level to the default, and then deploy the packages to the server. I use an agent to run the jobs that include these packages as steps. I have hardcoded the userID and password in the SQL jobs and so they have run fine.
In an effort to reduce maintenance on the packages I decided to run the packages from the File System instead of deploying them to the server. Now, the packages are not running as I have not changes the Protection Level yet. I did test running one of the packages using a Proxy I have created but that does not work either.
Based upon what I have read it appears that the first thing I need to do is change the Protection Level to DoNotSaveSensitive. How do I then pass the ID and password to the agent?
a. Create a confirguation file?
b. Create a package template?
c. Both of the above
To reiterate I do not wish to deploy the packages to the server; I prefer to run the packages from the File System. Further, I just have one box on which everything happens; there are no migration issues across servers.
Some insights from this group will be greatly appreciated.
Thank you!
I hope this is an easy question.
I am setting up my replication as it is in 2000... I want to set most publications to share an agent but I also want a few select table to have an independent agent... My question is if I have ones set up as independent they stay independent correct?? I mean once I select a publication to be shared it does not share with an agent that is marked to be independant right? it creates another agent for all the shared publications...
ok... I hope that was not confusing... I just want to confirm what I was thinking.
Thanks ahead of time.
I have a situation that I have discovered in our QA database that I need to resolve. When I looked at the Activity Monitor for our server, I discovered that a process is running under a domain user account for one of our .Net applications. The problem is that that domain user account has not been created as a SQL login account on the server. I am trying to figure out how someone can log in to the database server with a domain user account that has not been added to SQL Server as a login account.
Does anyone have any insight on this? I don't like the idea of someone being able to create domain account that can access the database without me granting them specific access.
- Larry
I'm going back and forth on an issue and was looking for some outside observations. I have over a dozen SQL NT servers, both 6.5 and 7.0. On some servers I'm dumping the databases and backing up the dumps with Veritas BackupExec, on 4 (two SQL 6.5, two v7) I'm backing up with the Veritas SQL Backup Agent.
Obviously, if you don't have room to dump a database, you must use the backup agent. That is the case on one of my servers and becoming so on a second. But my personal preference is to do the dump/backup.
(as a side note, one server is not backing up correctly with the backup agent but ultimately that box will require it due to DB growth, so it is something that I have to resolve)
I like the dump system for a few reasons. I find it easier to load from a dump, particularly a single table. Likewise, I find it easier to copy a database by loading from a dump rather than going from a backup, but that's mainly because of BackupExec being a little bit strange on redirecting restores.
Here's my clincher. I find restores via the backup agent to be ridiculously slow. Let's say I have a 5gig DB that has 1.5gig in it. The dump size will probably be somewhere below 2gig. The restore via backup agent carefully writes the entire 5gig even though 3.5g of that is empty. This takes a lot of time. Add to that the "post-restore DBCC". I had such a restore take something on the order of 13 HOURS which, needless to say, conflicted with my nightly DBCC's and backups.
OK. End of rant. Any suggestions or thoughts on the subject?
I've created (via Enterprise Manager) a merge replication and configured the publisher, distributer, and push subscription (via the wizards).
The Snapshot Agent is configured to autorun once the publication has been completed.
The Merge Agent has a schedule of 'Start automitically when SQL server agent starts', ie it runs continuously.
After setting this all via the wizards it all executes correctly with both the snap shot and merge agents firing and doing their jobs.
I then use the option Tools->Replication->Generate SQL Script. The 'script creation of replication jobs' option is checked. The script is created ok.
I then disable (completely) the replication, delete the database, and readd it cleanly.
From a command prompt I then fire:
osql -E -S <server> -d <db name> -i <script name>
This correctly sets up all the replication components and jobs BUT both the snap shot and merge agents haven't been started. Why haven't they been auto executed? Also, the merge agent schedule is defaulting to 'once per week' and no the selection i'd originally chosen. Does the script generation miss certain configuration choices?
If this is the case then what scripting additions do I need to make the agents fire and with the correct scheduling?
This is getting desperate as the SQL script needs to go into our InstallShield installation.
Please help,
Jez
This is the most frustrating error I've ever encountered.
I can get no more info from sql server besides "server execution failed". It gives this error when starting the snapshot, distribution, and log reader agents.
This server has been working in this configuration for over a year. We made no changes except restarted the server. This is a windows 2003 server with sql server 2000 running all the latest patches and windows updates.
I have a situation with a 6 server setup whereby merge agentswill not run if other merge agents are alreay running. Basicallywe have a 5 laptop, one tower config with the tower being thepublisher. In theory we want to be able to hook a laptop upand have it sync within, say, 5 mins. Trouble is, if agents forthe other laptops are running (trying and failing to connect becausethose laptops aren't connected) then the agents for the laptop that*is* connected won't run. It seems to be a 'maximum number of runningagents' issue. Is this is user defined setting, and if so where can I set it?Many thanks, sorry if this has been answered many many times before.Jim--Find me at http://www.ursaMinorBeta.co.uk"Brace yourself, this might make your eyes water."
View 3 Replies View RelatedDuring an upgrade, we had problems with PK errors on sp_MSget_repl_commands. I recreated the SP with the varbinary characteristic as we have seen in many posts and it was sucessful in starting. However, the sp now appears as a 'user' sp, not a 'system' sp, however I an not sure that is a real issue.
The problem I am seeing is that none of the agents are appearing in replication monitor under their agent folders. Replication appears to be working fine and the publications are showing on the distributor in the Replication Monitor - Publication folder under the appropriate publisher. But all of the agent specific folders (i.e. Snapshot agent, distribution agent, etc) are empty.
Another interesting point, is that if replication fails, the failure 'X' shows up on the Agents folder as well as the publisher, but again, no sign of the agent in the folder.
Thoughts?