Migration Toolkit for SQL Data Services(SDS)

• Download source code - 2.16 KB

Introduction

Microsoft SQL Data Services is a new concept of data hosting service that enables the company and developer to have flexibility and scalability in data hosting and handling. However, it's completely different from ordinary RDBMS, consisted not in Table and Fields, but in Authority and Entity. This toolkit helps developer or DBA to migrate existing data to SDS.

This library is a part of components of ROH Project: The Automata Storytelling Engine and Its Prototype.

Main Idea

SDS has two categories that define where data will be, Authorities and Containers. Entities are like Records that have various types of data. However, there are limitations. SDS is kind of simple DB, so there is no indexing and no relation information between entities.

Here is the idea. SDS can work as similar as RDBMS, if we map it as above.

• Database > Authority
• Table > Container
• Record > Entity

How It Works

This tool is made by VBA (Visual Basic for Application), especially with Microsoft Excel.

The whole process is divided into two parts; pre-migration, and migration.

In the pre-migration part, you need to bring database to Excel. There are several types of database such as Microsoft SQL Server, Access, Oracle, mySQL, etc., and most of them have a way to export their data to other formats like CSV or XLS. After exporting Excel, you need to cleanse data to fit into SDS.

In the migration part, you should attach a released migration toolkit VBA file on your Excel file. When you run the code, the application asks you about your SDS account information (ID, Password) and authority name to create. A migration toolkit automatically generates bulk data XML file formatted in SSDS XML format and calls st.exe Admin Utility from Microsoft SQL Data Services SDK, and asks to create authority, containers, and entities.

Requirements

• SDS Account: Either Windows Azure Platform or SQL Data Services is now offering free beta testing for SDS (SQL Data Services requires credit card information)
• Microsoft Office 2003 or more
• Microsoft SQL Data Services SDK

Pre-migration Process

Exporting Database

I can't explain the whole processes for every RDBMS, but show an example exporting database using Microsoft SQL Server Import and Export Wizard.

Run SQL Server Import and Export Wizard.

Choose data source and database to migrate.

Choose Excel data source, and its file name for destination.

Data Cleansing

Once you succeed in exporting data to Excel, you need to purify data. There are several rules as below:

1. Any name of authority, container, entity name have no space, no underline. (Dash(-) is fine)
2. The first row is field names.
3. Sheet names are names of containers.
4. String field should be set to text format (Figure. 2)
5. Double or Float should be set to a number format with limited decimal places (Figure. 3)
6. The first column is ID, and should be number.
7. Entity ID is defined as "Sheet name" + "ID".

Post-migration Process

1. Download Toolkit File
2. Open cleansed Excel file
3. Open Visual Basic Editor
4. Right-click on Module, then select File Import
5. Select Toolkit File (modSDS.bas)
6. Save and re-open it with normal security level (Figure.4)
7. Run module. There are 2 types of functions as below.
8. Input SDS ID, Password, and Authority

Code

First, define path of Microsoft SQL Data Services SDK:

'Location of Microsoft SQL Data Services SDK
Const strSDSSDKPath = "C:\Program Files\Microsoft SQL Data Services SDK"
'Microsoft SQL Data Services SDK Admin File
Const strSDSSDKFilename = "st.exe"  

Next, build a function that creates authority.

Sub SDSCreateAuthority(ByVal Authority As String, _
	ByVal ID As String, ByVal Password As String)

ExecuteAndWait (strSDSSDKPath & Application.PathSeparator & _
	strSDSSDKFilename & " create " & Authority & " /user:" & _
	ID & " /password:" & Password & " /soap /verbose")

End Sub

Then, build a function that makes container. An error occurs when the same container exists on authority. (But, it doesn't stop.)

Sub SDSCreateContainer(ByVal Authority As String, _
	ByVal Container, ByVal ID As String, ByVal Password As String)

ExecuteAndWait (strSDSSDKPath & Application.PathSeparator & _
	strSDSSDKFilename & " create " & Authority & " " & _
	Container & " /user:" & ID & " /password:" & Password & " /soap /verbose")

End Sub

To upload bulk data, you have to convert data as XML DB Scheme.

Sub BuildXML(ByVal Worksheet As Worksheet, ByVal Filename As String)

Dim inFile As Integer
Dim j, k As Integer
Dim strStartEntity As String
Dim strLine As String
Dim strType As String
Dim strValue

    inFile = FreeFile
    Open (ActiveWorkbook.Path & Application.PathSeparator & _
	Filename & ".xml") For Output As inFile
    j = 2
    strStartEntity = "<" & Filename & _
	" xmlns:s=""http://schemas.microsoft.com/sitka/2008/03/"" " & _
	"xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" " & _
	"xmlns:xs=""http://www.w3.org/2001/XMLSchema"">"
    While (Worksheet.Cells(j, 1)) <> ""
        k = 1
        Print #inFile, strStartEntity
        strLine = "  <s:Id>" & Filename & Worksheet.Cells(j, 1) & "</s:Id>"
        Print #inFile, strLine
        While (Worksheet.Cells(1, k)) <> ""
            strType = ""
            strLine = ""
            strValue = Worksheet.Cells(j, k)
            If Worksheet.Cells(j, k).NumberFormat = "General" Then
                If IsNumeric(Worksheet.Cells(j, k)) = True Then
                    strType = "xs:decimal"
                    If Int(Val(strValue)) <> Val(strValue) Then
                        strValue = Format(Worksheet.Cells(j, k), "#0.00000000000000")
                        End If
                    Else
                    If IsDate(Worksheet.Cells(j, k)) = True Then
                        strType = "xs:dateTime"
                        strValue = Format(Worksheet.Cells(j, k), _
					"yyyy-mm-ddThh:mm:ssZ")
                        Else
                        strType = "xs:string"
                        End If
                    End If
                Else
                If Worksheet.Cells(j, k).NumberFormat = "@" Then
                    strType = "xs:string"
                    Else
                    If IsNumeric(Worksheet.Cells(j, k)) = True Then
                        strType = "xs:decimal"
                        If Int(Val(strValue)) <> Val(strValue) Then
                            strValue = Format(Worksheet.Cells(j, k), _
				"#0.00000000000000")
                            End If
                        Else
                        If IsDate(Worksheet.Cells(j, k)) = True Then
                            strType = "xs:dateTime"
                            strValue = Format(Worksheet.Cells(j, k), _
				"yyyy-mm-ddThh:mm:ssZ")
                            Else
                            strType = "xs:string"
                            End If
                        End If
                    End If
                End If

            If (strType <> "") And (strValue <> "") Then
                strLine = "  <" & Worksheet.Cells(1, k) & _
			" xsi:type=""" & strType & """>" & _
			strValue & "</" & Worksheet.Cells(1, k) & ">"
                Print #inFile, strLine
                End If
            k = k + 1
            Wend
        j = j + 1
        strLine = "</" & Filename & ">"
        Print #inFile, strLine
    Wend
    Close inFile
End Sub 

This function converts a worksheet into XML file. I use worksheet name as filename. When you finish making XML files, upload files using this function.

Sub SDSBatchUpload(ByVal Authority As String, ByVal Container As String, _
	ByVal Filename, ByVal ID As String, ByVal Password As String)

ExecuteAndWait (strSDSSDKPath & Application.PathSeparator & _
	strSDSSDKFilename & " loadbatch " & Authority & " " & _
	Container & " " & Filename & " /user:" & ID & " /password:" & _
	Password & " /soap /verbose /overwrite /parallel:4")

End Sub 

You can easily use those processes using functions as below.

Function List

• ConvertActiveSheetToSDSXML: Convert and upload current worksheet to SDS
• ConvertToSDSXML: Convert and upload all of sheets to SDS

Post-migration Process

You can check the result of migration using Omega.SDSClient, SDS Browser powered by Silverlight.

Useful Links

• Azure Service Platform Home
• SQL Data Service Home
• SQL Data Services – Administration and Bulk Uploading
• CodePlex Spaceblock
• CodePlex Migration Toolkit for SDS

History

• (02/27/2009) First version
• (03/04/2009) Code Introduction added