Labels

Replication Doubts in Microsoft SQL Server



  1. Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.
  2. Beginning with SQL Server 2005, you can move or rename files without detaching and re-attaching the database, with no effect on replication.
  • Take database offline
  • Alter database modify MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )
  • Take database online
  1. To add table in existing publication, it is not necessary to stop activity on the publication or subscription databases in order to add a table (or another object). Add a table to a publication through the Publication Properties - dialog box or the stored procedures sp_addarticle and sp_addmergearticle.
  2. Replication functions properly using any of the recovery models: simple, bulk-logged, or full. Merge replication tracks change by storing information in metadata tables. Transactional replication tracks changes by marking the transaction log, but this marking process is not affected by the recovery model.
  3. If a transactional publication supports queued updating subscriptions, replication adds the column msrepl_tran_version to every table. If the table is dropped from the publication, the msrepl_tran_version column is not removed.
  4. Replication provides automatic identity range management for replication topologies that include updates at the Subscriber.
  5. There are no restrictions on the number or types of publications that can use the same distribution database. (Multiple publications can use the same distribution database).
  6. Replication processing resumes at the point at which it left off if a connection is dropped
  7. TRUNCATE TABLE is a non-logged operation that does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables.
  8. For transactional replication, bulk inserts are tracked and replicated like other inserts
  9. CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.
  10. To drop column on published table execute ALTER TABLE … DROP COLUMN at the Publisher, and the statement is replicated to Subscribers
  11. Replication supports restoring replicated databases to the same server and database from which the backup was created. If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must recreate all publications and subscriptions after backups are restored.
  12. To modify any column in replication
       ----> Alter table alter column
       ----> Example: Alter table dept alter column Deptname varchar(100)

  1. How to remove replication: http://msdn.microsoft.com/en-us/library/ms152757(v=sql.105).aspx
  2.  To add or drop column in replication, we can use Alter table (but it will not be replicated automatically)  ---->Alter table dept add depttest varchar (20)







No comments:
Write comments

Please do not enter spam links

Services

More Services