Shaun Xu

The Sheep-Pen of the Shaun


News

logo

Shaun, the author of this blog is a semi-geek, clumsy developer, passionate speaker and incapable architect with about 10 years experience in .NET. He hopes to prove that software development is art rather than manufacturing. He's into cloud computing platform and technologies (Windows Azure, Aliyun) as well as WCF and ASP.NET MVC. Recently he's falling in love with JavaScript and Node.js.

Currently Shaun is working at IGT Technology Development (Beijing) Co., Ltd. as the architect responsible for product framework design and development.

MVP

 

Yesterday I attended an online meeting with Microsoft and got an overview about a new database tool, SQL Server Data Tool (SSDT), which was shipped alone with the SQL Server 2012. After the meeting I decided to have a deeper try and found that it could make our live easier especially for SQL Azure development and deployment.

 

Install the SSDT

SSDT is part of the SQL Server 2012. It can be installed with the SQL Server 2012 installation. And if you don’t need the whole bunch of SQL Server you can install it through the Web PI or Visual Studio.

image

SSDT is an integrated environment for database developers to carry out all their database design work for any SQL Server platform (both on and off premise) within Visual Studio. We can use the SQL Server Object Explorer in VS to easily create or edit database objects and data, or execute queries. So the SSDT is not a database engine or runtime, it is just a database tool with some cool feature we can use during our development phase. So you can use SSDT with any existing database tools, such as the SQL Server Management Studio (SSMS) side by side.

Once you have installed the SSDT there will be a new window in the Visual Studio named SQL Server Object Explorer.

image

The new SQL Server Object Explorer is NOT the Server Explorer which exists in Visual Studio already. Although we can add and view the database connection through the Server Explorer it’s totally different to the new SQL Server Object Explorer.

SSDT highly integrated with Visual Studio. This means we don’t need to navigate to another application to tweak the database when coding and debugging. Also, once the SSDT had been installed there will be a new project type in Visual Studio named SQL Server Database project, which we will use later.

image

This project type is different than the original database project in Visual Studio Database Edition. It has another project extension name and has different functionality.

 

Statement Oriented, Instead of Command Oriented

SSDT adopts a totally different concept to maintain and control your database. It’s statement oriented, instead of command oriented. This means, by using SSDT you only need to care what your database schema should be. And you will never care about how to update the database to this schema. You will use T-SQL as a statement language to define the tables, views, keys, indexes, etc. you want and just save those CREATE scripts in SSDT project, and then SSDT will help you to apply the changes to the database.

For example, currently we have a database with a table and a view associated.

   1: CREATE TABLE [dbo].[Table1] (
   2:     [Id]   INT           NOT NULL,
   3:     [Name] NVARCHAR (50) NOT NULL,
   4:     PRIMARY KEY CLUSTERED ([Id] ASC)
   5: );
   6:  
   7: CREATE VIEW [dbo].[View]
   8:     AS SELECT * FROM [Table1]

In traditional way, if I want to extend the size of the Name column, I need to write an ALTER TABLE statement and then refresh the view. These scripts is focusing on how to change the database to the schema I want, but it cannot give me a very clear information on what schema I want it to be. This script is command oriented.

image

In SSDT we don’t need to care about how to alter the table and refresh the view. I only need to change my database creation script, which means just changed the CREATE TABLE script which have the length from 50 to 100 of the Name column. And then SSDT will compare my current script and the existing database schema, generate the upgrade script and run it.

image

SSDT also includes some other features such as the target platform validation, which means you can check your script against a database platform like SQL Server 2008, 2012 and SQL Azure, make sure it works before you run it.

It also provides the local database feature. By using this feature you can download a database schema copy from the production or center database and use it only by yourself when developing and testing. You can change the schema of your local database. All changes will be in some script files with many CREATE statement, you don’t need to write the alter script at all. And if you think it’s OK, you can use SSDT to update your changes back to the center database. SSDT will help you to alter the database to your latest schema. This is very useful when a team is working on the same database, and very useful when you are working with SQL Azure. Since frequently connect with the SQL Azure and upload download data is NOT free. In the following part I will demonstrate how to use SSDT to develop and upgrade the SQL Azure database.

 

Create SQL Azure Database and Schema in SSDT

SSDT can integrated with the SQL Azure very well. Just open the Visual Studio and the SQL Server Object Explorer window, right click on the root node and click the Add SQL Server item.

image

Specify the SQL Azure connection information in the popping up window.

image

Then it will appear in the SQL Server Object Explorer. You can create a new SQL Azure database in this window directly. Just expand this server and database node then right click to select the Add New Database. Then type the name of database. After several seconds the database will be created on your SQL Azure.

image

SSDT will create a 1GB Web edition SQL Azure database. If you want to create a database in different configuration you need to write the CREATE DATABASE command manually, or through the development portal.

Expand this new database you can see the tables, views, programmability, etc. in the list. Now let’s create a new table. Right click the Tables node and select Add New Table.

image

As you can see the table designer appeared! If you had been working with SQL Azure a bit while you should know that till now there’s no table designer in the local SSMS. And the only one designer is by using the web-based SQL Azure database manager, originally named “Project Houston”. But in SSDT you can use the designer to create and alter your tables. And more cool stuff is, when you change the table schema either in the designer or in the script panel they will be synchronized and refreshed automatically. Let’s added two columns through the designer and rename the table name in the script panel.

image

Then we can click the Update button at the top of the designer to apply the changes to the database. In this case, it will be run on the SQL Azure.

The SSDT will compare the schema between the SQL Azure database and our script, to generate the update script for us. In this case since the SQL Azure database is empty it will tell us just create a new table. But if our changes were huge SSDT will generate more steps to upgrade the schema.

image

We can retrieve the update script by clicking the Generate Script button, and we can ask SSDT to execute the script for us. Let’s click the Update Database button to apply the changes. Once the SSDT is performing the script steps will be shown in the Data Tool Operations window in Visual Studio. And you can see my script was executed finished successfully.

image

Back to the SQL Server Object Explorer window the new table had been shown there. And we can view its data, by using the context menu the View Data item, even though at this moment there is no data available.

image

 

Database Project and Local Database with SQL Azure

Assuming I’m a developer who is going to work with the database I had just created. Let’s have a look on how to use the SSDT database project and local database feature to make it easier and effectively.

There are many reasons that it is not a good approach to develop against the SQL Azure database directly. The first one is the cost. Since all transaction and data our bound is billed, I don’t want to frequently connect to the SQL Azure in development and test phase. The second reason is the performance. Connecting to the SQL Azure will be more slower than connecting to a local database of course. My boss doesn’t want me to waste of time on waiting for the SQL Azure response. The third one is, if I have some colleagues who are working on the same SQL Azure database, we might be affect each other by changing the schema and add or remove some data. So what I want is to download the SQL Azure database to my local machine, update the schema and data based on my business needs, develop and test, then finally update my changes of the database back to SQL Azure and my code to TFS.

To download the SQL Azure database I will create a new SQL Server Database project. And then right click on the project node from the Solution Explorer and select Import, Database.

image

SSDT allows to import database schema from a Data-tier Application package, a live database or a database creation script.

In the popping up window I selected the database which I had created on SQL Azure and then click Start button.

image

SSDT will connect to the SQL Azure database, grab the schema information and generate the creation script to me, and added them into the database project.

image

SSDT will only download the schema from the source database. It will NOT download and data.

In the Solution Explorer there will be some scripts listed under the related folders. In this example since I only have one table so there’s only one script under the table folder. Also in the SQL Server Object Explorer window there’s a new server node added with the name of (localdb)\Database 2 (SQL Server …). This is the local database SSDT created for me.

image

You can find the local database files (MDF and LDF) in the folder named Sandbox under you database project folder.

But currently there’s no table in the local database. We need to run the database project to let it generate the database content to us. Select the local database node in the SQL Server Object Explorer and click F5. In the output we can see the database had been deployed to the local database and the table was shown as well.

image

Now the schema of this database is exactly the same as what it is on SQL Azure, and I can develop against it rather than connecting to the SQL Azure. Select the database node we can find the connection string.

image

I don’t want to demonstrate how to use the local database through C# and ADO.NET. It’s exactly the same as what we are doing with SQL Server every day. I will focus on database part in this post.

Assuming we need a lookup table named Country and a new column in the Product table named CountryID, and a foreign key between them. We can add and modify on our local database. This is very quickly and will not affect other developers who is related with the SQL Azure database. In the Solution Explorer right click on the Tables folder and add a new Table item.

image

And in the designer added a column named Name and then save it. Now there will be two scripts under the Tables folder.

image

And then double click on the Product.sql file to open its design window. Let’s append a new column named CountryID and add a new foreign key from the design panel as well.

image

Input the foreign key name and then we need to manually specify the columns associated with it through the script panel at the bottom.

image

Finally, to make our change applied to the local database just run (click F5) on the database project. The output window told us the deployment successful and in the SQL Server Object Explorer the new table and key will be shown as well.

image

Now let’s assuming I had finished the development and testing and I want to update my database changes to the SQL Azure. In database project this can be done by right click the project and select Publish. In the publish window I selected the target database connection information and then click the Publish button. SSDT will compare the schema between the SQL Azure database and my local database, generate the update scripts and run.

image

You can check the “Add profile to project” so that your publish setting can be saved and used in the future. Clicking the “Generate Script” will let the SSDT generate the update script to us without performing it.

After a while the SQL Azure database will be changes based on what we have done on the local database.

image

 

Target Platform Validation and Schema Compare

Besides the designer and local database, there are some other features can help us for SQL Azure development. The first one is target platform validation.

As we know, even though the underlying database engine is same between SQL Server and SQL Azure, there are some limitation of SQL Azure. When we create or change the schema of SQL Azure it’s very hard to remember all these limitations and causes a service failure. But now, SSDT provide the target platform validation feature which means it can check our schema definition scripts based on the database platform we specified. To demonstrate this feature let’s create another table named Area and with two columns: ID and Area. But let’s remove the primary key of this table. This kind of table is called “Heap” sometimes, which is supported by SQL Server but not by SQL Azure.

image

Right click the database project and click Build. This will perform the target platform validation. By default our database project is using SQL Server 2012 as its platform so the Area table is OK. Next, let’s click the Property menu item of the database project and change the target platform to SQL Azure.

image

Then build again, we will get an error said that in SQL Azure a clustered index is required in a table. So when we using SQL Azure through the SSDT we can let it check the schema for us before publish to the cloud.

image

Another cool feature is schema compare. In fact when we perform any updates in SSDT it will invoke this feature to generate the update script. But we can invoke it manually by clicking the Schema Compare from the context menu of the database project. Then select the target database we want to compare with our database project. Then it will show the differences by tables, views, etc..

image

 

Summary

SQL Server Data Tool (SSDT) is not a new product, but was improved a lot and published alone with the new SQL Server 2012. SSDT resolved the problem that how to define, trace and update the database by introducing the statement oriented script principle. This makes the developers focus on what the database should be instead of how to upgrade the database, by using its powerful compare engine and script generator engine.

SSDT also provides the local database and database project feature as well. Working with the script update engine we can easily download the database to the development local and amend. This will not affect the center database. And the developer can publish his/her changes back to the center database very easily. And this is much more useful when working with SQL Azure.

SSDT also make it possible to trace the history of database changes in source control service, such as TFS, by checking the scripts in the repository. Since it only contains the schema definition it would be very easy to find who, when and what to changes to the schema.

 

Hope this helps,

Shaun

All documents and related graphics, codes are provided "AS IS" without warranty of any kind.
Copyright © Shaun Ziyan Xu. This work is licensed under the Creative Commons License.

 

This morning I received an email from the Microsoft said that, a meaningful company, Microsoft Open Technologies Inc., had just release their first pre-production, the Redis on Windows.

 

Redis

If you had been working in the Linux world or interested in NoSQL field you should have probably known or heard about the Redis. Redis is an open source, advanced key-value store. Someone categorized Redis to the distributed cache since it’s an in-memory key-value store. Someone categorized it to the NoSQL, since it provides the feature saving your data into disk. And someone categorized it to the distributed queue, since it supports storing your data into hash and list type and provides the enqueue, dequeue and pub/sub functionalities. You see, Redis is a very powerful tool that can be used widely in building especially a large scaling system, as a distributed cache, NoSQL and message queue.

The original Redis is written in ANSI C, which sponsored by VMWare, works in most POSIX systems like Linux, *BSD, OS X without external dependencies. Linux and OSX are the two operating systems where Redis is developed and more tested, and it’s recommended to use Linux for deploying. As you can see there’s no Windows in the list. Microsoft had submitted a patch into Redis repository to make it available on Windows, but unfortunately Redis doesn’t have any plan to merge it into the main branch. This means all Windows version of Redis are all unofficial, includes this one.

Different from the basic in-memory key-value store, such as the Memcached, the Redis not only provides storing objects by keys, it also can store your data in vary types such as strings, hashes, lists, sets and sorted sets. Redis also have the atomic operations on these types, like appending to a string; incrementing the value in a hash; pushing to a list; computing set intersection, union and difference; or getting the member with highest ranking in a sorted set.

In order to achieve its outstanding performance, Redis works with an in-memory data store. But depending on the use cases, we can persist it either by dumping the dataset to disk every once in a while, or by appending each command to a log. So it looks like a NoSQL database as well. Other features include a simple check-and-set mechanism, pub/sub and configuration settings to make Redis behave like a cache.

 

Download, Build and Install Redis on Windows

The Redis on Windows now is available on the GitHub of the Microsoft Open Technologies Inc. site. You can download the full source here, which including the Redis Service, Redis Client, Redis Benchmark, etc..

In Windows the simplest way to build the Redis is to use the Visual Studio. If you have installed the C++ component then you can open its solution file under the “msvs” folder. Then just build it.

image

Navigate to the “msvs\debug” folder there will be a lot of messing files appeared. I don’t have any experience on C and C++ but I can find something useful. The most imortant two files are redis-server.exe and redis-cli.exe.

redis-server.exe The execution file that start a Redis server process on your machine.
redis-cli.exe The command line client tool that can operate against a Redis server.
image

The simplest way to start a Redis server is just to open a command windows and go to this folder, execute the redis-server.exe then you can see the Redis is now running and on the screen it will show the status every 5 seconds by default.

image

Very easy, right? If you want to check whether the Redis is working or not, just execute the redis-cli.exe in another command window, and input the Redis command “ping”. If the server you are connecting is fine it will response a “PONG” back to you.

image

If you had been using the Linux version of Redis before you will find that the execution files, output log and the commands are exactly same. This means you can use any tools and clients you are familiar with which running on the original Redis to this new windows version. For example, let put a key-value pair into the Redis store through the client.

image

I added two key-value pairs into my Redis by using the SET command, and then searched all keys in the server. It gave me the result on the client screen. And then I got one of them by specifying the key. This is a very common and simple usage. You can find the full command list on this page.

 

Redis and C#

The Redis on Windows product just ship the windows version of Redis server, without the connection client for the .NET language. But this is not a problem. Since as I mentioned below, the Redis on Windows follows all behaviors and interfaces of the original one. This means we can use any connection client that works with the original Redis to this Windows version.

There are many clients available on this page, includes C, C++, Java, PHP, Ruby, Python, etc.. And of course, the C#. I  was using the ServiceStack.Redis as the connection client in one of my previous project. It worked well with my Redis running on Ubuntu, so it should be able to work on the Redis on Windows version as well.

You can download the ServiceStack.Redis here, build it can add the reference of ServiceStack.Interfaces and ServiceStack.Redis into the C# project.

When using the ServiceStack.Redis we need to instant its RedisClient class by specifying the Redis server name (or IP) and port. Then your can execute the Redis commands from vary methods in the RedisClient class. Let’s firstly have a try on how to use the list feature of Redis, which it works like a queue.

First of all, I will invoke the FlushAll method to clear all keys in my Redis. This will remove all items that stored in the server.

   1: using (var redis = new RedisClient("127.0.0.1"))
   2: {
   3:     redis.FlushAll();
   4: }

Then I will start a thread to let user input something and append it into one of the list (queue) of my Redis. The name of this list would be “default”.

   1: var senderThread = new Thread(() =>
   2: {
   3:     using (var redis = new RedisClient("127.0.0.1"))
   4:     {
   5:         Console.WriteLine("Input message: ");
   6:         var message = Console.ReadLine();
   7:         while (!string.IsNullOrWhiteSpace(message))
   8:         {
   9:             redis.EnqueueItemOnList("default", message);
  10:  
  11:             Console.WriteLine("Input message: ");
  12:             message = Console.ReadLine();
  13:         }
  14:     }
  15: });
  16: senderThread.Start();

Next, in the main thread the application will try to dequeue any items from the list, and print the value in the console.

   1: using (var redis = new RedisClient("127.0.0.1"))
   2: {
   3:     var result = string.Empty;
   4:     while (string.Compare("q", result, false) != 0)
   5:     {
   6:         result = redis.BlockingDequeueItemFromList("default", TimeSpan.FromSeconds(5));
   7:         Console.WriteLine("DEQUEUE RESULT = [{0}]", result);
   8:     }
   9: }

The full code would be like this.

   1: static void Main(string[] args)
   2: {
   3:     // clean up
   4:     using (var redis = new RedisClient("127.0.0.1"))
   5:     {
   6:         redis.FlushAll();
   7:     }
   8:  
   9:     // enqueue thread definition
  10:     var senderThread = new Thread(() =>
  11:     {
  12:         using (var redis = new RedisClient("127.0.0.1"))
  13:         {
  14:             Console.WriteLine("Input message: ");
  15:             var message = Console.ReadLine();
  16:             while (!string.IsNullOrWhiteSpace(message))
  17:             {
  18:                 redis.EnqueueItemOnList("default", message);
  19:  
  20:                 Console.WriteLine("Input message: ");
  21:                 message = Console.ReadLine();
  22:             }
  23:         }
  24:     });
  25:     senderThread.Start();
  26:  
  27:     // dequeue operation
  28:     using (var redis = new RedisClient("127.0.0.1"))
  29:     {
  30:         var result = string.Empty;
  31:         while (string.Compare("q", result, false) != 0)
  32:         {
  33:             result = redis.BlockingDequeueItemFromList("default", TimeSpan.FromSeconds(5));
  34:             Console.WriteLine("DEQUEUE RESULT = [{0}]", result);
  35:         }
  36:     }
  37:  
  38:     Console.WriteLine("Done!");
  39:     Console.ReadKey();
  40: }

As you can see after I input some strings it will be appended into the list, and then dequeued from the list at once.

image

In this example we only have one thread to dequeue the items. If there are more than one client which is dequeuing the same list, only one of them can retrieve the item. In message bus glossology this is named “queue”, which means only one consumer can retrieve an item. And if there’s no consumer available the items in the queue should be stay there until at least a consumer connected and dequeued.

image

If it’s a reliable queue, the items in the queue will be remained even though the message bus was terminated or the machine is crashed.

Next, let’s have a try on the pub/sub mode. Pub/Sub mode, also known as the “topic” mode in message bus field. Different from the “queue” mode I mentioned below, in “topic” mode a message will be received by all consumers that is subscribing on the topic. (In Redis it’s said “channel” rather than “topic”.) But if some consumers were not available at that moment the message will be lost and will never be delivered to them even though they got back later.

image

To use the Pub/Sub in Redis from C# as a consumer we also need to create the RedisClient instance, and use its CreateSubscrption method to bind it to a channel. And when a message comes it will raise the OnMessage event and we can handle it to do our own business logic.

   1: static void ConsumerAction(object name)
   2: {
   3:     using (var consumer = new RedisClient("127.0.0.1"))
   4:     {
   5:         using (var subscription = consumer.CreateSubscription())
   6:         {
   7:             subscription.OnSubscribe = (channel) =>
   8:             {
   9:                 Console.WriteLine("[{0}] Subscribe to channel '{1}'.", name, channel);
  10:             };
  11:             subscription.OnUnSubscribe = (channel) =>
  12:             {
  13:                 Console.WriteLine("[{0}] Unsubscribe to channel '{1}'.", name, channel);
  14:             };
  15:             subscription.OnMessage = (channel, message) =>
  16:             {
  17:                 Console.WriteLine("[{0}] Received message '{1}' from channel '{2}'.", name, message, channel);
  18:             };
  19:  
  20:             subscription.SubscribeToChannels("default");
  21:         }
  22:     }
  23: }

In order to demonstrate the behavior of Pub/Sub let’s create there threads as three consumers that are subscribing the same channel.

   1: var consumerThread1 = new Thread(new ParameterizedThreadStart(ConsumerAction));
   2: var consumerThread2 = new Thread(new ParameterizedThreadStart(ConsumerAction));
   3: var consumerThread3 = new Thread(new ParameterizedThreadStart(ConsumerAction));
   4: consumerThread1.Start("Consumer 1");
   5: consumerThread2.Start("Consumer 2");
   6: consumerThread3.Start("Consumer 3");

And use the PublishMessage method of the RedisClient to send some messages in the channel in the main thread. The full code would be like this.

   1: static void ConsumerAction(object name)
   2: {
   3:     using (var consumer = new RedisClient("127.0.0.1"))
   4:     {
   5:         using (var subscription = consumer.CreateSubscription())
   6:         {
   7:             subscription.OnSubscribe = (channel) =>
   8:             {
   9:                 Console.WriteLine("[{0}] Subscribe to channel '{1}'.", name, channel);
  10:             };
  11:             subscription.OnUnSubscribe = (channel) =>
  12:             {
  13:                 Console.WriteLine("[{0}] Unsubscribe to channel '{1}'.", name, channel);
  14:             };
  15:             subscription.OnMessage = (channel, message) =>
  16:             {
  17:                 Console.WriteLine("[{0}] Received message '{1}' from channel '{2}'.", name, message, channel);
  18:             };
  19:  
  20:             subscription.SubscribeToChannels("default");
  21:         }
  22:     }
  23: }
  24:  
  25: static void Main(string[] args)
  26: {
  27:     using (var redis = new RedisClient("127.0.0.1"))
  28:     {
  29:         redis.FlushAll();
  30:     }
  31:  
  32:     var consumerThread1 = new Thread(new ParameterizedThreadStart(ConsumerAction));
  33:     var consumerThread2 = new Thread(new ParameterizedThreadStart(ConsumerAction));
  34:     var consumerThread3 = new Thread(new ParameterizedThreadStart(ConsumerAction));
  35:     consumerThread1.Start("Consumer 1");
  36:     consumerThread2.Start("Consumer 2");
  37:     consumerThread3.Start("Consumer 3");
  38:  
  39:     using (var publisher = new RedisClient("127.0.0.1"))
  40:     {
  41:         Console.WriteLine("Input message: ");
  42:         var message = Console.ReadLine();
  43:         while (!string.IsNullOrWhiteSpace(message))
  44:         {
  45:             publisher.PublishMessage("default", message);
  46:             Console.WriteLine("Input message: ");
  47:             message = Console.ReadLine();
  48:         }
  49:     }
  50:  
  51:  
  52:     Console.WriteLine("Done!");
  53:     Console.ReadKey();
  54: }

Then let’s run our application and input some messages. You can see in this sample all of our three consumers received the messages and process their own business logic.

image

 

Summary

Distributed cache, distributed data store and distributed message queue are very important components when we build a large system with high scalability and high performance. Redis is one of the most powerful product. It can be used as a cache, a NoSQL database and message queue as well. And the performance of Redis is outstanding. But for the developers who is working on Windows platform it’s a little pity that Redis doesn’t support Windows by default.

In this post I forwarded the announcement that Microsoft Open Technologies had just published their first un-commercial Redis on Windows version. By using it we can deploy the Redis server on Windows, which means no need to learn on how to build and run it on Linux.

I also demonstrated how to use it from C# through the ServiceStack.Redis client library. Since the Windows version of Redis follows all APIs as the original one we can use anything we are familiar with.

Integrated with my previous post about using the WCF transport extension to build a high scalability system on top of the message bus, since the Redis supports the list data type which implements the message bus feature, we can use it as our underlying transportation as well.

 

Hope this helps,

Shaun

All documents and related graphics, codes are provided "AS IS" without warranty of any kind.
Copyright © Shaun Ziyan Xu. This work is licensed under the Creative Commons License.