June 2011 – Page 3 – SQLXpertise

SQL Server Compression Estimator

SQL Server Compression Estimator is a free tool available through CodePlex (http://ssce.codeplex.com/) which helps you in estimating the best compression for your SQL server objects. It estimates both Row and Page compression and also provides option to generate script for altering your SQL Server objects to implement compression.

My great thanks to MajikbyboB for providing this tool to the community for free.

Let us see how to install and use this tool

Download and extract the setup from the above site and extract the zip file and run setup.exe

Click “Next” to proceed

Select the installation folder and then click “Next” to proceed

Click “Next” to confirm the installation and proceed

Click “Close” to finish the installation

Now we have successfully installed the tool, Let us see how to run and estimate compression on databases

Run “Compression Estimator” and specify your Server name and select authentication method and provide required credentials and click “Connect”

Click on Database combo and select your database, and then specify the savings threshold.

Savings threshold identify what objects to be reports based on the saving in size due to compression

If you want to include index maintenance ratio, select the “Include index maintenance ratio” check box.

Click “Process” to proceed

Once the processing is complete, you can see the list of objects which can be compressed with the following details

1. Type of Compression (Page or Row)

2. Current Size

3. Compressed Size

4. $ of Savings

You can save the estimation results as CSV file by using “Save Results” option for your reference.

You can select the required changes to be made and then click on “Create Script” to generate the SQL Script to make compression changes

Sample Script generated

— This compression script was created by Compression Estimator
 
— By applying PAGE compression setting, you should obtain 
— approximately 53.33% compression
ALTER TABLE [Production].[Product]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 53.70% compression
ALTER INDEX IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode ON [Person].[Address]
REBUILD WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 63.64% compression
ALTER TABLE [Production].[BillOfMaterials]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 59.09% compression
ALTER TABLE [Purchasing].[PurchaseOrderHeader]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 56.25% compression
ALTER INDEX AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode ON [Sales].[CurrencyRate]
REBUILD WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 60.00% compression
ALTER TABLE [Production].[ProductListPriceHistory]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 60.00% compression
ALTER INDEX IX_ProductReview_ProductID_Name ON [Production].[ProductReview]
REBUILD WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 60.00% compression
ALTER TABLE [Production].[ProductCostHistory]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 60.00% compression
ALTER INDEX AK_Employee_LoginID ON [HumanResources].[Employee]
REBUILD WITH (DATA_COMPRESSION = PAGE);
 
— By applying ROW compression setting, you should obtain 
— approximately 60.00% compression
ALTER INDEX IX_ProductReview_ProductID_Name ON [Production].[ProductReview]
REBUILD WITH (DATA_COMPRESSION = ROW);
 
— By applying PAGE compression setting, you should obtain 
— approximately 69.62% compression
ALTER TABLE [Production].[WorkOrder]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 67.25% compression
ALTER TABLE [Production].[TransactionHistoryArchive]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 57.58% compression
ALTER TABLE [Purchasing].[PurchaseOrderDetail]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 73.25% compression
ALTER TABLE [Production].[WorkOrderRouting]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 64.86% compression
ALTER TABLE [Production].[TransactionHistory]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 65.31% compression
ALTER TABLE [Sales].[CurrencyRate]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);

I hope you all find this information useful. Please post your comments !!!

SQL Azure Videos

Microsoft Windows Azure website has posted lot of videos to learn about SQL Azure

8 Introductory Videos (Duration 5 mins to 10 mins each) – http://www.microsoft.com/windowsazure/sqlazure/videos/#introductory

16 Deep Dive Videos (Duration 5 to 15 mins each) –

http://www.microsoft.com/windowsazure/sqlazure/videos/#deepdive

3 Case Studies about How customers are using SQL Azure in production

http://www.microsoft.com/windowsazure/sqlazure/videos/#customers

23 Videos from TechEd 2011, Mix 2011, PDC 2010, WWPC 2010 (Duration – 60 mins each) –

http://www.microsoft.com/windowsazure/sqlazure/videos/#events

For those who cannot attend the technical events in person, this is really good advantage to see those videos online for free

SQL Azure Sync CTP2 – Synchronize SQL Azure with On Premise SQL Server and vice versa– How Sync Framework Tracks changes in databases – Part 3

In the previous posts, we saw How to download, install and configure SQL Azure Sync Agent and then How to define Sync Group, Manually run the sync and How to define Schedules to run the Sync automatically

In this post, we will How SQL Azure Sync provisions the databases and How it identifies the changes in the data and upload or download the changes

Open Management Studio and connect to SQL Azure server which we used for Sync Group

And then connect to on premise SQL Server and open Countries database, you can see the Tracking Triggers and Stored Procedures for each tables

If you look at the list of the tables, For each table we added for Sync, a new table has been added for tracking

Ex. DataSync.city_dss_Tracking

This table will be populated based on the Trigger created on the Main Data table

Let us insert a new record and see how it works

To verify that there is no city exists with name “Parisppany” we will run the below query on SQL Azure server

The above query returns 0 rows

Now insert a new record for City table in on premise SQL Server, if you see the Output Messages, you will see 3 messages, its due to Adding record in both Tracking table and Main table

Let us check the Main table

Now let us check the Tracking table

The tracking table ID column refers to the Primary Key of main data table and it contains timestamp information to identify when the record was created or updated or deleted

When record is deleted “Sync_Row_Is_TombStone” will be set to 1

Other Tracking Tables:

There are 3 other tracking tables created during Sync Provisioning process

1. [DataSync].[scope_config_dss]

2. [DataSync].[scope_info_dss]

3. [DataSync].[schema_info_dss]

[DataSync].[scope_config_dss] table contains the schema of the tables which are syncronized as XML data

If you open and view the XML Data, you will see the Tables as Adapters, along with details of Tracking tables, Procedures and Triggers used for tracking

I hope this post gives you an idea of How SQL Azure Sync service identifies the changes and updates the data.

Please post your comments !!!

SQL Azure Sync CTP2 – How to Synchronize SQL Azure with On Premise SQL Server and vice versa–Create Sync Groups and Schedules – Part 2

In the last post, we saw how to download, install the SQL Azure Sync Agent and configure it

In this post, we will see how to Create a Sync Group to include SQL Azure database and SQL Server On premise database, Synchronize them, and create a schedule to run them automatically.

Click on “New Sync Group” to define a new Sync Group

enter the name of the Sync Group and click on Database combo and select “Register New SQL Azure Database…”

Once selected this option, SQL Azure Database registration screen will popup

Specify the SQL Azure server name, Database name, User name and password and click “Test” to test the connection and then click “Save”

Click “Add” to add the database to Member List and click on “Set Hub” to specify it as main server

Again click on Database combo and select the On Premise database and click “Add”

Once completed adding the Hub and Sync Members, click “Next” to proceed

Select the table you want to Sync from “Available Tables” list and then click on Right Arrow to move to “Selected Tables”

Once done selecting the tables, click on “Create Sync Group”

To synchronize the databases initially, click on “Sync Now” button

Once the sync process starts, it will automatically redirect to “Sync Logs” tab, you can filter the logs by Sync Group and Database.

For initial sync, you will see 3 tasks, Provisioning of SQL Azure database for sync, Provisioning of SQL Server for Sync and then Sync the data

Once the tasks are completed, the Task state icon will change from Execution state to Ready State

To view the detailed log for each task, click on “View Log”

If you see the text marked showed in Yellow, it shows the total number of records downloaded.

Now we have successfully defined a Sync group and synchronized the data from SQL Azure and SQL Server bidirectional,

Let us now setup the Sync Group as a scheduled job

Click on “Sync Groups” and then select the Sync Group and click on “Edit”

In the Edit screen, you add additional Sync Members, you can change the Sycnhronization direction

You can schedule the sync job to run on specific intervals of time

After select the schedule, click on “Save”, Now your sync job will automatically run every 15 minutes once or as per your schedule definition

Now you might be wondering, How the Sync Service identifies the changes made to data, Let us see that in the next post.

I hope you all find this post useful. Please post your comments !!!

SQL Azure Sync CTP2 – How to Synchronize SQL Azure with On Premise SQL Server and vice versa–Part 1

SQL Azure Data Sync CTP2 is a SQL Azure Labs beta project which is a cloud-based data synchronization service built on Microsoft Sync Framework.

Using SQL Azure Data Sync you can synchronize data between SQL Azure to SQL Azure or SQL Azure to On Premise SQL Server on specific schedules for the specific tables.

You can sign up for SQL Azure Data Sync CTP2 invite in this website http://connect.microsoft.com/sqlazurectps

In this post, we will see how to Setup a Sync job to synchronize data between SQL Azure and SQL Server bi directionally

Once you received the Invitation Code from Microsoft Connect, you can activate the Sync CTP2 account in SQL Azure Labs (http://connect.microsoft.com/sqlazurectps )

After activating the Invite Code, Please login to https://datasync.sqlazurelabs.com using your Azure account

Sync Dashboard – has options to Manage the Sync Group, Databases (SQL Azure and On Premise SQL Server Databases), Agents

Let us first setup a Agent in Local SQL Server machine, click on “Agents” tab to download the Agent Setup

Click on “AgentServiceSetup.msi” link to save the MSI to your computer where you want to run the setup

Before installing the setup, we need to Generate Agent Key, Agent Key is unique to each Agents (Computers), used for its identification, To generate agent key, click on “Generate Agent Key”

Specify the Name of the Agent, and then click “Generate”

The Agent Key will be generated and saved automatically, to view the Agent’ Key click on the Agent Name,

The Agent Key will be displayed on the right side, Copy that save it for your reference, we will use this during Agent Installation, if you have multiple agents, you need to generate multiple keys. You cannot use the same key for multiple agent installations.

If you notice the Status column, it will be shown as Warning, the reason is Agent is not communicating to the SQL Azure Sync Server.

Let us now install the agent and configure it to communicate to SQL Azure Sync Server

To install the agent, open the folder where you have saved the AgentSerivceSetup.msi and run the setup

Click “Next” to proceed

Select “I Agree” and then Click “Next” to proceed

Specify the Service Account User Name and Password, If you are planning to use Windows Authentication for accessing the databases for Sync Process, please make sure this Service User has permissions to connect to SQL Server and access the databases.

After specify the Service username and password, click “Next” to proceed

Select the installation folder, specify whether to install just for you or for every one and then click “Next” to proceed

Click “Next” to confirm the installation and proceed

Installation will continue

Once the installation is completed, click “Close” to complete the setup.

Before stating the Sync Agent Configuration program, please go to Control Panel –> Administrative Tools –> Service to start the “SQL Azure Data Sync Service”

Right click on the service and then click “Start”

Go to Start Menu –> All Programs –> Microsoft SQL Azure Data Sync CTP, the following two shortcuts will be available

Click on “SQL Azure Data Sync Agent CTP2” to start the Sync Configuration program

Click on “Edit Agent Key” to setup the Agent Key

Copy the Agent Key we saved earlier and paste it here and click “Ok” to proceed

Once saved click on “Ping Sync Service” to check whether the Agent is able to successfully connect to SQL Azure Sync Server

On success connection, you should get this below message

Click “Ok” to proceed and then click “Add Member” to add a new database for doing synchronization

Select the tab based on the authentication you are planning to do, In this demo we are going to “Windows Authentication”, after select the tab, specify the name of the SQL Server Instance and then specify the database and then click “Test Connection”

Once the connection is successful, click on “Save” to save the Sync Member

If you want to change the database or Authentication, click on “Properties” to edit the saved Sync Member.

To delete the Sync Member, use “Delete Member” option

Click on “Check Member Schema” option to verify the selected database schema is compatible for SQL Azure sync

Now you have successfully download the agent, generated the agent key, installed the agent and configured the Agent and Sync Member ready for Sync.

In the next post we will see how to configure the Sync Group and schedule the sync job and synchronize the data between SQL Azure to SQL Server and vice versa.

I hope you all find this information helpful, Please post your comments and feedback !!!

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: