Transactional Replication Important note: If you have replication in your environment. First script out distributor and publication including property. Script out each publication. List out how many tables / objects are added into replication with table name, any filter applied etc. Also, generate script out of subscriptions database, in case if we reapply the snapshot the existing object will drop and created. If we have any triggers, additional index that won’t come. We need to create those after reapply. Design: Node1 – distributor & Publisher. One publisher with many subscribers. Pre request: Repl_Dist_Pub_connection — sp_adddistpublisher@login mainly used to connect distributor and publisher initial configuration. Remember or store the password for adding another publisher…
-
-
When you have a replication in your environment what pre requesting we need to do 2
When you have a replication in your environment what pre requesting we need to do. 1. Generate script out of subscriber database, include Trigger, indexes etc, useful for snapshot reapply. 2. Find which server is Distributor (Local same server or remote / different server), Publisher (All publication) And subscriber (All subscription) 3. Find out how replication topology designed. Ex: How many distributor, publisher and subscriber, one publication with many subscriptions or one publication with one subscription etc. 4. Script out all distributor, publication and subscription Go to replication folder in SSMS -> generate script of Distributor alone and save it Then choose generate script out of publication property and save…
-
Replication Technology SQL server 1
Replication Technology Replication is a set of SQL agent job which will be created while configuring replication. This Replication SQL Agent job will copy and send data and database objects (Tables i.e Articles, SP, Views & UDF etc) from one database to another database. There will be three roles in replication i.e. Source DB server is publisher and destination/Target DB server is subscriber. In between there will be distributor DB server. All three will communicate with each other. We can have all 3 in the same servers or 2 severs or each one will be in dedicated server to configure replication. It is based on client requirement and DBA needs we choose…
-
Table Partition Remove part 9
Remove Table Partition. --Remove table partition completely -- Drop PK or CI DROP INDEX Cx_tbl_partition_test on [tbl_partition_test] /* drop the clustered if already exists if not create new CI on primary FG*/ ALTER TABLE [dbo].[tbl_partition_test] DROP CONSTRAINT [PK_C1810429_tbl_partition_test] WITH ( ONLINE = OFF ) /* Create clustered index on the table to free it from the partitioning scheme; Here, the "ON [primary]" or Dummy part is key to removing the partition scheme from the table ! */ CREATE CLUSTERED INDEX Cx_tbl_partition_test ON [tbl_partition_test] (ID,Date_Col) ON FG_Dummy DROP PARTITION SCHEME PS_myPartitionScheme DROP PARTITION FUNCTION PF_myDateRange /* drop the dummy clustered index you created, as it was only created to free the…
-
Table Partition performance Tips part 8
Index aligned vs non aligned To have an Aligned index it should have same FG as partition resides and index structure should be match or the index column and partition key column of source table should match to switch in and out. In our switch example, I have created non aligned index and switch was failed with the following error. Msg 7733, Level 16, State 4, Line 147 ‘ALTER TABLE SWITCH’ statement failed. The table ‘Test_Tbl_Partition.dbo.tbl_partition_test’ is partitioned while index ‘Ix_tbl_partition_test’ is not partitioned. Partition Elimination Data partition elimination refers to the query optimizer is ability to determine, based on query predicates, that only a subset of the data…