Click here to Skip to main content
Click here to Skip to main content

Migration Toolkit for SQL Data Services(SDS)

, 3 Mar 2009 MIT
Rate this:
Please Sign up or sign in to vote.
Process and toolkit to migrate data to Microsoft SQL Data Services

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

SDSDataStructure.gif

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.

Shift.png

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

Process.png

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

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.

SQLServerIOWizard.png

Run SQL Server Import and Export Wizard.

SQLServerIOWizard2.png

Choose data source and database to migrate.

SQLServerIOWizard3.png

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

SQLServerIOWizard4.png
Success screen.

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".

ExcelCellFormat1.gif

Figure.1 Using Format->Cells or Right-Click->Format Cells.

format_cells.gif

Figure.2

credit1.gif

Figure.3

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

kb42_excel_security.gif

Figure.4

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.

omega.png

Useful Links

History

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

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

Won Dong
Technical Lead Domus Inc.
United States United States
Won Dong is a technology director of Domus Digital, division of Domus Inc. He majored in mathematics(undergrad) and computer graphics and gaming technology(master) in University of Pennsylvania. As CTO(Cheif Technology Officer) in E-motion Inc, the biggest web integration company in South Korea, he coordinated several massive web projects in Samsung, Hyundai, etc. His specialties are web development, cloud computing, social media development, computer graphics, and game development.
 
Development Tools : VS 2010, Xcode, Eclipse, Delphi
Design Tools : Photoshop, Maya, Illustrator, 3D MAX, Expression Blend, Flash
Technologies : C++, C#, Java, Objective-Pascal, Objective-C, .NET, WPF, WCF, SOAP, CUDA, Cg, FLEX, Mel, XAML, FBML, oAuth, oData
Follow on   Twitter

Comments and Discussions

 
GeneralNice! Pinmemberxramzes3-Mar-11 5:30 
GeneralRe: Nice! PinmemberWon Dong3-Mar-11 5:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 3 Mar 2009
Article Copyright 2009 by Won Dong
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid