.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

A query in Snapshot replication ---- Please help !!!!!

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
Hi,     I have a SQL Database with different roles and users,depends upon their roles,permissions,they can see some table,views and etc.     Now,I am going to replicate (Snapshot) the same Database into different server.    My query is, in the source database incase if they do some changes in the roles,permissions,adding roles/updation of users happens, the same will be replicated in the  destination databsase as well? One of my friend told that ,snapshot replication just deals with records inside the Database and does not deal with users/roles/permissions...Is that so?

View Complete Post

More Related Resource Links

Merge Replication: How to give read access on snapshot share to a sql account

Hello All, I want to give read access to an sql account(not windows account). Can it be given or not. Someone please tell. Thanks saandii777

How to let a non admin business user run a replication snapshot job?

According to the msdn library, I would have to give the non admin user (User1) SQLAgentUserRole privileges. But it also says that it has to be a local job and not a multi server job. Is the snapshot agent job a local or multi server job? Considering the snapshot agent is a local job and i make USER1 the owner of that job. He should be able to run the job rite? I need to give USER1 the ability to run sp_start_job to start the snapshot agent ONLY! (and not any other jobs). So what are the steps to do this? I also read that the only way to achieve this is by creating a proxy account? Is there an article somewhere that best describes this?  

Merge Replication, Push Subscription : The snapshot takes centuries to apply

Well, not centuries, except that the users are storming the gate. I'm trying to find how to get the snapshot moved to the subscriber and applied in a reasonable time. Last time was successful, but took 2 1/2 days to build the subscriber database from the snapshot. Hillary responded: Something is very wrong here. You should be able to generate your snapshot, copy it manually over to the subscriber - using the altsnapshotfolder parameter and then apply it there. So now I have my snapshot, a folder with lots of .cft .bcp .dri .prc .sch  and .trg files. Getting this to the subscriber computer shouldn't take long. Once I get it there, how do I use it to get the subscriber set up?  You can't be successful at this unless you're at least 1/2 a bubble off level.

which is best Snapshot Replication or Transaction Replication for my requirement?

Hi,    We have a main transaction database where all the live data is there.Now, to run a reporting application,we need a  different database, but the data is as EXACTLY as the live database.So,probaly every noght aroung 1 0'clock,we want to sync these two databases.   In this scenario,which is better, Snapshot replication or Transaction Replication? what are the pros and cons of using them? As I am new to this things,ur answers would be very much helpful..

Merge Replication Compressed Snapshot

Hi, How do I go about compressing my snapshot files? I am unable to find a tutorial explaining how to do it.

Can't replicate few tables in Snapshot replication SQL 2005 in two different domain


I am not able to replicate few tables of a database using snapshot replication.Both publisher and subscriber are in different network and domain.Although I am able to replicate 950 tables but facing problem in some 30 tables.I am using push replication and I can telnet 1433 port from A server to B server but vice versa not happening.When I am replicating few records of that few tables it is replicating properly but not able to replicate complete table. I tried Verbose history too but I didn't get complete error.

Please help me out.....

transactional replication - no initial snapshot generated


When I try to set up a transactional replication, setting it up works fine, but then I get an error from the snapshot assistant: the execution stops at the table sysdiagrams with the error message that the filed „definition“ uses the type varbinary(max).

There was no specific error code, i.e. the error code displayed was '0'

If I try to set up a snapshot replication the sysdiagrams table is not included and it runs through just fine.


SQL Server 2008 merge replication snapshot hangs on filtered articles


I have a publication on SQL Server 2008 Standard Edition using merge replication.  When I attempt to generate the initial snapshot, the snapshot agent appears to hang on the step "Setting up the publication for filtered articles."  I get a long (over 4 hour) series of messages: "The process is running and is waiting for a response from the server."  I know something is happening server-side, as SQL Server and the snapshot agent use a lot of memory and max out one core's processing capacity.

This has me confused as the publication is not doing any filtering.

Even more confusing:  I backed up the database and restored it onto my development-test system.  I created the snapshot there, and it took under 10 minutes every time.

Any suggestions for investigating and resolving this?

Merge Replication - Error - Query processor ran out of stack space


Hello everyone,

I have a merge publication configured that has been running fine with no issues for many weeks.  Here is my setup... Publisher - SQL 2008 SP1, Distributor - SQL 2008 R2, Subscriber - SQL 2008 SP1.

On Wednesday and Thursday of last week, I created a few hundred foreign keys referencing a table (user information for auditing).  Then, on Friday evening, I started to receive this message:



Msg 8621, Level 17, State 1, Procedure MSmerge_sel_sp_18DB84E1A521483601D2A6D477F6483D, Line 85
The query processor ran out of stack space during query optimization. Please simplify the query.



I have traced it down, this command gets executed:


exec MSmerge_sel_sp_18DB84E1A521483601D2A6D477F6483D @maxschemaguidforarticle = 'F990B1EE-D52F-4DCA-8EEF-C1783DA47A0F', @type = 6


Which executes this:



SQL 2008. Merge replication. Snapshot agent. Access Denied

Windows Server 2008 Standard x64 SP1, SQL Server 2008 Enterprise Edition x64 SP1
Snapshot agent has read-write permissions to ReplData folder but cannot access local snapshot folder. How to resolve this error?

Error messages:
Source: mscorlib
Target Site: Void WinIOError(Int32, System.String)
Message: Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\ReplData\unc\ServerName_DatabaseName_PublicationName\DateTime\' is denied.
Stack:    at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.Directory.InternalCreateDirectory(String fullPath, String path, DirectorySecurity dirSecurity)
   at System.IO.Directory.CreateDirectory(String path, DirectorySecurity directorySecurity)
   at Microsoft.SqlServer.Replication.Utilities.CreateDirectoryWithExtendedErrorInformation(String directory)
   at Microsoft.SqlServer.Replication.Snapshot.SnapshotProvider.CreateSnapshotFolders()
   at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.CreateSnapshotFolders()
   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
   at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
   at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: mscorlib, Error number: 0)
Get help: http

Cannot get Transactional Replication Snapshot to send


I have a database called reference that exists on server A and server B.  Both server A and B are publishers and both are their own distributors.  Certain articles are replicated (push) from the reference database on server A to server B and certain articles are replicated (push) from the reference database on server B to server A.  I recently had to restore the reference database on server B with a backup from server A (here is where my problems started).  I deleted the existing server A subscription from the server B publication and then deleted the server B publication, after that I rebuilt the server B publication and added the server A as a subscriber.  When I generated a snapshot of the server B publication the subscription will not recognize the snapshot and continues to say that the snapshot has not been fully generated (yes I verified that the snapshot was fully generated) at this point I even tried to reinitialize the subscription with a new snapshot and I netted the same result.

I then deleted the server B subscription to the server A publication and then deleted the server A publication and then took a backup of server A reference database restored it on server B and ran through the same steps in paragraph 1 with the same result.

I have tried, sp_replrestart, sp_replflush, sp_removedbreplication 'reference', 'tran' an

Transactional Replication - Creating Snapshot after adding a new table




Sorry for my English..


Could you tell me if it is possible not to create entire snapshot after adding a new table? Our database is very big and it takes a lot of time to make snapshot.


Also could someone tell me exactly how works blocking of tables while creating snapshot?

I've read in BOL that if Snapshot Replication is used then all tables are blocked while creating snapshot for replication. And if Transactional Replication is used, snapshot is created in parallel mode and tables are not blocked and users can work with them. I understand that in second case each table is blocked while it is processing and after processing it is unblocked and next table is blocked. Is it so?


Also I am interested how actually tables are blocked? For SELECT/UPDATE/INSERT or how? Where can I read information about it?




When doing Snapshot Replication and Transactional Replication, are the Foreign Keys and Primary Keys

When doing Snapshot Replication and Transactional Replication, are the Foreign Keys and Primary Keys replicated?

Query against a database snapshot


Hi All,

We are thinking of setting up DB snapshot against our transactional system for mainly reporting. I understand that SQL Server maintains certain pages (that went through updates) using copy-on-first-write in the Snapshot DB. I have not been able to understand the way SQL Server processes the queries on a Snapshot. Will it put the same burden on the source DB as if we were to go against the source DB and just use NOLOCKS hints? Will it use the same Index that were built on the source DB?



When is a new snapshot requied in merge replication?


Have I correctly understood that a snapshot is only required in a merge replication (pull) when the subscription is first created or reinitialized?

And that the publisher is re-generating those snapshots on some schedule, only to speed up to process if some subscribers need to be reinitialized?

I'm asking this because I intend to download the snapshot via ftp manually (inorder to use passive FTP-SSL, merge replication itself only supports active FTP). Since the MergeSubscriptionAgent.IsSnapshotRequired() method takes ages to return a result (more than a minute ove local LAN), I'd like to skip that part and simply go ahead and download the snapshot when the subscription has not been created or is marked for reinitialization.

I need answers to the questions above, so that if I go down this route -> a couple of weeks in production I wouldn't run into trouble, because SQL requires a new snapshot to say validate data or something...

snapshot replication when update records

i configure snapshot replication but it not update records automatically.what is the condition or job time to update records on replicate server. thanks in advance...

Applying Push Merge Replication snapshot manually


Here is the complete scenario :- SBS 2003 running Sql Server 2005 Standard, Merge Replication and Push Subscription. Connection Via VPN. Subscribers are SQL SERVER 2005 express..

Sometimes due to some structural changes, snapshot is to be re-applied. This is rare but is possible in our case.. On some subscribers snapshot applying goes on for hours and days with no sucess.

Are there any step by step instructions on how we can reintialize or reapply the snapshot on those subscribers manually i.e. maybe by copying the replication folder manually to subscriber and applying manually..

Pls help..

IMP : There might be a delay in posting replies due to time difference (GMT +2:00) and Friday's Holiday instead of Sunday.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend