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

SQL
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 GateThe gate is in the operational database
 SQL SERVER - How to Reach Out to Cloud - Cloud Computing with PartitionDB partitiondb5  SQL SERVER - How to Reach Out to Cloud - Cloud Computing with PartitionDB partitiondb7

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.

SQL SERVER - How to Reach Out to Cloud - Cloud Computing with PartitionDB partitiondb6

Step 1) Create a Gate

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

SQL SERVER - How to Reach Out to Cloud - Cloud Computing with PartitionDB partitiondb9

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.

SQL SERVER - How to Reach Out to Cloud - Cloud Computing with PartitionDB partitiondb2

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’,
@DatabaseName=’BayMartCloud’;”

SQL SERVER - How to Reach Out to Cloud - Cloud Computing with PartitionDB partitiondb1

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

SQL SERVER - How to Reach Out to Cloud - Cloud Computing with PartitionDB partitiondb4

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:

SQL SERVER - How to Reach Out to Cloud - Cloud Computing with PartitionDB partitiondb3

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)

,
Previous Post
SQL SERVER – Unable to Get Listener Properties Using PowerShell – An Error Occurred Opening Resource
Next Post
SQL SERVER – AlwaysOn Availability Group Listener – This TCP Port is Already in Use

Related Posts

1 Comment. Leave new

Leave a Reply

Menu