I need to setup snapshot replication for 1 large table.
Source table: SourceDatabaseName.dbo.MyTable1
Target table: TargetDatabaseName.CustomSchema.MyTable1
In the above example, how can I specify the target DB's CustomSchema name (it's currently creating dbo, not CustomSchema)
can a sql server 2000 snapshot replication
solution handle a schema change.
as far as i know i can't drop a table involved in replication
Is there a way to force the snapshot to use a format file when bcp'ng the data to the subscriber?
The publisher and subscriber schemas are slightly different. Mainly added columns to the subscriber.
We have a post snapshot script that updates these additional columns on the subscriber.
I'd like to force the snapshot to use a format file, so it doesn't error when initially loading the data to the subscriber.
Is this possible?
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?
View 3 Replies View RelatedRegarding Snapshot replication....
If I have 200 tables on my source DB and 100 tables on my target DB... and I set up a publication w/ just 1 article to be published....
Will the invocation of the snapshot/apply affect any tables on my target other than the article I'm replicating?
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:
MyTable_3#1.bcp
MyTable_3#2.bcp
...
MyTable_3#16.bcp
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.
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?
How do we know our schema changes will propagate to subscriber without breaking the replication? Is there any t-sql command to find out the option true or false?
View 2 Replies View RelatedThere 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.
How can I keep schema changes in the subscriber's database when I replicate the snapshot from the publisher?
I just want to move data from a remote server, but it seems that the tables are being dropped which is not good as we use the subscriber as a development box.
Please advise
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.
View 9 Replies View RelatedI 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?
Thanks
I have a "merge" publication created, with only Procedures, Views and Functions. (Note I have other merge publications that replicate the tables from the same database). I keep getting the same error (see -below) on various procedures. I cannot find anything wrong with the procedures themselves. I also checked the offending procedure by removing it from the publication and compiling it in the database...it works fine. None of my other publications (all tables) encounter this error. * I have 3 others.
My version of SQL is 9.00.1399.06
Here is the message I am getting. Any help would be greatly appreciated. Note I changed the proc name due to client restrictions.
Message: StartIndex cannot be less than zero.
Parameter name: startIndex
Command Text:
select 'number' = convert(int, 0), 'definition' = definition
from sys.sql_modules
where object_id = object_id(@qualified_object_name)
union all
select 'number' = convert(int, procedure_number), 'definition' = definition
from sys.numbered_procedures
where object_id = object_id(@qualified_object_name)
Parameters: @qualified_object_name = [dbo].[pra_merge]
Stack: at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.TextModeOnObjectScripter.Script()
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateNonTableArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, SqlSmoObject smoObject, Boolean quotedIdentifierOn, Boolean ansiNullsOn, Boolean textMode)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateStoredProcedureArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.MergeSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 52006)
Get help: http://help/52006
Source: mscorlib
Target Site: System.Text.StringBuilder Remove(Int32, Int32)
Message: StartIndex cannot be less than zero.
Parameter name: startIndex
Stack: at System.Text.StringBuilder.Remove(Int32 startIndex, Int32 length)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.TextModeOnObjectScripter.ProcessGetObjectScriptResult(SqlDataReader dataReader)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior) (Source: mscorlib, Error number: 0)
Can we create the Database with two schema and having the separate file group for each schema.
View 5 Replies View RelatedI am inserting updating few tables from snapshot and reading same bunch of tables from reporting using readcommitted . It is showing some deadlocks i think it is write in this situation as " x" is not compitable with "s" ,"is".
View 4 Replies View RelatedAny TSQL script to monitor replication(Transactional, Snapshot) with current status ? I have tried below script but it giving error.
"Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested."
DECLARE @srvname VARCHAR(100)
DECLARE @pub_db VARCHAR(100)
We have two SQL Server 2005 production DB at remote sites. Due to network bandwidth issue, we need to replicate these DBs (publishers and distributers) to central corporate SQL 2000 DB (subscriber for backup and possible reporting (and in rare case as a failover server).
We would start out with backup from SQL 2000 db restored on remote SQL 2005 DBs. When we have DB issue on remote 2005 DB, we want to restore it from central corp. 2000 DB backup. Since two DBs are replicating to central DB, we DO NOT want combined db back up data on restored remote 2005 db. We can restore the db and delete unwanted data before we turn on replication from this restored server. So, this is not a problem.
The real problem is how to avoid snapshot replication (during initialization) when we create a transaction replication on this restored server to avoid over writing data on the central subcriber sql 2000 DB???
HELP!!
Hi ,
I urgently need a solution for this. I have configured merge replication between a webhoster and a local machine
and it is working perfectly but when I am adding an article (table) which has a CLR assembly associated with it,
the merge replication is failing saying it cannot apply the schema of the CLR assembly at the client. I think Microsoft
supposedly says in it's documentation that merge replication supports CLR assemblies. I have recreated the snapshot
after selecting the table (article) and I have reinitialised the subscription also but it is just not working. It is the simplest
of replication with just one publisher and one subscriber.
Thanks,
Andy
The requirements are:
1. the user has read-only permissions to dbo tales.
2. the user can do everything within the rpt schema, which contains all objects analyzing dbo tables.
3. the user does not have any permission outside rpt schema, except permissions in #1.
The current solutions are:
1. grant the user select only on dbo tables.
2. make the user the owner of rpt schema.
3. Grant the user database permission on create table/create procedure/create view/create function.
My question is - in step 3, should I just grant "Alter" database permission to the user? Granting Alter seems to be cleaner and simpler. According to MSDN,
"Alter" confers the ability to change the properties, except ownership, of a particular securable. When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope.
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies View RelatedI have a problem with sql server merge replication in sql server 2000.
If my db owner €œdbo€? and replication setup under €œsa€? account it works with out any problem. But when I use another db owner it can not work properly.
For an example I have customer table ([dbo]. [Customer]) When I setup merge replication under sa account it€™s work properly.
Again I was setup merge replication using another db owner ([INV]. [Customer]) It doesn€™t work.
Hi there,
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..
DDL For this problematic table is as follows
CREATE TABLE [dbo].[AssignedCriteria](
[AssignedCriteria] [uniqueidentifier] NOT NULL,
[CriteriaName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TargetScore] [numeric](5, 0) NULL,
[HRPlan] [uniqueidentifier] NULL,
[ActualScore] [numeric](18, 0) NULL,
[Criteria] [uniqueidentifier] NULL,
[Employee] [uniqueidentifier] NULL,
[IsActive] [bit] NULL,
[addDate] [datetime] NULL,
[totalscore] [numeric](5, 0) NULL,
[isCalc] [bit] NULL,
[Weight] [decimal](18, 2) NULL,
[ProfileDetail] [uniqueidentifier] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_7FF25DF903B6415FBFF24AC954BC88E4] DEFAULT (newsequentialid()),
CONSTRAINT [PK_AssignedCriteria] PRIMARY KEY CLUSTERED
(
[AssignedCriteria] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Thanks.
Whenever I am doing reinitialization, publisher table schema is applying at subscriber level
I don't want to apply all changes ...
How can I skip the schema changes for transaction replication ...
The database will grow to 40GB in a short while and I intend to schedule replication every hour. Can this not be done using snapshot replication??
Subject:
From:
Date: Snapshot Replication - Help!! (reply)
MAk (mak_999@yahoo.com)
5/7/2002 12:02:08 PM
Create jobs to copy database and restore database in destination servers
------------
Robert at 5/7/2002 11:00:30 AM
Yes and I would rather not use dts to accomplish this task.
------------
Ray Miao at 5/7/2002 10:02:15 AM
Do you have direct network connection to remote server? Did you try dts?
------------
Robert at 5/7/2002 9:08:06 AM
I've been trying to replicate a database to an off site server using snapshot replication. It is scheduled to run every hour but I've noticed when data is changed at the source it never gets replicated to the destination. Does anyone know why?? I can't use transactional replication beause not all the tables have primary keys and they can't be added due to code. Some tables have id colunms and have been created with the Not for Replication option on the subscriber. Any help will be appreciated.
Thanks
Robert
I can set up snapshot replication for those tables without foreign key constraints. But if there are foreign keys in the table, there will be error
message indicating that this object can not be dropped because it is referenced by ....
Do you have any remedy for that? Thanks
2 questions:
1) In snapshot replication, can the subsciber send info back to the publisher (even in a manual process)
2) In snapshot replication, do we need a distributor set up between the publisher and subscriber if there will only be a single subscriber, or can we write directly to it?
Thanks so much for any and all help!
Hi Guys,
We have a production server in East Coast (SQL Server 2000 SP2 - Database size is around 30 Gig). We have a reporting server is the West Coast. We need to replicate (transactional replication every one hour) from East coast to West coast. Is there any way that I can take a backup and restore upto the last transaction backup and then start replication agent on the production (by saying schema and data already exist). Basically we don't wan't to snapshot using FTP or bcp through WAN because it is going to be very slow.
If this is possible, will there be any validation problem.
Please help.
Thanks,
Anu
Suppose i want to replicate data from server A to server B
I am using snaphot replication.I did the snapshot replication for the first time
and server B got a snapshot of server A.
Next time i run snapshot i want the incremental data to be replicated and not all..Is this possible in snapshot replication? If not which type of replication should i use?
Hi All,I have set up a snapshot replication, and schedule it to run everynight. The snapshot run successfully, and data get replicated to thesubscribed server. However, data do not get transfer as the second dayand there after. I check the job history, the job (distribution) runsuccessfully. I start the snapshot agent again, then data gettransferred. I can schedule the snapshot agent to run every night, butthis is just not the way it's supposed to be. Is there anyone out therecan give me some help. Thanks.
View 1 Replies View RelatedHi,
I am using SQL SERVER 2005 snapshot wizard to create snapshot. But as soon as i create a snapshot it takes away all the indexes and constraint for the tabels on the subscription end although i have this indexes and constraint on Publisher side.
Can someone help me in finding if their is some setting to create a snapshot without losing indexes and constraints or if their is some other way to do this.
Thanks,
Prashant
Hi is that possible to configure replication in the following situation.
server A is built by snap of server B. Because i am able to create publisher on server B but i am unable to create same on server A.
could any one explain the situation, why it is happening this way.
any help is appriciated.
if you are curious what kind of error i got.
TITLE: Publisher Properties
------------------------------
An error occurred connecting to Publisher 'SERVER A'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.PubshrPropertiesErrorSR&EvtID=CantConnectToPublisher&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVER B'. (Replication.Utilities)
------------------------------
BUTTONS:
OK
I am looking forward at performing a SNAPSHOT REPLICATION between a 2000-Publisher and a 2005-Subscriber. But on following the wizard the latter is unable to recognize the publisher.
Could anyone help to let me know the possible reason or if there is some setting which i may have missed out?
Thanks,
Varun
Hi Everybody:
We encounter a difficult problem in our production environment.
I have a merge replicaiton which contains 10 tables from Server s1 and database d1 to Server s2 and database d2. Among those 10 tables 5 of them are true merge tables ( carry data both ways, from publisher to subscriber and from subscriber to publisher). Other 5 tables I would like them to carry data only one way from publisher to subscriber ( never merge data from subscriber). Anyone has any suggestions how I impliment this?
Thanks in advance.
Joan