Skip to content

Commit 052d741

Browse files
daamiendaamien
committed
Create gh-pages branch via GitHub
0 parents  commit 052d741

9 files changed

Lines changed: 722 additions & 0 deletions

File tree

‎images/bg_hr.png‎

943 Bytes
Loading

‎images/blacktocat.png‎

1.39 KB
Loading

‎images/icon_download.png‎

1.13 KB
Loading

‎images/sprite_download.png‎

16.4 KB
Loading

‎index.html‎

Lines changed: 227 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,227 @@
1+
<!DOCTYPE html>
2+
<html>
3+
4+
<head>
5+
<meta charset='utf-8' />
6+
<meta http-equiv="X-UA-Compatible" content="chrome=1" />
7+
<meta name="description" content="Sqlserver2pgsql : Migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database, as automatically as possible" />
8+
9+
<link rel="stylesheet" type="text/css" media="screen" href="stylesheets/stylesheet.css">
10+
11+
<title>Sqlserver2pgsql</title>
12+
</head>
13+
14+
<body>
15+
16+
<!-- HEADER -->
17+
<div id="header_wrap" class="outer">
18+
<header class="inner">
19+
<a id="forkme_banner" href="https://github.com/dalibo/sqlserver2pgsql">View on GitHub</a>
20+
21+
<h1 id="project_title">Sqlserver2pgsql</h1>
22+
<h2 id="project_tagline">Migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database, as automatically as possible</h2>
23+
24+
<section id="downloads">
25+
<a class="zip_download_link" href="https://github.com/dalibo/sqlserver2pgsql/zipball/master">Download this project as a .zip file</a>
26+
<a class="tar_download_link" href="https://github.com/dalibo/sqlserver2pgsql/tarball/master">Download this project as a tar.gz file</a>
27+
</section>
28+
</header>
29+
</div>
30+
31+
<!-- MAIN CONTENT -->
32+
<div id="main_content_wrap" class="outer">
33+
<section id="main_content" class="inner">
34+
<h1>
35+
<a name="sqlserver2pgsql" class="anchor" href="#sqlserver2pgsql"><span class="octicon octicon-link"></span></a>sqlserver2pgsql</h1>
36+
37+
<p>This is a migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database, as automatically as possible.</p>
38+
39+
<p>It is written in Perl.</p>
40+
41+
<p>It does two things:</p>
42+
43+
<ul>
44+
<li><p>convert a SQL Server schema to a PostgreSQL schema</p></li>
45+
<li><p>produce a Pentaho Data Integrator (Kettle) job to migrate
46+
all the data from SQL Server to PostgreSQL. This second part is optionnal</p></li>
47+
</ul><h1>
48+
<a name="notes-warnings" class="anchor" href="#notes-warnings"><span class="octicon octicon-link"></span></a>Notes, warnings:</h1>
49+
50+
<p>This tool will never be completely finished. For now, it works with all the SQL Server
51+
databases I had to migrate. If it doesn't work with yours, feel free to modify this,
52+
send me patches, or the SQL dump from your SQL Server Database, with the
53+
problem you are facing. I'll try to improve the code, but I need this SQL dump.</p>
54+
55+
<p>It won't migrate PL procedures, the languages are too different.</p>
56+
57+
<p>I usually only test this script under Linux. It should work on Windows, as I had to do it once
58+
with Windows, and on any Unix system.</p>
59+
60+
<h1>
61+
<a name="installation" class="anchor" href="#installation"><span class="octicon octicon-link"></span></a>Installation:</h1>
62+
63+
<p>Nothing to do on this part, as long as you don't want to use the data migration part: the script
64+
has no dependancy on fancy Perl modules, it only uses modules from the base perl distribution.</p>
65+
66+
<p>Just run the script with your perl interpreter, providing it with the requested options (--help
67+
will tell you what to do).</p>
68+
69+
<p>If you want to migrate the data, you'll need "Kettle", an Open Source ETL. Get the latest version
70+
from here: <a href="http://kettle.pentaho.com/">http://kettle.pentaho.com/</a> .
71+
You'll also need a SQL Server account with the permission to SELECT from the tables you want to migrate.</p>
72+
73+
<p>As Kettle is a Java program, you'll also need a recent JVM (Java 6 or 7 should do the trick).</p>
74+
75+
<h1></h1>
76+
77+
<p>Ok, I have installed Kettle and Java, I have sqlserver2pgsql.pl, what do I do now ?</p>
78+
79+
<p>You'll need several things:</p>
80+
81+
<ul>
82+
<li>The connection parameters to the SQL Server database:
83+
IP address, port, username, password, database name</li>
84+
<li>Access to an empty PostgreSQL database (where you want your migrated data)</li>
85+
<li>A text file containing a SQL dump of the SQL Server database</li>
86+
</ul><p>To get this SQL Dump, follow this procedure, in SQL Server's management interface:</p>
87+
88+
<ul>
89+
<li>Under SQL Server Management Studio, Right click on the database you want to export</li>
90+
<li>Select Tasks/Generate Scripts</li>
91+
<li>Click "Next" on the welcome screen (if it hasn't already been desactivated)</li>
92+
<li>Select your database</li>
93+
<li>In the list of things you can export, just change "Script Indexes" from False to True, then "Next"</li>
94+
<li>Select Tables then "Next"</li>
95+
<li>Select the tables you want to export (or select all), then "Next"</li>
96+
<li>Script to file, choose a filename, then "Next"</li>
97+
<li>Select unicode encoding (who knows…, maybe someone has put accents in objects names, or in comments)</li>
98+
<li>Finish</li>
99+
</ul><p>You'll get a file containing a SQL script. Get it on the server you'll want to
100+
run sqlserver2pgsql.pl from.</p>
101+
102+
<p>If you just want to convert this schema, run:</p>
103+
104+
<blockquote>
105+
<p>sqlserver2pgsql.pl -f my_sqlserver_script.txt -b name_of_before_script -a name_of_after_script -u name_of_unsure_script</p>
106+
</blockquote>
107+
108+
<p>The before script contains what is needed to import data (types, tables and columns).
109+
The after script contains the rest (indexes, constraints). It should be run
110+
after data is imported. The unsure script contains objects where we attempt to migrate, but cannot guarantee,
111+
such as views.</p>
112+
113+
<p>-conf uses a conf file. All options below can also be set there. Command line options will overwrite conf options.
114+
There is an example of such a conf file (example_conf_file)</p>
115+
116+
<p>You can also use the -i, -num and/or -nr options:</p>
117+
118+
<p>-i : Generate an "ignore case" schema, using citext, to emulate MSSQL's case insensitive collation.
119+
It will create citext fields, with check constraints.</p>
120+
121+
<p>-nr : Don't convert the dbo schema to public. By default, this conversion is done, as it converts MSSQL's default
122+
schema to PostgreSQL's default schema</p>
123+
124+
<p>-relabel_schemas is a list of schemas to remap. The syntax is : 'source1=&gt;dest1;source2=&gt;dest2'. Don't forget to quote this option or the shell might alter it
125+
there is a default dbo=&gt;public remapping, that can be cancelled with -nr. Use double quotes instead of simple quotes on Windows.</p>
126+
127+
<p>-num : Converts numeric (xxx,0) to the appropriate smallint, integer or bigint. It won't keep the constraint on
128+
the size of the scale of the numeric</p>
129+
130+
<p>-validate_constraints=yes/after/no: for foreign keys, if yes: foreign keys are created as valid in the after script (default)
131+
if no: they are created as not valid (enforced only for new rows)
132+
if after: they are created as not valid, but the statements to validate them are put in the unsure file</p>
133+
134+
<p>If you want to also import data:</p>
135+
136+
<blockquote>
137+
<p>./sqlserver2pgsql.pl -b before.sql -a after.sql -u unsure.sql -k kettledir \
138+
-sd source -sh 192.168.0.2 -sp 1433 -su dalibo -sw mysqlpass \
139+
-pd dest -ph localhost -pp 5432 -pu dalibo -pw mypgpass -f sql_server_schema.sql</p>
140+
</blockquote>
141+
142+
<p>-k is the directory where you want to store the kettle xml files (there will be
143+
one for each table to copy, plus the one for the job)</p>
144+
145+
<p>You'll also need to specify the connection parameters. They will be stored inside the kettle files (in
146+
cleartext, so don't make this directory public):
147+
-sd : sql server database
148+
-sh : sql server host
149+
-sp : sql server port (usually 1433)
150+
-su : sql server username
151+
-sw : sql server password
152+
-pd : postgresql database
153+
-ph : postgresql host
154+
-pp : postgresql port
155+
-pu : postgresql username
156+
-pw : postgresql password
157+
-f : the SQL Server structure dump file</p>
158+
159+
<p>You've generated everything. Let's do the import:</p>
160+
161+
<h1>
162+
<a name="run-the-before-script-creates-the-tables" class="anchor" href="#run-the-before-script-creates-the-tables"><span class="octicon octicon-link"></span></a>Run the before script (creates the tables)</h1>
163+
164+
<blockquote>
165+
<p>psql -U mypguser mypgdatabase -f name_of_before_script</p>
166+
167+
<h1>
168+
<a name="run-the-kettle-job" class="anchor" href="#run-the-kettle-job"><span class="octicon octicon-link"></span></a>Run the kettle job:</h1>
169+
170+
<p>cd my_kettle_installation_directory
171+
./kitchen.sh -file=full_path_to_kettle_job_dir/migration.kjb -level=detailed</p>
172+
173+
<h1>
174+
<a name="run-the-after-script-creates-the-indexes-constraints" class="anchor" href="#run-the-after-script-creates-the-indexes-constraints"><span class="octicon octicon-link"></span></a>Run the after script (creates the indexes, constraints...)</h1>
175+
176+
<p>psql -U mypguser mypgdatabase -f name_of_after_script</p>
177+
</blockquote>
178+
179+
<p>If you want to dig deeper into the kettle job, you can use kettle_report.pl to display the individual table's transfer performance. Then, if needed, you'll be able to modify the Kettle job to optimize it, using Spoon, Kettle's GUI</p>
180+
181+
<p>You can also use a configuration file if you like:</p>
182+
183+
<blockquote>
184+
<p>./sqlserver2pgsql.pl -conf example_conf_file -f mydatabase_dump.sql</p>
185+
</blockquote>
186+
187+
<p>There is an example configuration file provided. You can also mix the configuration file with command line options. Command line options have the priority over values set in the configuration file.</p>
188+
189+
<h1></h1>
190+
191+
<p>FAQ:</p>
192+
193+
<p>Why didn't you do everything in the Perl script ? I don't want to use Kettle.</p>
194+
195+
<p>Because I have once installed a Perl DBD driver for MS SQL's server, and I don't want to ever do it again... nor
196+
force you to do it :) You can either do it using ODBC, or using Sybase's driver. In both case, it will take you
197+
quite a while, and you'll suffer with large objects. With Kettle, on the other hand, you're using the JBDC driver,
198+
which is already one of Kettle's default drivers, along with PostgreSQL. So all the heavy lifting (converting
199+
LOBs and IMAGES and whatever to bytea) is directly done by both JDBC drivers, and should work with no efforts
200+
(except maybe adjust Java's memory parameters). If you get a memory error, try setting a JAVAMAXMEM environment
201+
variable to a higher value (4096) for 4GB for instance.</p>
202+
203+
<p>What is this IGNORE NULLS I have to change in kettle.properties ?</p>
204+
205+
<p>Because Kettle behaves by default the way Oracle behaves: for Oracle, a NULL and an empty string are the same.
206+
You don't want this here, because neither SQL Server nor PostgreSQL do this.</p>
207+
208+
<h1></h1>
209+
210+
<p>Licence: GPL v3</p>
211+
212+
<p><a href="http://www.gnu.org/licenses/gpl.html">http://www.gnu.org/licenses/gpl.html</a></p>
213+
</section>
214+
</div>
215+
216+
<!-- FOOTER -->
217+
<div id="footer_wrap" class="outer">
218+
<footer class="inner">
219+
<p class="copyright">Sqlserver2pgsql maintained by <a href="https://github.com/dalibo">dalibo</a></p>
220+
<p>Published with <a href="http://pages.github.com">GitHub Pages</a></p>
221+
</footer>
222+
</div>
223+
224+
225+
226+
</body>
227+
</html>

‎javascripts/main.js‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
console.log('This would be the main JS file.');

‎params.json‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
{"name":"Sqlserver2pgsql","tagline":"Migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database, as automatically as possible","body":"sqlserver2pgsql\r\n===========\r\n\r\nThis is a migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database, as automatically as possible.\r\n\r\nIt is written in Perl.\r\n\r\n\r\nIt does two things:\r\n\r\n * convert a SQL Server schema to a PostgreSQL schema\r\n\r\n * produce a Pentaho Data Integrator (Kettle) job to migrate \r\n all the data from SQL Server to PostgreSQL. This second part is optionnal\r\n\r\n\r\nNotes, warnings:\r\n==========================\r\nThis tool will never be completely finished. For now, it works with all the SQL Server\r\ndatabases I had to migrate. If it doesn't work with yours, feel free to modify this,\r\nsend me patches, or the SQL dump from your SQL Server Database, with the\r\nproblem you are facing. I'll try to improve the code, but I need this SQL dump.\r\n\r\nIt won't migrate PL procedures, the languages are too different.\r\n\r\nI usually only test this script under Linux. It should work on Windows, as I had to do it once\r\nwith Windows, and on any Unix system.\r\n\r\nInstallation:\r\n==========================\r\nNothing to do on this part, as long as you don't want to use the data migration part: the script\r\nhas no dependancy on fancy Perl modules, it only uses modules from the base perl distribution.\r\n\r\nJust run the script with your perl interpreter, providing it with the requested options (--help\r\nwill tell you what to do).\r\n\r\nIf you want to migrate the data, you'll need \"Kettle\", an Open Source ETL. Get the latest version\r\nfrom here: http://kettle.pentaho.com/ .\r\nYou'll also need a SQL Server account with the permission to SELECT from the tables you want to migrate.\r\n\r\nAs Kettle is a Java program, you'll also need a recent JVM (Java 6 or 7 should do the trick).\r\n\r\n==========================\r\n\r\nOk, I have installed Kettle and Java, I have sqlserver2pgsql.pl, what do I do now ?\r\n\r\nYou'll need several things:\r\n\r\n * The connection parameters to the SQL Server database:\r\n IP address, port, username, password, database name\r\n * Access to an empty PostgreSQL database (where you want your migrated data)\r\n * A text file containing a SQL dump of the SQL Server database\r\n\r\nTo get this SQL Dump, follow this procedure, in SQL Server's management interface:\r\n * Under SQL Server Management Studio, Right click on the database you want to export\r\n * Select Tasks/Generate Scripts\r\n * Click \"Next\" on the welcome screen (if it hasn't already been desactivated)\r\n * Select your database\r\n * In the list of things you can export, just change \"Script Indexes\" from False to True, then \"Next\"\r\n * Select Tables then \"Next\"\r\n * Select the tables you want to export (or select all), then \"Next\"\r\n * Script to file, choose a filename, then \"Next\"\r\n * Select unicode encoding (who knows…, maybe someone has put accents in objects names, or in comments)\r\n * Finish\r\n\r\nYou'll get a file containing a SQL script. Get it on the server you'll want to\r\nrun sqlserver2pgsql.pl from.\r\n\r\nIf you just want to convert this schema, run:\r\n\r\n> sqlserver2pgsql.pl -f my_sqlserver_script.txt -b name_of_before_script -a name_of_after_script -u name_of_unsure_script\r\n\r\nThe before script contains what is needed to import data (types, tables and columns).\r\nThe after script contains the rest (indexes, constraints). It should be run\r\nafter data is imported. The unsure script contains objects where we attempt to migrate, but cannot guarantee,\r\nsuch as views.\r\n\r\n-conf uses a conf file. All options below can also be set there. Command line options will overwrite conf options.\r\nThere is an example of such a conf file (example_conf_file)\r\n\r\nYou can also use the -i, -num and/or -nr options:\r\n\r\n-i : Generate an \"ignore case\" schema, using citext, to emulate MSSQL's case insensitive collation.\r\n It will create citext fields, with check constraints.\r\n\r\n-nr : Don't convert the dbo schema to public. By default, this conversion is done, as it converts MSSQL's default\r\nschema to PostgreSQL's default schema\r\n\r\n-relabel_schemas is a list of schemas to remap. The syntax is : 'source1=>dest1;source2=>dest2'. Don't forget to quote this option or the shell might alter it\r\nthere is a default dbo=>public remapping, that can be cancelled with -nr. Use double quotes instead of simple quotes on Windows.\r\n\r\n-num : Converts numeric (xxx,0) to the appropriate smallint, integer or bigint. It won't keep the constraint on\r\nthe size of the scale of the numeric\r\n\r\n-validate_constraints=yes/after/no: for foreign keys, if yes: foreign keys are created as valid in the after script (default)\r\n if no: they are created as not valid (enforced only for new rows)\r\n if after: they are created as not valid, but the statements to validate them are put in the unsure file\r\n\r\nIf you want to also import data:\r\n\r\n> ./sqlserver2pgsql.pl -b before.sql -a after.sql -u unsure.sql -k kettledir \\ \r\n -sd source -sh 192.168.0.2 -sp 1433 -su dalibo -sw mysqlpass \\\r\n -pd dest -ph localhost -pp 5432 -pu dalibo -pw mypgpass -f sql_server_schema.sql\r\n\r\n-k is the directory where you want to store the kettle xml files (there will be\r\none for each table to copy, plus the one for the job)\r\n\r\nYou'll also need to specify the connection parameters. They will be stored inside the kettle files (in\r\ncleartext, so don't make this directory public):\r\n-sd : sql server database\r\n-sh : sql server host\r\n-sp : sql server port (usually 1433)\r\n-su : sql server username\r\n-sw : sql server password\r\n-pd : postgresql database\r\n-ph : postgresql host\r\n-pp : postgresql port\r\n-pu : postgresql username\r\n-pw : postgresql password\r\n-f : the SQL Server structure dump file\r\n\r\nYou've generated everything. Let's do the import:\r\n\r\n# Run the before script (creates the tables)\r\n> psql -U mypguser mypgdatabase -f name_of_before_script\r\n# Run the kettle job:\r\n> cd my_kettle_installation_directory\r\n> ./kitchen.sh -file=full_path_to_kettle_job_dir/migration.kjb -level=detailed\r\n# Run the after script (creates the indexes, constraints...)\r\n> psql -U mypguser mypgdatabase -f name_of_after_script\r\n\r\nIf you want to dig deeper into the kettle job, you can use kettle_report.pl to display the individual table's transfer performance. Then, if needed, you'll be able to modify the Kettle job to optimize it, using Spoon, Kettle's GUI\r\n\r\nYou can also use a configuration file if you like:\r\n\r\n> ./sqlserver2pgsql.pl -conf example_conf_file -f mydatabase_dump.sql\r\n\r\nThere is an example configuration file provided. You can also mix the configuration file with command line options. Command line options have the priority over values set in the configuration file.\r\n\r\n================================\r\nFAQ:\r\n\r\nWhy didn't you do everything in the Perl script ? I don't want to use Kettle.\r\n\r\nBecause I have once installed a Perl DBD driver for MS SQL's server, and I don't want to ever do it again... nor \r\nforce you to do it :) You can either do it using ODBC, or using Sybase's driver. In both case, it will take you \r\nquite a while, and you'll suffer with large objects. With Kettle, on the other hand, you're using the JBDC driver, \r\nwhich is already one of Kettle's default drivers, along with PostgreSQL. So all the heavy lifting (converting \r\nLOBs and IMAGES and whatever to bytea) is directly done by both JDBC drivers, and should work with no efforts \r\n(except maybe adjust Java's memory parameters). If you get a memory error, try setting a JAVAMAXMEM environment \r\nvariable to a higher value (4096) for 4GB for instance.\r\n\r\n\r\n\r\nWhat is this IGNORE NULLS I have to change in kettle.properties ?\r\n\r\nBecause Kettle behaves by default the way Oracle behaves: for Oracle, a NULL and an empty string are the same. \r\nYou don't want this here, because neither SQL Server nor PostgreSQL do this.\r\n\r\n================================\r\nLicence: GPL v3\r\n\r\nhttp://www.gnu.org/licenses/gpl.html\r\n","google":"","note":"Don't delete this file! It's used internally to help with page regeneration."}

0 commit comments

Comments
 (0)