SQL SERVER – How to Reach Out to Cloud – Cloud Computing with PartitionDB

1 Comment

Cloud Computing is not new anymore; everyone is talking about the cloud. Has everyone already touched the sky?  

Apparently not. There are many organizations around that hesitate to make the move, or cannot because of compliance issue or other limitations.

How about adopting a Hybrid cloud approach? This means keeping some of the data on premise, while expanding to the cloud with other data. The cloud data can be new data, or non operational data or any other way of data distribution that fits the business’s needs. The hybrid solution reduces the risk and keeps the crucial data close to the business while gradually establishing cloud traction.

Sounds like the perfect solution. But how do you start?

A company named PartitionDB identified the need for data distribution. It provides a simple solution to partition a database and manage it as a single database, while the partitions reside anywhere, on premise or in the cloud. This is a natural hybrid cloud solution that lets you work exactly as before. This is definitely something you want to try…

The below example is a great introduction for anyone that has never worked with the cloud before. It shows how to distribute a database in which the operational data reside on premise and the non operational data is in the cloud.

A Gate

PartitionDB engine is called a Gate. It is responsible for managing and controlling the distributed databases.  

The Gate can be created as a separate database, or to be integrated into the on premise database. The following diagrams depict the two different options:

Separate Gate The gate is in the operational database

The article follows the second case of Gate combined into the operational database.

Before you start

In order to run a hybrid service (as well as this example), first set up both environments; on premise and cloud. Please see instructions here.

Ready to go

This demo is using BayMart database. Refer to PartitionDB Hybrid Example for a full description.

Step 1) Create a Gate

A gate should be created as part of BayMart database. The following commands create the Gate:

Step 2) Create a database in the cloud

We currently have a BayMart database on premise. Now we create a database in the cloud:

We use BayMartCloud as the cloud database name.

The ecosystem is ready, and we can continue to work as before. Yet, now we can store data remotely, in the cloud.

Time to work

Let’s start with an example of creating a table in the cloud.

As we can see, the table is created, but we now want to have it stored in the cloud and not on premise. To move the table to the cloud, copy and run the command that is printed in the Messages area:

exec PdbtargetSplitTable @GateName=’BayMart’, @SchemaName=’dbo’, @TableName=’CustomerLogins’,

The table is now located in the cloud database ‘BayMartCloud’:

Note that database distribution can be applied to any object: tables, stored procedures, views etc.

It’s time to really take advantage of the hybrid environment that we have just set up. You keep working as before, and there is no need to change your code. You can run queries that cross over the two databases:

This script runs an inner join between two tables, Customers that is located on the premise and Customer Logins that is in the cloud. The code is the same as if the two tables were located in the same database.

In the next article I will talk about cross hybrid solutions to distribute a database between few machines and even between different clouds.

Meanwhile, checkout – PartitionDB.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Exit mobile version