SQLServerCentral / SQL Server 2005 / SS2K5 Replication

writable reporting db copy from log-shipped db

bstephenson — Fri, 16 May 2008 10:34:35 GMT

I have a server that has a high uptime requirement for when it is in use (actually in use for one day at a time and then brought down, but it cannot be offline when it is running, so actually 100% uptime requirement during the "runs"). We do log-shipping for DR purposes, but during our "run" of this database system one day at a time (in very high input rates), we are also in need of a copy of the database in which we can take the ongoing data and play with it, do what-if analyses, etc., and just doing read-only reporting off of the secondary log-shipped copy of the database just can't cut it, as we need a writable database to do some of our analyses.Replication of the main database is not a good choice for us, as the schema for the database changes rather frequently, and I know it is tough to change schema during replication publication. Mirroring is not the choice, as the mirrored database is not in readable mode. So I am not sure what is the best way to solve this problem. Latency of the "play" database is not necessarily an issue, it can be many minutes old, so is this a possiblity: Back up the secondary log-shipped database, and just restore it over the "play" database periodically? Or am I missing a basic SQL Server technology answer to solve this problem?Thanks in advance for assistance,Brian StephensonThorTech Solutions

Transaction Replication Connectivity Problems

John Mac Pherson — Fri, 16 May 2008 08:24:50 GMT

Hi allI have 64-bit distribution server struggling to replicate to a 64-subscriber in a specific location.Replicates fine (although alot of "cannot connect to subscriber errors) to a 32-bit subscriber in the same location.If I replicate from the subscriber down to the distributor, zero errors or problems.When setting up the subscriptions, the server connects to the subscriber and populates the database drop-down list. Yet, when it actually has to replicate, no joy or atleast takes ages to replicate 10 recordsThe process could not connect to subscriberUnable to complete login process due to delay in opening server connectionTurned on agent logging and error file also had above in, and error 106 (haven't found much on that)As extra tests, same distributor has replicated to other multiple locations (32/64 mix of subscribers) without any problems.There are no firewall rules blocking sql and as mentioned, it does connect during setup. The odd publication has made it there of numerous attemptsEven configured a 32-bit distribution to same location and it fails outright, whether the subscriber is same 64-bit machine or 32-bit.Our other locations replicate to "problem location" without any problems.Network admin has emailed the location hosting companies to see if any filtering is occurring on our line as a last resort.Ping times are 100ms average.Am I missing something?

I need to move distributer DB, but how?

NewDBAPhil — Fri, 16 May 2008 04:35:30 GMT

We currently have merge replication set up between 4 servers. The distribution DB is set up locally on one of the servers, which pushes all updates to the other servers.The server which has the distributer DB, is having a few performance problems which we think is down to the distributer DB. Therefore we want to move it to a fifth server, but not sure how?

Snapshot job jails on 1st time attempt for replication

steve smith — Thu, 15 May 2008 11:20:42 GMT

Test environment, first time attempting to setup any kind of replication, but starting with merge per management's instructions.1. Set up different login ID's on source machine (publisher) and target machine (subscriber) [side question - does pub-sub distinction truly make sense in a MERGE replication, once it's set up?]2. Used the wizard on the publisher side to start a merge replication.3. Snapshot job is set up to initialize the replication, but: 4. Error message and event viewer has message that login failed for subscriber login-id. I believe correct group memberships have been granted, unless more than Agent privileges are needed within SQL Server environment.5. The publisher is running on a workstation running Windows 2000 Server, SQL 2k5 is 9.0.2047.00 according to SSMS. The subscriber is running Windows 2003 Server, SL2k5 version is also 9.0.1399.00My questions are:1. How can I be certain, without granting admin rights to the Agent's login ID, that it has sufficient rights (even without knowing that's the point of failure...) - I've given it membership to user, superuser (probably not necessary, but could it cause problems?), backup operator, and all SQL related groups.2. Would it be more rational to start with a 'simpler' replication model before going to merge? Since this is a test machine, and I don't have use of an application, I have to script all the 'transactions' and have no clue what land mines I'll fall into when I start violating the application's integrity rules in attempting 'small' database changes. I am only replicating a small set of tables, rather than the entire database. 3. How do I replicate the non-data portions (i.e., other than tables) of the database? I did not see anywhere to replicate the application-specific stored procedures, etc. How does SS2k5 'know' to copy them over unless instructed to?

Distribution clean up Failing

Paul Ingham — Thu, 15 May 2008 08:30:23 GMT

Hi I have a Distribution clean up job failing on a SQL 2K5 Enterprise Instance (Windows Server 2003) Failing with the following error:-[b]Executed as user: *******. Could not remove directory 'DIRECTORY....'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.[/b]I have checked all the obvious xp_cmdshell permissions, account permissions etc.I can delete the folders when I log on to the server as admin.When i look in to event viewer i find this error:-[b]Replication-@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only check rowcou: agent distribution@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only scheduled for retry. Could not clean up the distribution transaction tables.[/b]Cheers,Paul

DB Mirroring and Replication or Log Shipping

jchamiak — Thu, 15 May 2008 09:53:48 GMT

I have a client who is currently using SQL Server 2005 DB Mirroring and they would like to have a copy of this database on a third server to offload querying to. We are currently looking into using either Replication or Log Shipping to accomplish this. Would either solution be recommended over the other to work alongside db mirroring?

Replication between disconnected servers

David — Mon, 12 May 2008 18:31:14 GMT

We have a database application that we want to run on two (and possibly more) different servers that will not be able to be connected. Is there a way to use (and set up) replication in SQL Server 2005 to periodically synchronise the data between these two databases?Thanks,David

Nothing happening?

Chris Hall — Wed, 14 May 2008 14:35:29 GMT

We implemented transactional replication on a large database (350 gb).The snapshot has been applied (took around 12 hours) and things seem fine, however when I look in the subscription under undistributed commands I see 887647 and this number has not gone down any at all even after 30 minutes.I have set my remote timeout to 0 and I don't see any errors in the replication, but why are the 887647 commands replicating?

Subscriber uploads only?

Bob Shaw — Tue, 13 May 2008 14:35:12 GMT

I need a way to upload new article rows to the publisher without downloading anything from the actual publisher article. The table is used for storing attached files from our application and get very large after a few files. The server is more than capable of handling it but the remotes are tablet PC and the database size is effecting the applications overall performance.I saw a parameter called EXCHANGETYPE but that was for SQL 2000. Does anyone know if its even possible to publish an article to be upload only from a Subscriber or a work around?This is for a merge replication setup unless another type provides this option.

THE PROCESS COULD NOT CONNECT TO A SUBSCRIBER

Cynthia Bell — Tue, 04 Dec 2007 12:35:53 GMT

I am receiving the following error when I try to replicate from SQL SERVER 2005 to ORACLE 9.2.0.8: THE PROCESS COULD NOT CONNECT TO A SUBSCRIBER:'MY ORACLE DATABASE'.(Source:MSSQL_REPL,Error number:MSSQL_REPL20084) Issue resolved after installing the Oracle driver.

Looking for the best solutions

Andre — Mon, 12 May 2008 10:59:05 GMT

I have a prod and dev servers. On the prod server, the db is 100 GB with approx 4,000 tables. My goal is to do daily refresh of my dev db and minimize the load on the production server.I am considering snapshot replications. Since our prod enviroment is down everynight between 10 and 11 pm, I would schedule the snapshot of prod at 10 pm. I beleive I can reduce the impact on the prod server by configuring a pull subscription on my dev server.I am running 64 bit Windows 2003 servers with SQL 2005. The prod server has 32 GB ram.Any other suggestions, recommendations?Thank you.Andre

restoring replication without a snapshot via scripts

s.garrison — Mon, 05 May 2008 16:56:14 GMT

Hi guys,Can anyone describe the steps necessary to restore a transactional replication via scripts only. Specifically I'd like to restore replication without using a snapshot...or backup for that manner. I have custom replication procedures that alter the data going over to the subscriber databases...therefore they should not be overwritten by the snapshot upon restarting the replication.Just as an example, say you want a subscriber database to act as an archive...therefore the sp_MSdel_tablename stored procedures have been modified to return 1 instead of deleting rows. How would one stop replication, restore from backup or do maintenance and then restart replication without loosing data?I believe you could use the sp_addsubscription @sync_type = N'none' to accomplish this, but I have been unsuccessful as of yet. Anyone scripted this out before?[code]use XGOexec sp_addsubscription @publication = N'Replication', @subscriber = N'HQ15', @destination_db = N'W', @subscription_type = N'Push', @sync_type = N'none', @article = N'all', @update_mode = N'read only', @subscriber_type = 0[/code]Thanks in advance for you help.

Transactional Replication From Publisher on Domain to Subscriber on Standalone Box

tfoley — Thu, 08 May 2008 14:40:09 GMT

I have setup transactional replication from a publisher/distributer (same box) on a domain over to an offsite standalone machine that is a subscriber. It has a dedicated line from one box to the other and no firewall in between. The transactional replication is a pull subscription. There is a share on the publisher which is used for a snapshot folder. I set up a user, .\user1 on the subscriber that runs the SQL Server agent on the subscriber. I setup a user on the publisher, .\user1, that has the same password as the .\user1 on the subscriber. The .\user1 has read,change permissions on the snapshot folder. Is there anyway to apply a snapshot using the passthrough authentication? Or is using ftp the only way to apply a snapshot in this scenario?It is running fine since I have set it up, but I never initialized the database with a snapshot. If I need to run the snapshot when items get way out of sync for any reason is this possible?

Replicating large tables

Luk — Thu, 08 May 2008 09:58:12 GMT

We have setup a transactional replication from one of the servers on one of the largest tables we have. The table is 300 million rows in size and takes about 100GB of disk space. The initial replication worked fine, the problem is with future updates. Sometimes we need to update several million rows in the tables, this causes the latency to go up to several hours. Any performance improvement tips?

On the publisher data modifications are disallowed until the upgrade completes and snapshot has successfully run.

sehgalnamit — Sun, 11 May 2008 23:43:31 GMT

NIL

data retrival time is too slow

Nicole — Tue, 06 May 2008 08:12:23 GMT

last weekend we had an upgrade, my data retrieval time from the replication server is extremely slow. a report that previously took maybe 15 seconds to return data is taking 5 minutes now. I have made no changes to the query.running on sql server 2005replication is working finecan any one tell me what might be the problem

Central subscriber and remote distributor

Sky — Thu, 08 May 2008 11:43:11 GMT

Hi,I'm pretty much new to SQL replication and not sure that I'm fully aware of all its aspects. Any help you can give me on my questions would be greatly appreciated. Server A (SQL 2005) is at the central office. Server B (SQL 2000) is half way around the globe.On a regular basis (every 15-30 min), we need to replicate data over to the central office from the remote regional office. The data at the remote office is just a small portion (subset) of data at the central office ... regional sales, RMA, product info, accounting data (which needs to be consolidated into whole company financial), and so on. Data needs to go only one way from the remote office to the central office. The two servers are on a same logical network, via VPN equipment.I'm thinking transactional replication with Server A as the central subscriber and Server B as the publisher. The data will need to be partitioned for the most part.My question is where to place the distributor. The remote office is quite new and still trying to get their systems and operations working. Everyone there is trying to learn how to operate. Because of this, their data may not always be correct and changes (design or just data) are constant.I was thinking about a separate SQL 2000 remote distributor (relative to the publisher) at the central office. But, is this rather odd, not typically used? Will it create too much network traffic? The reason I'm thinking this type of setting is because having the distributor on the publisher side, I will have no control over what gets replicated over to the main database, right? If something's wrong with the data or with the way it's replicating, I can't really monitor in the middle and stop data from ending up in the main database? The system at the main office is pretty much stable and a lot of its data are critical in nature. Any data discrepancy or problems with system are taken very seriously.Another question is, with transactional replication ... initial snapshot is typically done. But, since the data to be replicated is only a subset and shouldn't overwrite anything existing before, there should be no initial snapshot? How do I go about getting it to work this way?Thank you.

Field size too large

rvz — Fri, 09 May 2008 08:00:13 GMT

I have set up transaction replication between two databases. Data from a table in the first database is replicated to the same table in another database.The table at the publisher already has some data in it. The table at the subscriber is empty. When the replication is synchronizing, I get the following errors in the replication monitor:*The process could not bulk copy into table "dbo"."virtualdatalocations_waitingqueues". (Source: MSSQL_REPL, Error number: MSSQL_REPL20037) Get help: http://help/MSSQL_REPL20037*Field size too largeThe table looks like this:[code]CREATE TABLE virtualdatalocations_waitingqueues ( dataid int , personid int , queueid int , CONSTRAINT FK_vw_dataid FOREIGN KEY(dataid) REFERENCES datalocations(id) ON DELETE CASCADE , CONSTRAINT FK_vw_personid FOREIGN KEY(personid) REFERENCES persons(id), CONSTRAINT FK_vw_queueid FOREIGN KEY(queueid)REFERENCES waitingqueues(id));[/code]It used to run fine in the past. I couldn't find any help on google or on forums.Any help or comments are greatly appreciated.

Merge Replication Subscription Create Date

steveb — Thu, 08 May 2008 05:01:45 GMT

I have a database here with a number of local subscriptions,a couple of them are causing a few problems.Is there a way to determine what date and time a local subscription to merge replication was created ?Thanks,Steve.

Configuring IIS - SSCE - RDA and Merge Replication

grahowler — Tue, 06 May 2008 16:39:52 GMT

HiI've been following the How-To here: [url=http://msdn.microsoft.com/en-us/library/bb226707.aspx]http://msdn.microsoft.com/en-us/library/bb226707.aspx[/url] Things were going along wonderfully until I hit...[b]To configure IIS for SQL Server Compact Edition synchronization[/b] > Step 14When I click the "Add" button I get an error... "The program cannot open the required dialog box because no locations can be found. Close this message, and try again." Then when closing that error I get the exception result I've included below.I'm using...IIS7SSCE 3.1Sql Server 2005 All on the same Vista machineAny help greatly appreciated, exception follows.Thanks![quote]See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.************** Exception Text **************System.ArgumentException: Value does not fall within the expected range. at Microsoft.SqlServerCe.ConnWiz.ObjectPickerTypeLib.IDsObjectPicker.Initialize(DSOP_INIT_INFO& pInitInfo) at Microsoft.SqlServerCe.ConnWiz.ObjectPickerTypeLib.ObjectPickerWrapper.DisplayObjectPicker(IntPtr hWnd, String machineName, Boolean IsMultiSelected, UInt32 uplevelFilterFlags, UInt32 downlevelFilterFlags, UInt32 flType, Int32& count, DS_SELECTION[]& dataArray) at Microsoft.SqlServerCe.ConnWiz.ObjectPickerTypeLib.ObjectPickerWrapper.GetSelectedAccounts(IntPtr hWnd, String machineName, Boolean IsMultiSelected, Boolean AllowGroups, Int32& count, DS_SELECTION[]& dataArray) at Microsoft.SqlServerCe.ConnWiz.DirectoryAccess.OnBtnUserAccountsAddClick(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativewindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m) at System.Windows.Forms.Nativewindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)************** Loaded Assemblies **************mscorlib Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.1434 (REDBITS.050727-1400) CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll----------------------------------------ConnWiz30 Assembly Version: 9.0.242.0 Win32 Version: 3.0.5300.0 CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%20Compact%20Edition/v3.1/sync/SQL/ConnWiz30.exe----------------------------------------Microsoft.SqlServer.WizardFramework Assembly Version: 9.0.242.0 Win32 Version: 9.00.3042.00 CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.SqlServer.WizardFramework/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.WizardFramework.dll----------------------------------------System.Windows.Forms Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.1434 (REDBITS.050727-1400) CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll----------------------------------------System Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.1434 (REDBITS.050727-1400) CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll----------------------------------------System.Drawing Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.1434 (REDBITS.050727-1400) CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll----------------------------------------Microsoft.NetEnterpriseServers.ExceptionMessageBox Assembly Version: 9.0.242.0 Win32 Version: 9.00.3042.00 CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.NetEnterpriseServers.ExceptionMessageBox/9.0.242.0__89845dcd8080cc91/Microsoft.NetEnterpriseServers.ExceptionMessageBox.dll----------------------------------------Microsoft.SqlServer.GridControl Assembly Version: 9.0.242.0 Win32 Version: 9.00.3042.00 CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.SqlServer.GridControl/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.GridControl.dll----------------------------------------Microsoft.SqlServer.CustomControls Assembly Version: 9.0.242.0 Win32 Version: 9.00.3042.00 CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.SqlServer.CustomControls/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.CustomControls.dll----------------------------------------System.DirectoryServices Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.1434 (REDBITS.050727-1400) CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.DirectoryServices/2.0.0.0__b03f5f7f11d50a3a/System.DirectoryServices.dll----------------------------------------Accessibility Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.1434 (REDBITS.050727-1400) CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Accessibility/2.0.0.0__b03f5f7f11d50a3a/Accessibility.dll----------------------------------------System.Data Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.1434 (REDBITS.050727-1400) CodeBase: file:///C:/Windows/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll----------------------------------------System.Web Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.1434 (REDBITS.050727-1400) CodeBase: file:///C:/Windows/assembly/GAC_32/System.Web/2.0.0.0__b03f5f7f11d50a3a/System.Web.dll----------------------------------------************** JIT Debugging **************To enable just-in-time (JIT) debugging, the .config file for thisapplication or computer (machine.config) must have thejitDebugging value set in the system.windows.forms section.The application must also be compiled with debuggingenabled.For example: When JIT debugging is enabled, any unhandled exceptionwill be sent to the JIT debugger registered on the computerrather than be handled by this dialog box.[/quote]

Transactional Replication from SQL2K5 to Sql 7.0 (sp4)

Pankaj Shere — Wed, 07 May 2008 09:40:36 GMT

I am trying to replicate some tables from SQL2k5 (SP2) to SQL (7.0). My publisher and distributor is the 2K5 box and I want the 7.0 to be the subscriber. According to this article on microsoft.com [b]http://msdn.microsoft.com/en-us/library/ms143241.aspx[/b] I should be able to do it. But when I try to add the 7.0 as a subscriber, I get an error which says "This SQL Server version (7.0) is not supported"Is there a workaround to make it work. Or can I manually run the stored procs and set the 7.0 box to be the subscriber.Thanks

Scale out Transactional Replication

Maurizio79 — Sat, 29 Mar 2008 04:44:15 GMT

Hi there,I'm searching for the best solution to scale out transactional replication with hundreds of publications, but..."I still haven't found what i'm looking for" :)That's the situation: we use transactional replication (push, no update) with almost 100 articles (tables and sp) on sql2k5.We've the online database, that expose a publication with no filters to the distribution database, which is the remote distributor for that publication . We use that way to not stress the online database with replication log reader of distribution database.The distrib db itself expose 150 publications (the same base publication, but with different filters on articles - based on subscriber) to 150 subscribers, and he is local distributor of itself.This seems to work: we've a latency of 2-max 7 sec. in the normal work, but...when whe do large insert/update (almost 100.000 records), the distribution server goes down, with 100% cpu for hours and hours, and the latency goes to the sky.:blink:The other problem is that it seems whe reached out the hardware resource of the distributor server (150 publications, almost 10-15 mb per distrib.exe), so we're searching for a new scaled solution.The question is: there is a good way to scale out sql2k5 transactional replication, having more than one distributor server?Can I have an online database and have a lot of distribution server, each with some publications to load the balance?I also took a look to log shipping, but..can it work with transactional replication?Thanks in advance, questions, ideas, criticism and comments are welcome!;)Maurizio Mangiavacchi.

How to schedule publisher and subscriber replication database backups

sram24_mca — Tue, 06 May 2008 23:27:38 GMT

Hi,Can anyone tell me,how to schedule the publisher and subscriber database backups without any data loss

EMC SAN replication Mirrorview/S with SQL 2005

aliciaooi — Tue, 06 May 2008 04:48:06 GMT

Hi, we are trying to implement EMC Mirrorview/S of databases. There is a 3 node SQL cluster all active. Does anyone know if the DR site has to have exactly the same infrastructure/configuration for the a failover to DR to work? There are some forums which say one just has to start the SQL services on the cluster on the DR site to bring up (after mounting the SAN disks). But how similar does the configuration have to be on the DR site to prod site ie would it need 3 node cluster as well and exactly the same physical drive mapping, virtual servernames etc as the production etc?

joining two tables in filter condition

balbirsinghsodhi — Mon, 05 May 2008 14:01:47 GMT

Is it possible in replication to use two tables in filter condition and create a new table and send it to client.

Cannot alter table because part of replication but it's not

Pam Ozer — Mon, 05 May 2008 05:08:34 GMT

I have a table that is replicated in two different publications. I need to rename the columns. I tried removing the tables from the replication and then altering the columns but for some reason the system thinks that the table is still being replicated. I found some documentation on this for Sql server 2000 but not 2005 and the fix for 2000 will not work in 2005. The error I am getting is Cannot alter column because table is part of replication but it isn't. It has something to with the system not dropping some information.Has anyone else had this problem in SQL SErver 2005?

Replication Latency

Lynn — Thu, 01 May 2008 07:22:18 GMT

Anyone has some good tips or scripts for troubleshooting transactional replication latency between 2000 servers as well as 2000 to 2005 servers? Your help would be much appreciated.Lynn

Mirror failover breaks transactional replication

Simon Larsen — Tue, 18 Mar 2008 18:13:37 GMT

I have 2 servers (2005 with the good sp2).They run a database with mirroring (high safety manual failover).This database then has transactional replication set up with a remote distributor and 2 remote subscribers.The replication works fine until I fail the mirror over.The mirror was created and working fine.I created the distributor first and added the -PublisherFailoverPartner MirrorServerName to the snapshot and log reader profiles. Then I created the publishers and subscribers.I have since created a new agent profile and clicked the change existing agents and restarted the jobs.That didn't work so I added -PublisherFailoverPartner [MirrorServername] to the agent step (second step) in the ServerName-Publication-Number job, stopped and started the job.Still fails.The replication monitor continues to show the agent attempting to run replcmds on the primary server and failing.Any ideas?

Reporting options for Replication conflicts?

Adrian Robertson — Sun, 04 May 2008 20:55:44 GMT

HelloI have a merge replication environment setup with a single Publication and a single Subscriber.Due to some anomalies in the database, there are quite a few conflicts appearing in certain tables. Whilst I can see how to resolve these conflicts, is it possible to produce a report on the existing conflicts, displaying something like the table name and the columns causing the conflicts? I'd like to be able to review these in an easier format before resolving the conflicts. I can view each conflict individually but I was hoping for something more high level. It appears I can log the resolutions, but I'd like to see something in a report (or a log) about the conflicts themselves, prior to resolving anything.Thanks in advanceAdrian

snapsnot retention

Oberion — Sun, 04 May 2008 20:16:23 GMT

I was wondering if anyone know how to set up the snapshot folder retention to only keep 2 instances of the snapshot in the snapshot folder rather than, filling up the disk space?I can't seem to find this parameter.thx

Restore subscriber DB from subscriber backup

MFoley — Wed, 23 Jan 2008 13:36:16 GMT

We need to maintain replicated subscriber data. Here is what I am trying to do in SS 2005:Have replication setup, with the subscriber not receiving DELETE transactions, and have no snapshot initialization (the first initialization of the subscriber DB would of course be from a backup of the publisher). With the subscriber NOT receiving DELETE transactions, it will be in a sense the archive DB. Problem comes when we have issues and need to reinitialize the subscriber DB. We wouldn't be able to use the publisher backup since it will not have all of the archived data. Can we reinitialize the subscriber DB from a backup of itself instead of the publisher? Thanks in advance,Matt F.

snapshot slow

Oberion — Mon, 28 Apr 2008 18:19:43 GMT

This tickle my brain cell a bit. I broke up my publication from one to 5, making it easier to recover when only 1 table/article failed rather then sending over all 5 tables/articles.I have these two tables that has 1.3 million rows and the other one has 74 million rows.The 74 million rows table was able to take a snapshot of everything in under 5 minutes and sent it over to its subscriber in roughly 3 minutes.The other table with 1.3 million rows, however, took over 45 minutes to snapshot, and over 1:30mins to send it to a subscriber ... Ahh crap, I just figured it out.the 1.3 million rows is 37GB large, while the the 74million rows is only 6GB, that's why it is faster.I guess sometimes it is good to type it out on a forum and the answers comes to you. hahahaha

Maintenance plan & transaction log

jvElecenter — Wed, 30 Apr 2008 00:46:11 GMT

Hi thereWe have a Navision database. It's about 9gb. Some tables are being replicated overnight.We have a maintenance plan running, that backups database and tries to shrink it.This won't work because it is in in replication.On top of that our transaction log grows quickly (yesterday it was ,6Gb, now it is 7gb).So two questions :- A maintenance plan on a database which is published should be possible ? How to do this ?- How can a transaction log grow so quickly due to replication.Fyi it is a transactional replication.Many thanx in advanceJeffrey

Do INSERT triggers fire during MERGE replication?

tom menacher — Tue, 29 Apr 2008 12:45:39 GMT

Probably a dumb question. But we are experimenting with MERGE replication. If I insert a record into table A, I have an AFTER INSERT trigger which executes a procedure to update another table B.My other job site is a subscriber to both table A and table B, and when someone there inserts a record into table A, the AFTER INSERT trigger executes a procedure to update their table B.Every evening, we intend to use Merge Replication to combine the day's work and synchronize the data. Replication makes sure I have the changes made by the other job site, and they have the changes made at the home office. But do AFTER INSERT triggers fire when the Replication inserts new records into the table? Or is this not really considered an INSERT into the table?

How to grant permissions on replicated stored procs

Dipali — Wed, 30 Apr 2008 19:42:50 GMT

I have a transactional replication with central publisher and 10 subscribers. The replication runs pretty smooth. Now I want to add a new stored proc as an article to the existing subscription and then distribute it to the subscribers. The 10 subscribers are set to synchronise once a day at 4 pm. I am able to add the new article using exec sp_addarticle Replication command. Is there a way that I can grant execute rights on that newly created SP to a particular user/role on that subscriber.Whats the best way to do it.

Logshipping and Replication setup on DR servers

Nicole — Wed, 30 Apr 2008 08:48:41 GMT

Hi All,Logshipping and Replication setup on DR servers: We have 3 servers which are on site A, A1, B1 and C1 with replication setup hereA1 is the production server (in this case with 2 databases), A1 is the publisherB1 is the Distributor and SubscriberC1 is also another subscriber. This is the setup already setup on site A and working. Now we want a DR for site A, We want the same setup as site A on the DR side(site B) with replication as being setup on site A.so we want to setup another site B on remote location. Where we can synchronize our site A db’s using log shipping We have created 3 servers on site B as, A2,B2 and C2 Now the issue is we want to setup logshipping from A1 to A2,And setup replication between A2,B2 and C2.Hence we need to make A2 as publisher, B2 as (distributor,subscriber) of A2 and C2 as subscriber of A2. ?’sCan we do logshipping and replication like this?What is the best way of doing this?Site1 is working on cluster server with A1 as production, B1(active cluster) and C1(passive cluster), will this affect the process?When we do logshipping from A1 to A2, A2 will be in restoring mode, Can we still do replication like that?While doing replication, we need to create distributor and then from distributor we need to subscribe the publication for B2 and C2?In a scene of DR, how fast we can bring the site B back on production in case of failover at site A and How?Can we keep the site 2 on standby, and do this process? Note: All servers are on sql server 2005 with sp2 on both site A and site B. The whole idea of this process is to keep our site A replica to another location and the replication process running, so that in a case of DR we can restore back all the Db’s to the latest point of time with minimum downtime. Thanks

Data Ware House Replication Scenario for Ad-hoc System.

mpradeesh — Sat, 26 Apr 2008 11:32:38 GMT

We have a real-time system in sql server 2005. We would like to create a replication for this real-time system and use that as the base for the ad-hoc reporting using Report Builder and Reporting Services.The options 1. How to back up the real-time database to another sql server 2005 on hourly basis.like the reporting server should be one hour behind the current real-time system. Can I use replication Services in sql server 2005.2. For Ad-hoc Reporting, the solution we are thinking is to create models for each business areas and use reporting builder as the tool to generate reports on fly and save to the report manager .3. Create pre-defined reports using Reporting Services in Sql server 2005.I would like to discuss a better solution for this scenario.Looking forward to hear the best one !!Thanks,Matt.

SQL 2000 replicate a ntext column to SQL 2005, OK ?

onlo — Mon, 28 Apr 2008 08:58:30 GMT

Can I do these things :(1)Use Transaction Replication, replicate a SQL 2000 database to a SQL 2005 database ?(2)Some of the SQL 2000 data columns are ntext, can we replicate a ntext column ?

Subscription Setup Issue w/ strange error msg.

Andrew Pfeiffer — Tue, 04 Jul 2006 08:32:00 GMT

I'm having this issue setting up a push trans subscription for a 2000 instance from a publication on a 2005 instance.

The Error:

"The concurrent snapshot for publication 'BetaDB01' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Snapshot Agent for the publica"

Details:

Yes that is the exact error message it appears to chop off at "publica" with nothing more to scroll down or view details on.

Same Physical machine with Windows 2003 Server SP1. But with two instances of SQL Server Installed. One is SS2000 w/SP3 Version 8.0.818 The other is running SS2005 9.0.1399

I have another publication setup on the SS2005 instance for a totally different database AlphaDB01 and a push trans subscription on the SS2000 for this and it works fine. I'm trying to setup the same thing but for BetaDB01.

After I complete all the steps to setup the Subscription in the same way that AlphaDB01 is setup it gives the above error. The Subscription appears in the treeview below the Publication node for BetaDB01 and the Replication monitor shows a status of completed for the snapshot agent and the log reader agent is running. I've tried to reinitialize, I've stopped and started the agent etc.

This is a development environment so there is no critical data on the SS2000 instance in the BetaDB01. My next thought is to drop the BetaDB01 and recreate it.

*Note I have tested a DTS export/import from SS2005 -> SS2000 and the BetaDB01 takes it just fine and appears to work.

If anyone can provide some constructive ideas it would be greatly appreciated.

distribution agent

sql2k8 — Sun, 27 Apr 2008 15:00:11 GMT

hi,the distribution agent says that the process is running and waiting for response from the server .i have replication set up for different locations.only one location bothers me.it runs for hours and it will not get complete for quite a few days.once it is done i am missing the data for few days.can you please tell to me how to make the distribution agent run faster with out much delays.thank you,