Migrating from MS SQL Server 2008 to EnterpriseDB

Exclusive offer: get 50% off this eBook here
Entity Framework Tutorial

Entity Framework Tutorial — Save 50%

Learn to build a better data access layer with the ADO.NET Entity Framework and ADO.NET Data Services with this book and eBook

$23.99    $12.00
by Dr. Jayaram Krishnaswamy | April 2009 | Microsoft

In this article by Dr. Jayaram Krishnaswamy, we will learn about migration of data from MS SQL Server 2008 to EnterpriseDB. Migration Studio bundled with the EnterpriseDB download is a collection of tools to migrate automatically the data and business logic into Postgres from a variety of vendor products that include SQL Server, Oracle, MySQL to mention a few. This article shows you step by step how you may migrate a SQL Server 2008 table to Postgres Plus. You can only migrate tables and schemas but not database as a whole.

With many database vendor products in the market and data intensive applications using them, it is often required to port the application to use the data or, migrate the data so that the application can use it. Migration of data is therefore one of the realities of the IT Industry. Some of the author's previous articles on migration can be found at this link. You may find more if you do a search on his blog site.

Table to be migrated in SQL Server 2008

The following figure shows the Categories table in the Microsoft SQL Server 2008's Management Studio that will be migrated to the Postgres database.

Entity Framework Tutorial

Creating a database in Postgres Studio

Right click Databases node in the Advanced Server 8.3 and click on New Database... menu as shown.

Entity Framework Tutorial

The New Database... window gets displayed as shown. Create an empty database PGNorthwind in Postgres Studio by entering information shown in the next figure.

Entity Framework Tutorial

This creates a new database and related objects as shown in the next figure.

Entity Framework Tutorial

This also creates the script in the Properties pane as shown. Review the properties. The database may be dropped using the Drop Database statement.

Entity Framework Tutorial

Starting the Migration Studio

Click on Start | All Programs | Postgres Advanced Server 8.3 to display the drop-down menu as shown.

Entity Framework Tutorial

Click on the Migration Studio drop-down item. This opens the EnterpriseDB Migration Studio 8.3(Migration Studio for the rest of the tutorial) with a modal form with the title Edit Server Advanced Server 8.3(localhost:5432). This is the server we installed in the previous tutorial.

Entity Framework Tutorial

Enter the User Name and Password and click OK. You will get a message displaying the result as shown in the next figure.

Entity Framework Tutorial

Click OK to both the open windows and the EnterpriseDB Migration Studio shows up as shown here.

Entity Framework Tutorial

Click on the File in the main menu on the Migration Studio and pick Add Server. This brings up the Add Server window with a default as shown.

Entity Framework Tutorial

Entity Framework Tutorial Learn to build a better data access layer with the ADO.NET Entity Framework and ADO.NET Data Services with this book and eBook
Published: October 2008
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

Add Microsoft SQL Server 2008 to the Migration Studio

Click on New which opens a New Server window with all fields empty. Change Server Type to SQL Server by picking it up from the drop-down. Provide a Nickname, and for windows authentication used for SQL Server, type in [DomainUSER] User Name as shown. For the InitialDB use TestNorthwind.

"The SQL Server 2008 used here is a named instance with the name Sangam[localhost]. The user name in the Migration Studio's New Server window is that of the computer owner. If you are not the computer owner, you should get the proper credentials to the SQL Server from your DBA."

Entity Framework Tutorial

Test the server information you provided with the Test button. You should get a display of the test results as before (figure not shown). Click OK. The SQL Server gets added to the Add Server window as shown.

Entity Framework Tutorial

Click OK to the Add Server window. The SQL Server gets added to the list of servers as shown. Note that although one initial db was specified while adding the server we find all the user databases including the reporting services databases and the Subscribers (part of SQL Server Reporting Services) databases.

Entity Framework Tutorial

Right click on Catogories table in the server with the name SSonEDB to display the drop-down menu as shown.

Entity Framework Tutorial

Migrating the table

The Online Migration window gets displayed where there are two drop-down boxes to choose, the target database server and the target databases all of which are shown here. The default schema is that of the dbo. Some of the schemas are for storing meta data and you cannot store your data there. Change the schema to public. You have a two choices as to what you want to export.

Entity Framework Tutorial

Accept default options (Export constraints and Export Indexes).Click on Run. The migration gets started and the result of migration gets displayed in the same window as shown.

Entity Framework Tutorial

The Categories table is migrated. This can be verified in the Migration Studio by expanding the objects in the PGNorthwind database as shown.

Entity Framework Tutorial

In the migrated table in the Migration Studio's database PGNorthwind review the items in the Table:categories. The Properties, Statistics, Dependencies, Dependents and the Data. The migrated data is shown in the next figure.

Entity Framework Tutorial

In the Advanced Server's Postgres Studio the expanded display of the imported table is as shown.

Entity Framework Tutorial

Comparison of orginal and migrated tables

Here is a comparison of the original and migrated tables as to data type, size, nullability etc.

Microsoft SQL Server 2008

CategoryID (PK, int, not null)

CategoryName(nvarchar(15), not null)

Description(nvarchar(max), null)

Picture(image, null)


EnterpriseDB (Postgres)

categoryid Integer Primary Key Not Null

categoryname Character Varying(30) Not Null

Description text

picture bytea

Summary

This tutorial showed step-by-step all the details regarding copying a database from SQL Server 2008 to a Postgres Plus database. The process of creating a target database; connecting to the SQL Server database in the Advanced Server's Migration Studio; and the migration of chosen object were described. A comparison of the original and the migrated table on Postgres is also presented.

Entity Framework Tutorial Learn to build a better data access layer with the ADO.NET Entity Framework and ADO.NET Data Services with this book and eBook
Published: October 2008
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

About the Author :


Dr. Jay Krishnaswamy is a graduate of the Indian Institute of Science, Bangalore writes on database and web development related topics to several computer programming related web sites. He is an active participant in several forums and discussion groups. Before working in the IT industry as a Microsoft Certified Trainer and a Siebel Certified consultant he taught at several institutes of technology and universities in India, Australia, Brazil and the USA. Links to his articles may be found at his website or the blog. He lives in Plainsboro, NJ, USA and may be reached at jkrishnaswamy@comcast.net

Books From Packt

 

VSTO 3.0 for Office 2007 Programming
VSTO 3.0 for Office 2007 Programming

ASP.NET 3.5 Social Networking
ASP.NET 3.5 Social Networking

Implementing Microsoft Dynamics NAV 2009
Implementing Microsoft Dynamics NAV 2009

WCF Multi-tier Services Development with LINQ
WCF Multi-tier Services Development with LINQ

ASP.NET 3.5 Application Architecture and Design
ASP.NET 3.5 Application Architecture and Design

Microsoft Visual C++ Windows Applications by Example
Microsoft Visual C++ Windows Applications by Example

Learning SQL Server 2008 Reporting Services
Learning SQL Server 2008 Reporting Services

C# 2008 and 2005 Threaded Programming: Beginner's Guide
C# 2008 and 2005 Threaded Programming: Beginner's Guide

 

Your rating: None Average: 5 (1 vote)
Thnak you! by
Excellent article!

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Z
2
a
v
V
z
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software