SQL Server 2008 :: Changing Alt Snapshot Folder For Merge Replication?
Sep 22, 2015
I have a merge replication. Currently works fine. Publisher & Distributor are on the same server. I need to change the location of the alternate folder for the snapshot files.
I’ll probably just change it through the GUI, but would I use sp_changedistpublisher or sp_changemergepublication if I were scripting everything?
My real concern is the subscribers. Do I have to ‘tell’ the subscribers where the alt folder has been changed to? Do I just run sp_changemergepullsubscription on the subscribers?
At present our snaopshot folder for merge replication(mainly) and our snapshot replications are on a seperate server. We now need to move the folder to a new server. Anybody explain how we will do this and what affects it will have
We have a domain joined SQL 2008 R2 server performing a snapshot database replication to a non-domain joined SQL 2008 R2 server. The snapshot replication is working with one exception. Under one of the tables, there is a Key, Constraint and Index that are part of the database. The Key and Indexes is copying over. However, the constraint is not. Why would the Key and Index copy but not the Constraint?
I have setup merge replication which successfully synchronizes with a group of desktop users using SQL Compact Edition.
However now I have setup Article Filters and when I attempt to regenerate the snapshot I get the following error:
Invalid column name 'rowguid'.
Failed to generate merge replication stored procedures for article 'AssignedCriteria'.
When I look at publication properties at the Articles page.. All my tables have the rowguid uniqueidentifier successfully added to tables and selected as a compulsory published column, apart from the table above "AssignedCriteria".. Even when I attempt to select this column in the article properties page and press ok, when I come back it is deselected again. ( The Rowguid column is however physically added to the table)
I have scripted the publication SQL and then totally reinstalled from scratch, including the database but for some reason it doesn't like this table. I remove the article filters, but still this "rowguid" is never "selected" in article properties.
We are using Uniqueidentifiers in other columns as well for historical reasons, but this doesn't appear to be a problem in other tables..
We have a large database with a small number of large tables in it (and a larger number of SMALLER tables), and it is a publisher for a transactional replication scenario. When I create a snapshot to initialize a new subscription, I notice with the larger tables that sometimes it generates multiple files in the snapshot folder, usually in multiples of 16, and numbers them like this:
With other tables, I'll get just one LARGE snapshot file, named:
MyOtherTable_4.bcp
In the latter case, the file can be very large (most recent is 38GB).
In both cases, the subscription will eventually be initialized, but the smaller files will generate separate log entries every few minutes in the Replication Monitor, showing 'Bulk Copied data into 'MyTable' (34231221 rows)', whereas the larger table will generate only ONE log entry, showing 'Bulk coping data into table 'MyOtherTable', and it may take a couple of hours before there is anything else showing...except for an entry saying, 'The process is running and is waiting for a response from the server.'
My question is: what would be the difference between the two tables that would result in one generating MULTIPLE snapshot files, the other only a single, much larger one? The only difference I can see in the table definition is that the one generating multiple files has a clustered index, whereas the others do not.
There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.
When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication). The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).
When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.
The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.
I have tried:
- dropping/re-adding the article in question.
- Setting up a new Snapshot Folder
- Validated all the settings and configurations
I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.
If I create a merge replication snapshot for database A today and I subscribe to the servers on Monday, will the changes made to database A from the time I created the snapshot be applied to the other servers once i subscribe to them
I'm trying to test merge replication, but am unable to create the publication, the snapshot agent keeps failing. The error I get from replication monitor is "Script failed for Table <tablename>"
Our database has quite a few XML datatype columns, when I take out the xml columns the snapshot succeeds OK, but fails with the xml column.
I created a simple table containing a varchar column and xml column. I created this table in our database, and in Adventure Works. Was able to create a publication with this test table in the adventureworks database, but was unable to create publication with this table in our database. The snapshot failed with error "Script failed for table testtable". I removed the xml column on the test table in our database and was able to create the publication successfully. Creating a publication in the adventureworks database with an xml column has no problems, but does in our database.
When SQL Server accesses the data in a table, it locks the data for the duration of the process to ensure the transactional consistency of the data during the process.
I was wondering if it is possible to take advantage of snapshot isolation to allow me to avoid these locks?
We have a central office with a SQL2005SP4 server (yeah, I know... old as heck) that's the main database and it has multiple subscribers in regional offices. Well... one of the regional offices server is failing, and it needs to be replace.
The original server is an ancient Win2003 86x
The Server team will build a new Win2008r2 64x, and use the same name and IP address
And I'm tasked with the SQL part.
I'll be installing the same version/patch of SQL, but 64x instead, and migrate all databases, including the system databases.
How do I handle replication? Do I need to reintialize from scratch? or can I just use the backup as a starting point?
I'm having issues setting up merge replication and the errors I'm getting are inconsistent but lead me to believe there is a bigger underlying issue than what they actually indicate.
Issue: unable to initialise subscriber with snapshot for merge replication. Fails for various reasons regardless of environment and setup. Previously this has deployed fine but now it is not.
Publisher is SQL 2005 (9.0.2047), Subscribers are SQLExpress or SQL Dev
The various errors I get are as follows:
These come mixed together generally... Unable to INSERT ... cannot insert NULL into column rowguid. [This error comes up on different tables for different snapshots, including ones with no records] bcp (Bulk Copy) error 20253 batch send failed Unspecified error Failed to send batch after max errors end of file reached, terminator missing or field data incomplete
Actions I have taken so far, all to no effect:
Adjusted Agent Profile to increase timeout values and decrease packet sizes Took new snapshots (tables where errors occured changed but still ame errors) Created a local subscription database on the server to remove any network related issues (using merge sync over VPN) Deleted and recreated Publication Created second publication with different articles on same database to find same errors in different locations again
I've tried reinitializing subscriptions (before subscribers were dropped and I tried recreating) but the DROP command fails due to FK constraints so if I reinitialize any subscriptions they will fail to be applied. Have tried editing the properties to just delete data and not drop tables but that still failed.
I've tried setting up subscriber without snapshot - created publication and snapshot, backed up db, copied to subscriber and restored there. See this article: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=689428&SiteID=1 This bloke suffered same results and the response from MS was to change the help files, not functionality. Doesn't help us dealing with subscribers via satellite AND unable to deploy snapshots...
I've checked the service pack releases to see if any issues like this are addressed and found nothing relevent.
My gut feeling is that the snapshot is not being generated correctly for some reason - system stored procedure errors perhaps.
So far my experience with replication has been good, with the odd issue or frustrating limitation in older versions, but this is really killing me...
Barry
(Sorry that author is "Anonymous" - must have messed that up)
i am getting the below error while applying running the Synchronization agent for the Subscriber. I have created replication topology with one central server and one subscriber. Here central server has windows server 2003 and subscriber has windows XP. Both are having SQL server 2005. After creating the merge subscriber, i am runnnig the Synchronization agent manually for the first time. While running that i am getting below error. Anybody aware of this error.
2006-06-24 00:26:00.175 Applying the snapshot to the Subscriber 2006-06-24 00:26:02.722 The schema script 'D_NUM_7.sch' could not be propagated to the subscriber. 2006-06-24 00:26:02.784 Category:NULL Source: Merge Replication Provider Number: -2147201001 Message: The schema script 'D_NUM_7.sch' could not be propagated to the subscriber. 2006-06-24 00:26:02.816 Category:AGENT Source: WMBT-07 Number: 0 Message: The process could not read file '\WMBT-01 epldatauncLTR-IN001_TEST_PUB20060624034804D_NUM_7.sch' due to OS error 1265. 2006-06-24 00:26:02.831 Category:OS Source: Number: 1265 Message: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.
I'm trying to setup merge replication and get the following error from the snapshot agent after I've finished the 'New Publication Wizard'. This doesn't make sense to me as it is an automatic process. Also, it looks like it is complaining about a 'dbo.MSmerge_cont...' view which is a view created automatically for the merge publication.
ERROR MESSAGE: The identifier that starts with '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC' is too long. Maximum length is 128.
FULL DETAILS: Message: The identifier that starts with '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC' is too long. Maximum length is 128. Command Text: IF OBJECT_ID(N'[<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT AUTHORITYSYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90 as select * from MSmerge_contents where 1 = 2</CommandText></TSQLCommand></EVENT_INSTANCE>]', 'V') IS NOT NULL DROP VIEW [<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT AUTHORITYSYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90 as select * from MSmerge_contents where 1 = 2</CommandText></TSQLCommand></EVENT_INSTANCE>] Parameters: Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command) at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout) at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout) at Microsoft.SqlServer.Replication.AgentCore.ExecuteAdHocQueryDiscardResults(String strQuery, SqlConnection connection) at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.GenerateSystemTableBcpFiles() at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPostArticleFilesGenerationProcessing() at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot() at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun() at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 103) Get help: http://help/103 Server XF4SWCS464, Level 15, State 4, Procedure , Line 1 The identifier that starts with '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC' is too long. Maximum length is 128. (Source: MSSQLServer, Error number: 103) Get help: http://help/103
I'm converting an existing production replication process to SQL 2005 for testing. My snapshot for a merge replication is failing.
Environment: Single XP server, no filters, @schema_option = 0x000000000C034DD1
I have several snapshot jobs in my process, 2 for transactional replication and 2 for merge replications. In both types, I'm replicating from A to B and then from B to C. In the failing job, I'm merge replicating from B to C. In this failing snapshot job, the error messages are:
2006-03-01 18:42:17.22 [58%] Bulk copying snapshot data for article 'MyName1' 2006-03-01 18:42:17.34 [58%] Bulk copied snapshot data for article 'MyName2' (0 rows). 2006-03-01 18:42:17.41 [58%] Bulk copied snapshot data for article 'MyName1'(195 rows). 2006-03-01 18:42:29.84 [61%] Discovering dependencies among article objects in the specified publication 2006-03-01 18:42:33.66 [61%] The replication agent had encountered an exception. 2006-03-01 18:42:33.66 Source: Unknown 2006-03-01 18:42:33.66 Exception Type: Microsoft.SqlServer.Management.Smo.FailedOperationException 2006-03-01 18:42:33.66 Exception Message: Discover dependencies failed. 2006-03-01 18:42:33.66 Message Code: Not Applicable 2006-03-01 18:42:33.66
Does anyone have an idea what I could look for to try and fix this?
The A to B snapshot obviously uses the same tables. My snapshot for merge job (which worked) messages for the A to B snapshot are:
Message 2006-03-01 18:40:24.98 Time spent analyzing foreign key references (seconds): 0.00 2006-03-01 18:40:24.98 Time spent analyzing check and default constraint references (seconds): 0.19 2006-03-01 18:40:24.98 Time spent analyzing non-article object dependencies (seconds): 0.42 2006-03-01 18:40:24.98 Time spent preparing snapshot generation (seconds): 35.49 2006-03-01 18:40:24.98 Time spent generating merge replication procedures (seconds): 41.39 2006-03-01 18:40:24.98 Time spent generating merge replication stored procedure scripts for subscribers (seconds): 20.66 2006-03-01 18:40:24.98 Time spent bulk copying data (seconds): 1.25 2006-03-01 18:40:24.98 Time spent copying/compressing generated file (seconds): 0.00 2006-03-01 18:40:25.00 Time spent adding rowguid columns/creating merge triggers (seconds): 1.38 2006-03-01 18:40:25.00 Time spent posting snapshot commands (seconds): 25.97 2006-03-01 18:40:25.00 *******************************************************************************
I kicked off the job again and got a different error message:
Message 2006-03-01 19:17:30.32 --------------------------------------------- 2006-03-01 19:17:30.32 -BcpBatchSize 100000 2006-03-01 19:17:30.32 -HistoryVerboseLevel 2 2006-03-01 19:17:30.32 -LoginTimeout 15 2006-03-01 19:17:30.32 -QueryTimeout 1800 2006-03-01 19:17:30.32 --------------------------------------------- 2006-03-01 19:17:30.32 Connecting to Publisher 'xxx-xxxxxxxx' 2006-03-01 19:17:30.34 Publisher database compatibility level is set to 90. 2006-03-01 19:17:30.36 Retrieving publication and article information from the publisher database 'xxx-xxxxxxxx.xxxxxxxxx' 2006-03-01 19:17:30.54 [0%] The replication agent had encountered an exception. 2006-03-01 19:17:30.54 Source: Replication 2006-03-01 19:17:30.54 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException 2006-03-01 19:17:30.54 Exception Message: Data is Null. This method or property cannot be called on Null values. 2006-03-01 19:17:30.54 Message Code: 52006 2006-03-01 19:17:30.54
I get the following error when trying to run a pull subscription from the subscriber:
The schema script '' could not be propagated to the subscriber. (Source: Merge Replication Provider (Agent); Error number: -2147201001) --------------------------------------------------------------------------------------------------------------- The process could not connect to FTP site 'ftp://ESOFTSERVER.NO-IP.INFO' using port 21. (Source: ESOFT (Agent); Error number: 0) --------------------------------------------------------------------------------------------------------------- The connection with the server was reset (Source: (OS); Error number: 12031) ---------------------------------------------------------------------------------------------------------------
I've tried everything I could to solve this problem, but I can't get it working.
I'm working with merge replication between Sql Server 2005 and Sql Server 2005 Mobile. I'm using dynamic filtering by function HOST_NAME (... where Table.FilteredColumn = HOST_NAME())
When I'm trying to chagne values from filtered column on the client side (Table.FileteredColumn) I get error message that the column is read-only.
How can I change data in filtered column? Is this possible?
Getting this error after successfully setting up merge replication scenario:
The server agent could not connect to the publisher (29049)
Well, successful all the way up to the point of subscribing that is.
The publication is configured with IIS 5 under XP. Already had a 3.1 sample publication running. The new web share is running sqlcesa35.dll and successfully loads diag page. The publication snapshot is successfully created. The snapshot agent and IUSR accounts have permission in publication and distribution dbs and are both in the PAL. I created the publication under SQL 2008 Mgt Studio. The web config did not work from within Mgt Studio, but did work when I ran wizard from Start Menu. Subscription steps all seem normal and then when attempt to sync at end of wizard get this error.
Will try again tomorrow with fresh environment, only using SQL 2005, SQLCE 3.5 and the 3.5 server tools. This is on a secondary test machine with lots of new Visual Studio stuff etc.
Reference here on MSDN says contact product support if reproducible. http://technet.microsoft.com/en-us/library/ms172357.aspx
Hi all, I found that my testing server is accumulating shapshot folder in repldata. Everytime, we refresh the tables (snapshot publication), a new folder created without the old folders. And I found that there is at most 1 snapshot folder remain in production server. Any parameter to adjust the retention period of the snapshot folder? Thanks in advance
I need to restore a sql server 2000 database which does not have any backups apart from a snapshot folder which contains some .bcp, .IDX and .Sch files. (there was a transactional replication setup on this database at some point and the snapshot folder is still there.)
Is this possible? any help will be highly appreciated.
I have huge database on prod. One time I tried to run DBCC CHECKDB, it took more than a day. My question is can I created a snapshot of the prod database on the same server and run DBCC CHECKDB on the Snapshot DB? will doing this interfere production database? I don’t have option to make copy of the database on a test server and run it there.
I have two clusters and one publishes snapshots and the other cluster subscribes. I am planning an SP2 installation but I see in the SP2 readme that there are only special considerations for merge and transaction replication.
Will I need to patch in a certain order and will it matter if I patch the publisher who is also the distributor and then a week later patch the subscriber?
One of the things that annoys me in 2005 is that you cannot change the snapshot location during the setup of the replication. Does anyone know if I missed something or if they are going to change that in a future patch?
We are using SQL Server 2005 SP2 to do transactional replication.
We and have a separate service account for the SQL Agents (sqladmin) vs. SQL Replication Agents (sqlrepadmin). It is my understanding this is a replication security best practice. The sqlrepadmin has full permissions on the snapshot share folder and it's subdirectories. The sqladmin account does not have permissions at all.
I have been getting an error message when we run the distribution clean up job. Executed as user: PRODsqladmin. Could not remove directory '\Tes01boxRepldatauncqabox01_DB01_TO_ORACLE20070905104896'. Check the security context of xp_cmdshell
I have dropped the publication and recreated which is what appears to have caused the error.
From http://technet.microsoft.com/en-us/library/ms151151.aspx
Note:
If a publication is dropped, replication attempts to remove the snapshot folder under the security context of the SQL Server service account. If this account does not have sufficient privileges, log in with an account that does have sufficient privileges and remove the folder manually. Removing a folder requires the Modify privilege if the folder is a local path or the Full Control privilege if the folder is a network path.
The note above implies that the SQL Server service account (sqladmin) needs permissions on the snapshot folder as well.
Finally my questions: Is there a workaround that will allow the distribution cleanup job to run as sqlrepadmin and perform the delete?
If both sqlrepadmin and sqladmin need permissions to the snapshot what is the reasoning from a security perspective of separating them out?
I have a requirement to create a package that takes all files in a given folder and adds them to a single archive (.zip) file. I've tried several methods using 7zip and while I can create archives for every file in the directory I can't seem to get them into a single .zip file.
I am setting up a database for one contractor who needs to access one of our databases but i need to remove the permission for them to view the security folder so he cant access the SA account and enable it. I was able to deny them the view permission to see all the other databases and hide the system objects but i can still see the security folder and the sa account there where i can disable and enable it.
I'm, using my script to many location to create folder to save output files and if the folder is removed/not present it can create it without any noise. But the problem is, while I use the same sort of script to check if a folder is present in the sharedpath it will not create it to copy all bkp files from local to remote path works good, but if you delete the folder or rename the exisitng folder and if the below script tries to create the folder it created as "fILE", very interesting. Per IT team they have given SQL Server account the full rights to create/delete/alter folder/files.
Do I need to use seperate script or way to create / alter folders in the sharepath?
