Creating Objects

When it comes to creating objects such as: view, schema, type and synonym PartitionDB does the work for you. Keep creating them the way you always did, and PartitionDB will make sure to replicated them to the partition databases. This is different from tables and stored procedures and functions that must be explicitly published in order to replicate them to the partition databases (read more about creating tables here and stored procedures here).

What happens upon create object

When you create a view for example. the view will be created at the gate, and automatically be replicated to the partitions. This means that all the maintenance is done for you, and your code should not be changed.

Here is an example of creating a view

After running this code we see that ‘view1’ is replicated to the partition databases:

In the same manner, we create a synonym:

We see below that the synonym was replicated to all the partition databases.

Creating a type will follow the same idea, replicating it to the partition databases:

What if you want to control which objects are replicated to the partitions? Or you are responsible for managing different versions on the different partition databases? In this case, you can decide which object to replicate using the publish command.

Publish an object

In order to avoid replicating objects automatically to the partition databases, you need to set the ObjectPrivateMode gate attribute. Having it set to true, will cancel the replication:

exec pdbupdate  @gateName = 'TwoWestGate', @ObjectPrivateMode = 1

Now, when you create a view it will reside at the gate only.

The returned message tells us that ‘view3’ is create on the gate, and that we may wish to consider to publish ‘view3’.

In order to replicate it you need to call the corresponding publish API (PdbPublishView in this case) as it is recommended in the message. The publish API will also let you decide if to replicate the object to all partitions or to a specific partition. This comes handy when keeping different versions on the partitions.

Here is how to publish our object ‘view3’:

exec PdbpublishView 'TwoWestGate','dbo','view3',@DatabaseName='SunsetDB',@IsEncrypt=0;

Running this publish command replicates ‘view3’ to partition SunsetDB only, and not to the other partitions. If you wish to replicate ‘view3’ to another partition, call PdbpublishView again.

All other objects follow the same rule. In order to publish an object, use the matching publish command; PdbpublishView  for view, PdbpublishSynonym for synonym, PdbpublishType for type and so fourth.