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
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
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???
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
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
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
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
How can I do an alter table in some table that replicated database ?
I got the error message when I try !
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?
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.
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!
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 RelatedWhen 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 RelatedHi 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.
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
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.
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.
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
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!
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,
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.
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.
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 RelatedLoooks 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
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
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
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
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
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 RelatedHi, 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 RelatedI 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.
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
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