forked from dalibo/sqlserver2pgsql
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathindex.html
More file actions
202 lines (148 loc) · 9.08 KB
/
Copy pathindex.html
File metadata and controls
202 lines (148 loc) · 9.08 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
<!DOCTYPE html>
<html>
<head>
<meta charset='utf-8'>
<meta http-equiv="X-UA-Compatible" content="chrome=1">
<meta name="description" content="Sqlserver2pgsql : Migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database, as automatically as possible">
<link rel="stylesheet" type="text/css" media="screen" href="stylesheets/stylesheet.css">
<title>Sqlserver2pgsql</title>
</head>
<body>
<!-- HEADER -->
<div id="header_wrap" class="outer">
<header class="inner">
<a id="forkme_banner" href="https://github.com/dalibo/sqlserver2pgsql">View on GitHub</a>
<h1 id="project_title">Sqlserver2pgsql</h1>
<h2 id="project_tagline">Migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database, as automatically as possible</h2>
<section id="downloads">
<a class="zip_download_link" href="https://github.com/dalibo/sqlserver2pgsql/zipball/master">Download this project as a .zip file</a>
<a class="tar_download_link" href="https://github.com/dalibo/sqlserver2pgsql/tarball/master">Download this project as a tar.gz file</a>
</section>
</header>
</div>
<!-- MAIN CONTENT -->
<div id="main_content_wrap" class="outer">
<section id="main_content" class="inner">
<h1>
<a id="sqlserver2pgsql" class="anchor" href="#sqlserver2pgsql" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>sqlserver2pgsql</h1>
<p>This is a migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database, as automatically as possible.</p>
<p>It is written in Perl.</p>
<p>It does two things:</p>
<ul>
<li>convert a SQL Server schema to a PostgreSQL schema</li>
<li>produce a Pentaho Data Integrator (Kettle) job to migrate
all the data from SQL Server to PostgreSQL. This second part is optionnal</li>
</ul>
<h1>
<a id="notes-warnings" class="anchor" href="#notes-warnings" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Notes, warnings:</h1>
<p>This tool will never be completely finished. For now, it works with all the SQL Server
databases I had to migrate. If it doesn't work with yours, feel free to modify this,
send me patches, or the SQL dump from your SQL Server Database, with the
problem you are facing. I'll try to improve the code, but I need this SQL dump.</p>
<p>It won't migrate PL procedures, the languages are too different.</p>
<p>I usually only test this script under Linux. It should work on Windows, as I had to do it once
with Windows, and on any Unix system.</p>
<p>You'll need to install a few things to make it work. See INSTALL.md</p>
<h1>
<a id="install" class="anchor" href="#install" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Install</h1>
<p>See <a href="https://github.com/dalibo/sqlserver2pgsql/blob/master/INSTALL.md">https://github.com/dalibo/sqlserver2pgsql/blob/master/INSTALL.md</a></p>
<h1>
<a id="usage" class="anchor" href="#usage" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Usage</h1>
<p>Ok, I have installed Kettle and Java, I have sqlserver2pgsql.pl, what do I do now ?</p>
<p>You'll need several things:</p>
<ul>
<li>The connection parameters to the SQL Server database:
IP address, port, username, password, database name</li>
<li>Access to an empty PostgreSQL database (where you want your migrated data)</li>
<li>A text file containing a SQL dump of the SQL Server database</li>
</ul>
<p>To get this SQL Dump, follow this procedure, in SQL Server's management interface:</p>
<ul>
<li>Under SQL Server Management Studio, Right click on the database you want to export</li>
<li>Select Tasks/Generate Scripts</li>
<li>Click "Next" on the welcome screen (if it hasn't already been desactivated)</li>
<li>Select your database</li>
<li>In the list of things you can export, just change "Script Indexes" from False to True, then "Next"</li>
<li>Select Tables then "Next"</li>
<li>Select the tables you want to export (or select all), then "Next"</li>
<li>Script to file, choose a filename, then "Next"</li>
<li>Select unicode encoding (who knows…, maybe someone has put accents in objects names, or in comments)</li>
<li>Finish</li>
</ul>
<p>You'll get a file containing a SQL script. Get it on the server you'll want to
run sqlserver2pgsql.pl from.</p>
<p>If you just want to convert this schema, run:</p>
<blockquote>
<p>sqlserver2pgsql.pl -f my_sqlserver_script.txt -b name_of_before_script -a name_of_after_script -u name_of_unsure_script</p>
</blockquote>
<p>The before script contains what is needed to import data (types, tables and columns).
The after script contains the rest (indexes, constraints). It should be run
after data is imported. The unsure script contains objects where we attempt to migrate, but cannot guarantee,
such as views.</p>
<p>-conf uses a conf file. All options below can also be set there. Command line options will overwrite conf options.
There is an example of such a conf file (example_conf_file)</p>
<p>You can also use the -i, -num and/or -nr options:</p>
<p>-i : Generate an "ignore case" schema, using citext, to emulate MSSQL's case insensitive collation.
It will create citext fields, with check constraints.</p>
<p>-nr : Don't convert the dbo schema to public. By default, this conversion is done, as it converts MSSQL's default
schema to PostgreSQL's default schema</p>
<p>-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
there is a default dbo=>public remapping, that can be cancelled with -nr. Use double quotes instead of simple quotes on Windows.</p>
<p>-num : Converts numeric (xxx,0) to the appropriate smallint, integer or bigint. It won't keep the constraint on
the size of the scale of the numeric</p>
<p>-validate_constraints=yes/after/no: for foreign keys, if yes: foreign keys are created as valid in the after script (default)
if no: they are created as not valid (enforced only for new rows)
if after: they are created as not valid, but the statements to validate them are put in the unsure file</p>
<p>If you want to also import data:</p>
<blockquote>
<p>./sqlserver2pgsql.pl -b before.sql -a after.sql -u unsure.sql -k kettledir \
-sd source -sh 192.168.0.2 -sp 1433 -su dalibo -sw mysqlpass \
-pd dest -ph localhost -pp 5432 -pu dalibo -pw mypgpass -f sql_server_schema.sql</p>
</blockquote>
<p>-k is the directory where you want to store the kettle xml files (there will be
one for each table to copy, plus the one for the job)</p>
<p>You'll also need to specify the connection parameters. They will be stored inside the kettle files (in
cleartext, so don't make this directory public):
-sd : sql server database
-sh : sql server host
-sp : sql server port (usually 1433)
-su : sql server username
-sw : sql server password
-pd : postgresql database
-ph : postgresql host
-pp : postgresql port
-pu : postgresql username
-pw : postgresql password
-f : the SQL Server structure dump file</p>
<p>You've generated everything. Let's do the import:</p>
<pre><code> # Run the before script (creates the tables)
> psql -U mypguser mypgdatabase -f name_of_before_script
# Run the kettle job:
> cd my_kettle_installation_directory
> ./kitchen.sh -file=full_path_to_kettle_job_dir/migration.kjb -level=detailed
# Run the after script (creates the indexes, constraints...)
> psql -U mypguser mypgdatabase -f name_of_after_script
</code></pre>
<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>
<p>You can also use a configuration file if you like:</p>
<blockquote>
<p>./sqlserver2pgsql.pl -conf example_conf_file -f mydatabase_dump.sql</p>
</blockquote>
<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>
<h1>
<a id="faq" class="anchor" href="#faq" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FAQ</h1>
<p>See <a href="https://github.com/dalibo/sqlserver2pgsql/blob/master/FAQ.md">https://github.com/dalibo/sqlserver2pgsql/blob/master/FAQ.md</a></p>
<h1>
<a id="licence" class="anchor" href="#licence" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Licence</h1>
<p>GPL v3 : <a href="http://www.gnu.org/licenses/gpl.html">http://www.gnu.org/licenses/gpl.html</a></p>
</section>
</div>
<!-- FOOTER -->
<div id="footer_wrap" class="outer">
<footer class="inner">
<p class="copyright">Sqlserver2pgsql maintained by <a href="https://github.com/dalibo">dalibo</a></p>
<p>Published with <a href="https://pages.github.com">GitHub Pages</a></p>
</footer>
</div>
</body>
</html>