Remove Primary Key On Replicated Database

Jul 31, 2006

I have a table that was set up with a primary key - that i need to change. The problem is that the database is being replicated to a remote location, and will not allow me to remove or change the primary key on a published database.
Due to the size of the database and bandwith limitations it is not an option to re-initialise the published database.
Is there any to do this without breaking replication

View 1 Replies


ADVERTISEMENT

Changing A Primary Key In A Replicated Table

Jul 20, 2006

Hi

We have a merge publication - I want to change the primary key for one of the tables (add another column to the primary key)

How do I do it ?

Currently there is no data in the table - which I guess might help..

thanks
Bruce

View 3 Replies View Related

How To Remove A Primary Key

Aug 15, 2005

hello friends!...

I am always workign in Query Analyzer..

I hav a created a table with a primary key set to its first column..

Now i want to remove the key from the column..

Is there any query to remove the key that has been already set to a column???

View 18 Replies View Related

How To Remove A Primary Key You Don't Know?

Nov 8, 2006

Hello Guys,

I need some help with a question I've been struggling to solve all day. I'm new to Transact-SQL and thus tried to solve my little problem by reading the language reference, but I didn't find what I was looking for.

Here's the scenario: I want to remove a table's primary key and replace it with a new one. Removing the pk is usually done with:

1) ALTER TABLE tablename DROP CONSTRAINT constraint_name; GO

Unfortunatley, I don't know constraint_name and the simple MySQL command

ALTER TABLE tablename DROP PRIMARY KEY

doesn't seem to work. To get constraint_name I made a query:

2) SELECT name from sys.key_constraints where parent_object_id = (select object_id from sys.tables where name = 'tablename')

Now, here's the core of my problem: I need to link statements 1) and 2) and tried

ALTER TABLE tablename DROP CONSTRAINT (SELECT name from sys.key_constraints where parent_object_id = (select object_id from sys.tables where name = 'tablename'))

but that doesn't, I get a syntax error.

Do you have a clue? I'm sure its a simple problem for an experienced programmer, but I don't get it. Or do you know another way of removing the primary key without having to state constraint_name explicitely?

Any help is appreciated!

Thanks a bunch,

adunak

View 3 Replies View Related

Move Replicated Database

Sep 2, 2005

Hi,
We have to move two of our servers in US hosting SQL Server 2000 database to different location. These servers are clustered using MSCS and uses SAN to store database data. The database on this cluster hosts publisher and distributor. The other two sites in Europe host updateable subscribers. We are using transactional replication. The downtime will be around 24 hours during which servers will be physically moved from one location to other. During this time we will keep the two subscribers running and let the changes queued on subscribers. I have listed following steps based on my limited knowledge of SQL Server. Could somebody please advise if this approach is valid and if I have missed any steps?

1. Make sure no users are accessing database
2. Backup database (In case of emergency need)
3. Stop snapshot, Log Reader, Queue Reader and Distributor Agents
4. Stop SQL Server Service and SQL Agent
5. Take OS backup
5. Shutdown the server
6. Ship the servers and OS backup to new location
7. Setup servers at new location and restore OS backup
8. Start SQL Server Service and SQL Agent
9. Start agents listed in step 3

Regards,
Nilesh

View 6 Replies View Related

How To Move Replicated Database?

May 8, 2006

I got Server A with 30 Databases and I was asked to move 30 databases to Server B…
But server A is having 2 databases as Publication and Distribution to two different servers….. and Server A is a subscription for 6 Databases from other servers….

Once I move the databases from Server A to Server B…… what is the best way to get the Replications too…. I mean Server B should have 2 Db’s as Pub & Distr and 6 Db’s as Subscription…

Is there any way I can script it out in Server A and run the same script in Server B with some modification…. so that the Replication work…..??

I have Jobs and Maintenance plans…. Which should also move from Server A to Server B…. I know using DTS...... is there any better way..??

Please some one give me ur suggestion on this…

Thanks
Bob

View 6 Replies View Related

Resotring A Replicated Database

Mar 3, 2004

I have a database A on server_1 that is being merge replicated to database B on server_2 which is production a box

NOW

I want to restore database A on server_3 that is being merge replicated to database B on Server_4 which is test box.

How about do I do that with replication being involved?

Thanx

View 2 Replies View Related

Alter Table In Replicated Database

Apr 26, 2003

How can I do an alter table in some table that replicated database ?
I got the error message when I try !

View 3 Replies View Related

Backup/Restore For Replicated Database

Jul 5, 2006

Hi, I have setup a merged replication on two server. Server A contains the Publisher and the Distributor database and Server B contains Subscriber.

If the database at the Subscriber got corrupted. I am thinking maybe i can 'restore' the subscriber database by resynchronising the data from the publisher to subscriber.

But what happen if the Publisher database failed? How should one restore the publisher database and then restore the replication setup?

What will be a good backup/restore strategy for such a setup?

View 1 Replies View Related

Moving A Merge-replicated Database For SQL CE

Apr 6, 2004

Is there a way to move a SQL replicated database from one server to another without dropping the publication?
I have SQLCE setup with SQL server 2000; I want to redo the server but needs to move my replication to a temp server and then back.
The problem is that I do not want to drop my subscription and recreate it…it needs to stay the same.

View 2 Replies View Related

Disk Configuration For Replicated Database

Jan 25, 2008

We are going to implement either a replicated database or use log shipping to another database to support our reporting needs. Basically we are moving reporting over data off our transactional database.

With this in mind, I have several questions:
Are there any special configurations in terms of disks for a database in this situation?What are the recommended raid types?
Also, we currently store our data in one filegroup and indexes in another so obviously I'll want those on 2 separate physical disks other than the C:. What about the log files? Do I care where they go (on C: or another physical drive separate from C:, data filegroup drive and index filegroup drive)?

Thanks!

View 3 Replies View Related

Move Mdf/ldf Files For A Replicated Database

Jul 23, 2005

Can someone lend some assistance in this? It sounds like it should beable to be done.We have a large replicated database in SQL 2000. We need to move themdf and ldf files to a location on another drive.Will doing this affect or break replication in any way? Is there anyway to prevent this?Thanks in advance.Glenn DekhayserVoyant Strategies

View 1 Replies View Related

Table Mantenance Within A Replicated Database

Jul 20, 2005

When I try to remove a table, it complains because it's part of apublication. I could script the publication and subscriptions suchthat they can be deleted, the table removed, and then added again.But, I not sure if this is the 'best' approach.Anyone faced this challenge before?Regards,gary

View 1 Replies View Related

Replicated Database Movement From One Server To Other

Oct 5, 2006

Hi All,

I have SQL server 2005 Database which is having following Replication stuff.

1. 6 merge Subscribers

2. 5 Snapshot Subscribers (Push Susbribers)

3. 3 Transactional Publisher

Due to the Performance Issue, there is need to move SQL server from the Current Server to an Higher End Server.

I want to keep all the Replication settings after movement of the Database. Can anyone tell me how to acheive this requirement?

Is there a possibility to keep the Replication settings ? Even we can have the Same System Name to the New Server.

Awaiting response...

Thanks,

Thams.

View 1 Replies View Related

Replication Help - Problem Restoring Replicated Database

Nov 27, 2002

I'm a novice with replication and am in need of direction on how to restore replicated databases. Server A replicates 5 databases to server B using transactional replication. Server A also contains the distribution database. I'm perfoming PUSH replication.

The development area asked me to restore Server A with a copy of production data located on server C. After I restored the data from C to A, I noticed that server A was no longer published. Is this normal?

I re-published the 5 databases, but noticed that the Log Reader Agent has now been running for 2 hours. I'm trying to give myself a crash course in replication, but am coming up empty regarding (1) the prefered method for restoring replicated databases and (2) determining why the Log Reader Agent is taking so long.

Any suggestions?

Thanks, Dave

View 8 Replies View Related

SQL 2012 :: Truncating Log File Of Replicated Database

Jul 14, 2014

I have a production database which uses merge and snapshot replication. The Merge is for 3 tables. The snapshot is to update the rest of the data once daily. I use a Full recovery model and perform database backups (full, differential) and transaction log backups.

I have a database optimization plan which runs 4 times a week. This plan performs and integrity check and rebuilds the indexes. This optimization plan is growing the transaction log by about 8MB each time it is run and we are running out of space on the drive for our log files. The space is not being reused.

I saw in another post where Gail Shaw suggesting using SELECT name, log_reuse_wait_desc FROM master.sys.databases to see why the log space is not being reused. On the database in question, the above returns "REPLICATION".

A colleague tried to backup the transaction log a couple of times to truncate the log this weekend. She was going to perform a DBCC Shrinkfile command afterwards. But the truncate failed. Again looking into things it seems replication prevented the truncation.

We are looking at stopping the merge replication or even removing it to truncate the log file and then recreate the merge replication. How to handle shrinking the log file for now and then seeing if there are any checks or changes I can perform which will allow the transaction log space to be reused.

View 7 Replies View Related

How To Configure Distributor Thread For Being Up While Replicated Database Is Down

May 1, 2006

Hi



I often deal with a problem when replicated database is down due to
electric outage or any other scheduled type of downtime. Primary
database is up but distribution agent is going down 'after 10 attempts
to connect to replicated database'. That's the problem I think because
not only distribution agent goes in undetermined state and I have to
restart sqlagent service, but I need to go through whole process of
snapshot and applaying it to replicated db. I would like it to
accumulate transactions in distribution database and once replicated db
is up - distribute them.

I wonder if anybody can tell me how to configure distribution thread so
it does not go down after 10 unsuccessfull attempts but stays for day
or two.



Thank you, Gene.

View 4 Replies View Related

Enumerate Subscriptions In A Merge Replicated CE Database?

Jun 27, 2007



How can I list all of the Subscriptions in a CE Database that was created with the .Net System.Data.SqlServerCe.SqlCeReplication.AddSubscription method?



After I create the CE database in an sdf file, I can connect to it with SQL Servr Mgmt Studio, then see the list of Subscriptions in the Object TreeView, under "Replication"



But I can't find that Subscription list in any properties or methods of System.Data.SqlServerCe.SqlCeReplication or System.Data.SqlServerCe.SqlCeConnection



Should I be looking in RMO features like Microsoft.SqlServer.Replication??



We want to confirm that a local CE database was last synced from the expected SQL Server database by checking the Publisher property of each subscription in the CE database.



Thanks

View 3 Replies View Related

Database Maintenance On Replicated Database

Jun 18, 2007

Hi,



I have transactional replication set up on a DB and run database maintenance scripts (index defrag, update stats, etc) on this DB daily.



Will the maintenance job/scripts also be propagated to the replica (subscriber) causing these maintenance operations to be performed on the replica OR do I need to create separate jobs on the replica DB (subscriber) also? I'm guessing the latter but wanted to verify to make sure I wasn't missing something. Thanks!

View 3 Replies View Related

Access2000 Database(table) Replicated To SQL 2000/2003 Server

Nov 4, 2006

I'm trying to replicate a table(s) in access 2000 to MS SQL server 2000/2005 programatically on a timed instance and then have these tables merged in SQL to create one table.

Any guidance appreciated.

Thank you,

View 1 Replies View Related

SQL 2012 :: Replicated Database Log File Grows Exponentially With Few Row Deleted / Inserted

Jun 29, 2015

I have a publisher database set up for a merge replication. This is using parameterized filter with join filters.

I also have a stored procedure that does deletes & inserts on the table where the parameterized filter is applied to aid in changing a subscriber's eligibility to receive so and so data. I have observed that running the stored procedure takes extraordinarily long and as a result, the log file grows to a size 1.5 - 2.5 times the database size.

At first I reasoned that this might because I had it set up to use precomputed partitions and changing it requires recalculating the partitions. As a test, I turned off the precomputed partitions. Didn't work. I turned on "optimize synchronization" AKA "keep_partition_changes", which normally is not available when you have precomputed partition on, and that didn't work, either.

At this point, I think I can rule out precomputed partitions being a problem here but I'm stumped now what else I should do to reduce the amount of log writes being required. We do need the parameterized filters & join tables, so that can't go.

View 0 Replies View Related

Failure To Create First Replicated Database (SQL Mobile/SQL Server 2005) / Err 28627

Jan 8, 2006

Hi,

I'm trying to get my first replication going, and I have set up a database successfully, along with merge replication, however when I attempt to create the first subscriber, I get a permissions issue, regardless of what I do. In the SQLCESA30.LOG file, I get the following entries:

2006/01/07 16:59:36 Hr=00000000 SQLCESA30.DLL loaded 0
2006/01/07 16:59:58 Hr=80004005 ERR:OpenDB failed getting pub version 28627


I posted the initial thread under the "Replication" forum, but am including this 'pointer' post here due to a lack of replies there, and the relevance of this thread to this forum. Please see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193693&SiteID=1 for more details.

 

Thanks.

View 1 Replies View Related

Remove Database From AG

Mar 23, 2015

i need to do patching of the application servers, some of the databases are configured in availability group .as part of this need to remove the database from availability and rejoin the db after doing patching .

View 5 Replies View Related

Cannot Remove Database

Nov 8, 2007

Loooks like I have a corrupt database. I cannot delete it because "database is used for replication" I am told in an error box . There are no items shown when trying to expand the database. Opening Query anylizer the database is not shown in the dropdown box. If I try to create a database with same name SqL says it is already there. Also the database icon is grey not yellow like the active working databases. Anyone have an Idea of how to get rid of this rogue database. This was a temp entry and no backup was made

Thanks

Keith

View 2 Replies View Related

Database Explorer In VB Express:AdventureWorks.mdf-Files Do Not Match The Primary File Of The Database

Nov 5, 2007

Hi all,

I downloaded and ran AdventureWorks.msi into my SQL Server Management Studio Express (SSMSE) one year ago.But I did not know how to attach it to my SSMSE then. Last week, I deleted it from the "Add or Remove" of Control Panel and I downloaded the new AdventureWork.msi and installed it my SSMSE. Today, I tried to use the Database Explorer of VB 2005 Express for the first Stored Procedure programming. I clicked on AdventureWorks.mdf and I got the following error: One or more files do not match the primary of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupt and should be restored from a backup. Cannot open user default database. Login failed. Login failed for user 'CENADe1enxshc'. Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data_log.ldf' does not match the primary file. It may be from a different database of the log may have been rebuilt previously. Please help and advise me how to correct this problem.

Thanks,
Scott Chang

View 9 Replies View Related

SQL Server Management Studio: Can Not Remove AdventureWorksDB In Add Or Remove Programs Of Control Panel

Nov 30, 2006

Hi all,

I tried to remove AdventureWorksDB in the "Add or Remove Programs" of Contol Panel and I got the following errors: (1) AdventureWorksDB     Error 1326: Error getting file security: CProgram FilesMicrosoft SQL ServerMSSQL1MSSQLGetLastError: 5.      |OK|   and (2) Add or Remove Programs   Fatal Error during installation (after I clicked the |OK| button).   Please help and tell me how I can solve this problem.

Thanks in advance,

Scott  Chang 

View 1 Replies View Related

How Do I Clean Up The SQL Server (ctp) From ADD/REMOVE Program Without The Change/remove Button

Oct 12, 2006

I have uninstalled the CTP version of the SQL Server express so that I can install the released version but CTP version is still listed in the add/remove program list but without the change/remove button. I have been to different sites to find information on cleaning this up and I have ran all the uninstall tool I can find but the problem still prevails. I cannot install the released version without completely getting rid of the CTP version. Please help anyone.

Thanks

deebeez1

View 1 Replies View Related

Remove All Constraints In A Database

Jun 11, 2007

Hi Everyone..
 I want to remove all the constraints from all the tables in a database. I'm using SQL Server 2000.
will you please help me.. Thanks in advance
 
with regards
Fraijo

View 1 Replies View Related

SQL 2012 :: Database Remove From AG

May 19, 2015

As part of patching i need to remove the database from availability group and add the database after patching is done , how can i achieve this ?

View 2 Replies View Related

Database Table Primary Key Question For Database Experts

Jan 17, 2008

Hi, I have a Users table that I use for membership. Now I am using username varchar(30) as the primary key for this table since username will always be unique.I am storing username in UsersInRole table so that I can find what roles each user has.The question I have is regarding how SQL Server actually stores data:I see that when I add users, they are always stored alphabetically sorted on username. I was expecting that all users will appear on the users table in the order they were added.Example: I have 3 users (john, jonah, wilson). Now I added 4 user with username='bob'If I execute select * from users, it returns me (bob, john, jonah, wilson). Look bob is has become the first row of the table.My question: Is Sql server moving 3 older rows to make room for 'bob' and it is also rebuilding part of the index due this new username 'bob'?If this is the case, then it will have big impact if I have 100K users and I add one user that becomes firstrow. In that case huge other rows will have to move.Bottom line, insert, delete will be expensive. I know sql server keeps data physically sorted on PK. But I am concerned here since rows are losing the order in which they were inserted.Thanks  

View 8 Replies View Related

SQL 2012 :: Partition On Log Shipping Database (Primary Database)

Jun 11, 2014

I would like to do partitioning one of my client, but that primary database is participating in log shipping.

If I do partitioning on primary, automatically apply those changes on secondary db? if not, what is the way?

Note: I am adding new filegroups and ndf file in different drives on primary database.

View 2 Replies View Related

Change Database With A Different DB On Server X That Is Being Replicated To Server Y

Nov 16, 2015

I have Transaction Replication setup on a Server X. Server X is publisher and distributor. Server Y is the Subscriber.Everything works perfectly.  But I need to change the Database with a different DB on Server X that is being replicated to Server Y. I have following questions:
  
  1-  Do I create a new distributor on Server X and delete the current distributor?
   2- Create a new Replication Setup on Server X and Y ? 
   3- Delete the subscriber DB on Server Y (Subscriber) ?
   4- Can I just disable the current setup on Server X and Server Y instead of deleting it

View 5 Replies View Related

How To Remove Case Sensitivity In Database

Mar 2, 2008

Hi All,
How to remove case sensitity from database like table names,column names etc.
 If we type either select * from AUTHORS" or "select * from authors" should result the same value.
Abdul
 
 

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved