Replication: Any Way To Change Table Schema W/o Reinitializing Subscriptions
Mar 23, 2004
Hi all,
I'm admitedly a bit new to the world of replication, so please bear with me. I've got two SQL Server 2000 servers running in different locations. Server A does transactional replication over a push subscription to server B. If I need to make a minor change to one of the replicated tables (for example, dropping a no longer used column or changing a varchar field's length) do I need to drop the subscription, make the changes and then re-initialize the schemas and data?
For minor changes, I really hate having to knock out the site runnign off server B while the subscription is re-initialized and data is bulk copied back over. If I want to just make the changes manually on both servers will that cause problems down the line?
Any help is greatly appreciated.
tia.
-m
View 2 Replies
ADVERTISEMENT
Aug 27, 2007
Hi,
I'm doing merge sync between SQL Compact on mobile devices and SQL Server 2005. I recently made a schema change on the server and noticed that it caused an error on the subscribers when syncing. I fixed the schema problem, and did a reinitialize all subscriptions with a new snapshot. On the mobile devices I forced a reinitialisation (upload changes first). I'm still getting the same error message, which relates to a schema change which is no longer relevant to the server db and snapshot. For some reason the subscriber dbs are trying to reapply the original problem schema change even though they're supposed to be reinitializing to a new snapshot. I've even tried dropping the affected tables from the publication, and reinitializing all subscriptions with a new snapshot, but still the same error on the subscribers. Does anyone know why the subscriber dbs are still trying to apply a now defunct schema change (on tables which no longer exist in the publication)? Is there any way to flush this from the subscribers so they correctly pick up the new snapshot?
Regards,
Greg McNamara
View 2 Replies
View Related
Apr 16, 2007
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
View 7 Replies
View Related
Jul 14, 2015
Question re Merge rep (pull) and processing order.  We have a group of changes associated with an app upgrade, the scripts run fine on the publisher.   Part of the change includes creation of a new table , followed by altering a view to use new table.Following the change at the publisher, when the sync is kicked off from the subscriber, it fails - the alter of the view throws an'invalid object' error with regard to the new table. Seems as if the view alter is attempted before the dependant table has been created.Â
I have tried to amend the processing order of the view using sp_changearticle, which executes (quickly) with a 0 return code.But it is to no avail , the error still occurs.  is it possible to change the processing order for a view article , which will be applied to schema changes ? Have
View 4 Replies
View Related
Aug 10, 2006
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
View 4 Replies
View Related
Jan 11, 2007
We have a two SQL Server 2005 databases set up using Transactional Replication. My manager has asked me to set it up to replicate constraints (default, fk, et) on the tables in the main publication. I said sure, I can do it, but I will have to re-initialize the subscription. He said there was a way to do it without re-initializing the subscription. I cannot find anything in my research to indicate that there is a method to do so (via call sp_ functions, etc). Is it indeed possible to replicate constraints on replicated tables without re-initializing the who subscription? The reason he does not want me to perform a re-initialization is that we are a few weeks before moving into production, and a full re-initialization takes 1.5 days, which would impact system availability.
Thanks in advance for any advice.
View 1 Replies
View Related
Jan 24, 2007
I am using SQL 2000. How can I get my transactional replication reinitialized after it has failed with several attempts.
I know one way of doing it through enterprise manager and specifying the subscription to reinitialize. But this will apply the snapshot and will take long time.
Is there a fast way of doing it?
-Nipul
View 1 Replies
View Related
Dec 9, 2013
I have created a table on SQL Server from SAS. The table gets created fine. However, the table schema has my user ID in it (AD-ENTmyuserid.Table1). How can I change the schema to dbo.(dbo.Table1)? It's fine if I have to make this change in SQL Server Management Studio.
View 4 Replies
View Related
Oct 16, 2015
Is to Possible to Create a Triggers to capture Schema (alter table, Drop table) Changes only for certain tables.I don’t want schema change for entire database.
View 7 Replies
View Related
Aug 8, 2007
Hello Everyone,
I am working on a dtsx package wherein i am sending the data from OLE DB Source (SQL Server) to OLE DB Destination (Oracle). For development purpose i use DEVLOPMENT environment on oracle but for unit testing i have to use QA or Some other Schema. when i use DEVELOPMENT Schema in ole db destination, tables are accessed under Schema name eg. "DEVELOPMENT"."EMPLOYEE", but when i m chenging schema name to QA table names are not changing as "QA"."EMPLOYEE". Data Flow Task is pushing the data to DEVELOPMENT environment only.
Can Anyone suggest me any remedy for it ?
Or this is one more BUG in SQL Server 2005.
Advice and suggestions are highly appreciated !
Thanks
View 3 Replies
View Related
Jan 16, 2004
I am still having problem with making View automatically updates itself when the underlying table schema changes. Running sp_recompile on the view table doesn't seem to work either, as I am still getting old format from the view (in Design mode the view returns the right info, but not when I open the View by doing Open View) even though the underlying schema has changed. Right now I find that I have to go into the View and change it a bit to force a recompilation.
And even if sp_recompile does, it would require that I manually do it each time I change a table. Any idea?
View 4 Replies
View Related
Jun 30, 2015
How can I easily identify who dropped a table?
View 8 Replies
View Related
Jun 2, 2006
We are having problems when creating new subscriptions or when trying to edit existing subscriptions. When editing an existing subscription, the report manager displays "An Internal error occured" message and when I look in the log the error says
Only members of sysadmin role are allowed to update or delete jobs owned by a different login
When trying to create a new subscription the report manager displays "An internal error occured" and the log says
System.Data.SqlClient.SqlException: The schedule was not attached to the specified job. The schedule owner and the job owner must be the same or the operation must be performed by a sysadmin.
We are running sql reporting services 2005 sp1. The report server database server is on a different machine to the reporting server. The existing subscriptions were set up previously on another report server which was originally our dev server. These subscriptions are working just can't change them, or create new ones.
Any help is much appreciated.
View 23 Replies
View Related
Dec 17, 2007
I have a merge where the subscriber can do changes to its tables ... But as I found out the changes are made only if publisher simulate an row change or changes something effectively
I'm not sure that at every replication the publisher table would be changed so i need something to simulate a change in the publisher table
Can anyone please help me with this ?
View 2 Replies
View Related
Sep 27, 2006
Hi everybody, I'm quite new to SQL 2005 and I€™m trying to understand some key concepts regarding replicas. I need to develop an application with characteristics similar to the Sales Order Sample for Merge Replication, on the client side it should run with the express version of sql server and also the synchronization should only work via web. I try to run the sample but I got an exception in the CreateSubscription method on invoking publisherConn.Connect();
TITLE: Microsoft.SqlServer.ConnectionInfo
------------------------------
Failed to connect to server XXX.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
I can€™t understand how this connection could work if it isn€™t aware of the fact that it should use https to connect with the server.
Can someone point me in the right direction?
Thanks
Angelo
View 6 Replies
View Related
Jun 13, 2007
I have been trying to set up replication with updateable subscriptions. I first followed the tutorial on MSDN and set up basic transactional replication. This all worked fine. I then tried, using that tutorial as a basis, to set up replication with updatable subscriptions.
On the Agent Security page you are asked for a Snapshot Agent, a Log Reader Agent and a Queue Reader Agent. I assigned these to the following accounts, which I created and added as logins, PUBLISHERSERVER
epl_snapshot, PUBLISHERSERVER
epl_logreader and PUBLISHERSERVER
epl_queuereader.
I then tried to set up a subscriber on SUBSCRIBERSERVER. Under the publication I add new subscriber, select the publisher, add SUBSCRIBERSERVER as the new SQL server Subscriber. In the Distribution Agent Security page of the wizard it asks for process account, which will be run when synchronizing the subscription. I entered PUBLISHERSERVER
epl_distribution here and selected the other two default options of connecting to the distributor and subscriber by impersonating the process account. I then took the default options for the next few screens and finally get to:
Login for updateable subscriptions. This offers the option of a login or using a linked server. I have tried various logins here, initially trying the €˜sa€™ login and password. This produced the error: The user is not associated with a trusted SQL Server connection. The servers are set up for mixed mode operation. I then tried PUBLISHERSERVER
epl_distribution and subsequently every other account I had created on PUBLISHERSERVER. All of these failed. I tried linking the servers, but this also failed. How exactly do you set up subscriber with the ability to login to the publisher? I have spent days trying to set this up, and am as you can gather new to this technology, any help would be greatly appreciated.
View 3 Replies
View Related
Apr 5, 2006
I have recently deployed a sql replication system into a production environment and I€™m experiencing a few problems.
I have a server holding consolidated data (4 processor/2GB ram) and fifteen subscribers, each being a 400Mhz Celeron with 256MB ram.
Transactional replication is used to pull base data from the main server to the subscribers. This data does not change very often at the publisher, but the subscribers need an immediate update, so continuous transactional replication is configured at the subscribers. This data will never be changed at the subscriber.
The server has another three tables used for historical data. Each subscriber has been configured for merge replication on these three articles. An initial snapshot is run to setup each subscriber. Each subscriber writes up to 10 records per minute into these local tables and continuous merge replication replicates these 10 records from the subscriber to the publisher. As all subscribers are doing the same, part of the merge operation will potentially bring down 140 records to each subscriber (10 records from each subscriber merged to the publisher and back to each subscriber).
Anyway on to the problem€¦
Each subscriber system has a Microsoft.Net application writing these 10 records per minute to the local database. Refreshing a graph within the application occurs once every minute. Refreshing, queries the three local tables used for the merge replication. This application shows an error (randomly) with a sql timeout message trying to get a connection to the local database.
Has anybody got any ideas why this will occur?
View 4 Replies
View Related
Jul 8, 2015
I get the following error message when a job calls a Stored Procedure that TRUNCATES a Table:
Cannot truncate table 'CombinedSurveyData' because it is published for replication or enabled for Change Data Capture
Is my only option to change the TRUNCATE to DELETE?
[URL]
View 2 Replies
View Related
Feb 27, 2004
If I have one table in one publication in transactional replication
replication between primary and replicate is broken because subscription is marked as inactive.
If there are 3 rows on replicate and 5 rows on primary , out of which 2 are added after replication is broken
If I do
Reinitialize subscriptions
start the snapshot agent
start the distribution agent
Does this mean that it will only transfer the new 2 rows to replicate sites ? or will it drop everything from replicate site and apply all rows from primary site on to replicate.
Any help is appreciated
View 1 Replies
View Related
Nov 29, 2006
Hello,
I have the following situation. I have a single publication on my publisher Server. This publication is created using SQL Server Manager. Snapshot is created completly. Now I want several Pull subscriptions from several machines to work with this publication (One subscription per machine). I'm creating these subscription using RMO. I'm synchronizing data using RMO again. My code workflow is:
Syncronize -> success -> do nothing
-> fail -> Check if everything with Publisher and Publication is ok -> Generate Snapshot -> Create Subsscription -> Sync again. (I tried to eliminate Generate Snapshot step but couldn't because I receive error that I must rerun Snapshot Generation.)
When I tested my code per single machine it's working. Next test I tried was to run my program on 2 machines simultaneously.
The result is:
---> System.Data.SqlClient.SqlException: Another snapshot agent for the subscription(s) is running or the server is working on a previous request by the same agent.
or:
---> System.Data.SqlClient.SqlException: Unable to acquire the replication merge administrative application lock for database 'XXX'. This could be due an active snapshot running while the schema change (DDL) or the administrative proc change was attempted.
Replication merge admin stored procedure 'sp_changemergepublication' failed for publication 'YYY'. This could be due an active snapshot running while the admin proc was called.
The problem is obvious but because I'm new to the replication I'm not sure if I did general mistake in what I want to achieve. Any advices how can I fix my problem will be highly appreciated.
View 5 Replies
View Related
May 24, 2012
So, Microsoft decided that they were deprecating Transactional Replication with Updatable subscriptions. In that case, you have 2 options (if I am correct): Pay for Enterprise (if you are already not) and use peer-to-peer or use bidirectional transactional replication which is basically setting up a transactional from db1 to db2 and also transactional from db2 to db1.
The issue I see in both cases is conflict resolution. With updatable subscriptions, you could specify how to handle the conflict. With either of these 2 options (from what I can tell) you cannot allow the engine to handle this for you.
Any thoughts? Seems like a slap in the face to those who have been using MS for years and a damn good reason for companies that rely on updatable subscriptions to not upgrade to 2012.
View 7 Replies
View Related
Jun 22, 2006
I am executing sp_dropmergesubscription, but the rows are still in dbo.msmerge_subscriptions, and are still shown in the replication monitor as expired; the last synch dates were in april (expiration is set to 10 days). The 'expired subscription clean up' job appears to be running okay. I need to remove these subscriptions from the publisher as the subscribers are mobile devices, which sometimes are lost.
View 5 Replies
View Related
Oct 28, 2006
Replication Type: Transactional With Updateable Subscriptions
SQL Server Version: SQL Server 2005 Enterprise Edition
Publisher, Subscriber1 and Subscriber2 all on same SQL Server
Problem: I am trying to set up a transactional replication with two updateable subscriptions. All three databases are a carbon copy of each other. Every table to be replicated contains an identity key column. I've been asked to create the replication without assigning separate identity ranges for the publisher and the updateable subscriptions. In other words they want a continous ID range. Can this be done without managing identity ranges programatically outside of SQL Server itself?
Example:
Publisher inserts into table test1 in database1. This creates ID 100 for the record. ID 100 replicates to Subscriber1 (database2) and Subscriber2 (database3).
Subscriber1 and 2 identity seeds are then incremented to ID 100. So far, all three databases have the same identity seed in table test1. Subscriber1 inserts into table test1 in database2. This creates ID 101 for the record. ID 101 is replicated to Subscriber2 (database3) and to the publisher (database1).
Subscriber2 identity seed is then incremented to ID 101. However, the Publisher retains it's original identity seed of 100.
Insert into Publisher table test1 will fail since it will try to insert the identity seed of 101 which is taken.
I have tried identity range management Automatic and Manual. Both seem to have some limitations in managing identity seeds unless different identity ranges are assigned for publisher and subscriber.
Can someone recommend a solution to this problem? Can this be done natively in SQL Server 2005 replication. Or can some recommend another approach to make this work?
View 3 Replies
View Related
Mar 29, 2007
I set up a sql server 2005 database as publisher for about 50 sql mobile subscriptions. Yet in the replication monitor windows, it shows in subscription columns is 26549. Does it mean I have 26549 subsriptions or it means 26549 synchronizations?
thanks,
View 1 Replies
View Related
Aug 30, 2007
Greetings:
I am working on a replication setup using transactional replication using with pull subscriptions and a separate distributor. The pull subscriptions are located on a SQL cluster using the virtual SQL Server name as the subscriber; when the box fails over, we get an error of missing replication.dlls. Researching further, we found that replication only works on one node of the subscriber.
Any ideas on what we did wrong in the setup?
Thanks,
Lee Everest
www.texas2oo.com/sqlblog
View 6 Replies
View Related
Apr 3, 2007
Hello,
If I want to access or modify my local subscription data(not the configuration) how can I do that(from sql server 2005 or from asp.net)? Also can i update directly to the local subscriptions data or do I need another layer which will update from a table for example??
Thank you
Salah
View 3 Replies
View Related
Apr 27, 2001
Hello,
I need to know if the anyone has had success using any sort of tracking tool to manage schema in SQL 7. I'm responsible for several SQL servers, each with upwards of 10 databases - most still in deelopment phase. We use Erwin to store our models and Visual sourcesafe for stored procs and sql scripts.
But now as a Dba I'm looking either:
(1) create a custom database to keep track of schema changes (as they movefrom development to test to prod). OR
(2) buy a 3rd party tools to keep track of schema changes? Platinum?
It's getting too much for a single Dba to manage a busy devel shop with 20 db's. Schema is comming in too often. Only some of our db's are in Erwin.
thanks,
View 1 Replies
View Related
Nov 9, 2006
Is it possible to turn off transaction logging when making a table schema change? For example, when expanding a varchar field from 10 to 40 characters? This is occurring on a hosted site for a table with about 150,000 records. The db size is 200 Mb. If I try this normally with transaction logging enabled, I get the dreaded log file is full message, even if I first truncate it.
I was thinking about doing the following instead:
taking the db offline
creating a backup
disabling logging
change the schema
re-enable logging
put db back online
If a problem occurs during the schema change, I would just restore from the backup. Please let me know the following:
Is it possible to turn of the transaction logging for the schema change?
Do you see any problems with the above.
Alternatives?
One alternative is to look into increasing the db size. A second is to add a new field (named temp) to the table, copy the old field to the new field, delete the old field, add another field (with the original field name) with the new schema, copy the temp field to the new field, and finally delete the temp field. This should require less space in the transaction log. Unfortnately, it could possibly affect some linked Access databases with the new field order.
Thanks in advance.
View 5 Replies
View Related
Feb 1, 2008
There is a report when you click servername, report and run SCHEMA CHANGE HISTORY
I had my SQL 2005 running for a few weeks and this is many listed from day started is there a way to recycle this and clean it up on a weekly basic
View 5 Replies
View Related
Aug 8, 2007
Simple question, I hope. I need to add a column to a table of a database that is mirrored. How do I have to do that? Do I need to stop mirroring? Is it sufficient to simply pause mirroring? If I make the change on the principal db, what do I need to do the make the same change on the mirror?
Thanks,
View 6 Replies
View Related
Sep 12, 2006
Hi,Is there a way I can change schema name on tables and stored procedures? How do I do this?I´m very news to SQL and .netThanks
View 1 Replies
View Related
Apr 22, 2008
Hi,
While i trying to drop the user,i getting the following error:
(Microsoft SQL Server, Error: 15138)"
Now,i wanna change the owner of that schema,Which is associated with the schema,which i am trying to delete.
How to transfer the owner?
Plz help me.
View 1 Replies
View Related
