Menu
▾
▴
[Firebird-checkins] CVS: manual/src/docs/firebirddocs migrationmssql.xml,1.1.1.1,1.2
|
From: David J. <d_j...@us...> - 2001-06-01 02:51:14
|
Update of /cvsroot/firebird/manual/src/docs/firebirddocs
In directory usw-pr-cvs1:/tmp/cvs-serv21519
Modified Files:
migrationmssql.xml
Log Message:
Added qanda section and contributions from Yeoh Ray Mond. On behalf of Marcelo Lopez Ruiz
Index: migrationmssql.xml
===================================================================
RCS file: /cvsroot/firebird/manual/src/docs/firebirddocs/migrationmssql.xml,v
retrieving revision 1.1.1.1
retrieving revision 1.2
diff -U3 -r1.1.1.1 -r1.2
--- migrationmssql.xml 2001/05/14 17:44:02 1.1.1.1
+++ migrationmssql.xml 2001/06/01 02:51:11 1.2
@@ -1,5 +1,4 @@
-<?xml version = "1.0" encoding = "UTF-8"?>
-
+<?xml version="1.0" encoding="UTF-8"?>
<!-- - - > <!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN">< ! - - -->
<section id="migration-mssql">
<sectioninfo>
@@ -10,20 +9,34 @@
<address>mar...@xl...</address>
</affiliation>
</author>
+ <author>
+ <firstname>Ray Mond</firstname>
+ <surname>Yeoh</surname>
+ <!--
+ <affiliation>
+ <address>ray...@ex...</address>
+ </affiliation>
+ -->
+ </author>
</sectioninfo>
+ <!--
+ Note: pending items are being tagged as emphasis, instead of comment, to
+ avoid errors and warnings during the book build.
+ -->
+
<title>Converting from Microsoft SQL Server to Firebird</title>
<para>
<application>Microsoft SQL Server</application> (MS SQL) is a widely used
database server. There are three versions which
currently account for the majority of the user base: MS SQL 6.5, MS SQL 7 and
- MS SQL 2000
+ MS SQL 2000.
</para>
<section id="migration-mssql-intro">
<title>Introduction</title>
- <para>This section describes the convertion section itself.</para>
- <para>The convertion from MS SQL server documentation is meant, first,
+ <para>This section describes the conversion section itself.</para>
+ <para>The conversion from MS SQL server documentation is meant, first,
to help users evaluate whether the process should be performed at all. It then
goes on to detail how this can be done, adding bits of experience collected
by different people.</para>
@@ -31,12 +44,16 @@
migrating. First, moving the data from one database server to another
can be trivial or not, depending on your database schema. There are
many tools to help you with this process. The standard data
- convertions are listed in this document.</para>
+ conversions are listed in this document.</para>
<para>Second, you will need to migrate any stored procedures and
triggers manually. This is the tricky part. There are many differences,
some minor, some important; this document attempts to address most
of them, giving examples on the most frequent problems and how
to solve them.</para>
+ <para>At the end, you will find a section about Frequently Asked Questions
+ (FAQ) about Firebird and MS SQL. Note that these questions are not
+ always specific to migration in general, but if you have a question and
+ you are coming from MS SQL, it is very likely your question is among these.</para>
</section>
<section id="migration-mssql-pros-cons">
@@ -47,11 +64,15 @@
<title>Why migrate to <application>Firebird</application></title>
<para>This depends mostly on what version you are currently using and what
you are using MS SQL for.</para>
- <para>For example, if using MS SQL 6.5, it is a simple matter of considering
+ <para>For example, if using MS SQL 6.5, deciding it is a simple matter of considering
the features and ease of use. MS SQL 6.5 will work with fixed devices
rather than dynamically expanding files, which makes it very difficult
to balance ease of administration vrs. available space. There are numerous
- bugs and annoying behaviours which </para>
+ bugs and annoying behaviours in MS SQL 6.5; MS SQL 7 is a much better
+ database server. Two particularly annoying limits of MS SQL 6.5 are
+ the fact that variable-length character data cannot
+ be longer than 255 characters, and that you can only add (never remove)
+ columns to a table.</para>
<para>If you are using MS SQL 7, you know a lot of the little quirks have
been removed, but you are still missing some great features, such as
updateable views, greater control over identity fields, user-defined
@@ -135,13 +156,18 @@
devices. All data are kept in files in the normal file system available. Note
that you cannot use a raw disk partition to hold your databases.</para>
<para><emphasis>Important Note</emphasis>: MS SQL uses a logging mechanism to keep database
- consistent and survive crashes. <application>Firebird</application> uses a multi-generation mechanism
- to create copies in-place as they are required, but these are not written
- immediately to disk. While this provides a considerable speed gain, you
- can turn Forced Writes on a per-database basis to ensure that sudden
- blackouts will not compromise data integrity. If your server has a reliable
- environment such as a dedicated Linux box, and some form of UPS, turning
- Forced Writes on can be ignored.</para>
+ consistent and survive crashes. <application>Firebird</application>
+ uses a multi-generation mechanism to create copies in-place as they
+ are required, but these are not written immediately to disk. While
+ this provides a considerable speed gain, you can turn Forced Writes
+ on a per-database basis to ensure that sudden blackouts will not
+ compromise data integrity.</para>
+ <para>Forced Writes instructs the database server to flush all writes
+ to disk immediately, rather than allowing the operating system
+ to flush it at its leisure. If your server has a reliable environment
+ such as a dedicated Linux or Windows 2000 box, and some form
+ of UPS, turning Forced Writes on can be ignored.<emphasis>A reference
+ to Forced Writes documentation should be inserted here.</emphasis></para>
</section>
<section id="migration-mssql-db-admin">
@@ -219,7 +245,8 @@
and MS SQL, and how to translate types from one system to another.</para>
<para>MS SQL has different data types, depending on the version. The
following table lists the data types along with the version in which
- they were introduced.</para>
+ they were introduced. <emphasis>The Firebird data types should have a link
+ to more extensive documentation.</emphasis></para>
<para><table><title>Data Types Conversion Table</title>
<tgroup cols="4" align="center" colsep="1" rowsep="1">
<colspec colname='c1' colwidth="2cm"/>
@@ -324,7 +351,7 @@
<entry><para>6.5</para></entry>
<entry><para>numeric</para></entry>
<entry><para>NUMERIC</para></entry>
- <entry><para>In MS SQL, decimal and numeric are synonims.</para></entry>
+ <entry><para>In MS SQL, decimal and numeric are synonyms.</para></entry>
</row><row>
<entry><para>7</para></entry>
<entry><para>nvarchar</para></entry>
@@ -417,7 +444,7 @@
<type>DECIMAL</type> mean <emphasis>at least</emphasis> the request
precision (the digits to the right of the decimal symbol,
however, are maintained exactly). In MS SQL, on the other hand,
- <type>numeric</type> and <type>decimal</type> are synonims.</para>
+ <type>numeric</type> and <type>decimal</type> are synonyms.</para>
<para>There is also a very common quasi-data type, identity, which
can only be used when defining tables. This is an <type>int</type> which
is automatically assigned a value on insertion and cannot be
@@ -436,7 +463,7 @@
<title>Converting the identity data type</title>
<para>There are many ways to perform the conversion. In general,
Firebird is more flexible and powerful in this respect.</para>
- <para>The most direct convertion is to create a BEFORE trigger
+ <para>The most direct conversion is to create a BEFORE trigger
on the table, assigning to the previous column the value
from a generator. This ensures that the number is unique.</para>
<para>For added flexibility, a single generator can be used
@@ -481,7 +508,7 @@
for a record.</para>
<para>To use the field like this, create a BEFORE trigger on the
table with the field, and retrieve the value from a UDF.</para>
- <para>TODO: write the UDF and write the importing procedure</para>
+ <para><emphasis>TODO: write the UDF and write the importing procedure</emphasis></para>
</section>
</section>
@@ -567,12 +594,14 @@
be declared before the procedure or trigger body.</para>
<para>For example, compare the following code snippets.</para>
<programlisting><![CDATA[
+/* This is MS SQL syntax. */
CREATE PROCEDURE my_procedure
AS
DECLARE @my_variable int
SET @my_variable = 5
]]></programlisting>
<programlisting><![CDATA[
+/* This is FireBird syntax. */
CREATE PROCEDURE my_procedure
AS
DECLARE VARIABLE my_variable int
@@ -677,7 +706,7 @@
every statement); <command>GOTO</command> is used to group error handling
statements. In Firebird, there is a better error-handling
mechanism: the <command>WHEN...DO</command> statements.
- <!-- TODO: insert reference to docs here. --> </para>
+ <emphasis>TODO: insert reference to docs here.</emphasis></para>
<para>Of course, <command>GOTO</command> statements can be used for other purposes.
In these cases, using stored procedures correctly will
usually improve the database design.</para>
@@ -809,7 +838,7 @@
level problems found in MS SQL environments are void
when taken to a <application>Firebird</application>
database server. Contention between readers and
- writers is minimal and is resolved by the multigeneration
+ writers is minimal and is resolved by the multi-generational
architecture.</para>
</section>
<section id="migration-mssql-sql-syntax-cursors">
@@ -916,12 +945,12 @@
<para>The standard command-line utility is <command>isql</command>.
This is used usually when executing large scripts,
or when writing batch files.
- <!-- TODO: provide a link to docs. --></para>
+ <emphasis>TODO: provide a link to docs.</emphasis></para>
<para>When a graphical user interface (GUI) is available,
the administration tool will most probably be
<application>IBConsole</application>. This tool is similar to
MS SQL's <application>Enterprise Manager</application>.
- <!-- TODO: provide a link to docs. --></para>
+ <emphasis>TODO: provide a link to docs.</emphasis></para>
</section>
<section id="migration-mssql-client-access-developers">
<title>Client Access for Developers</title>
@@ -940,7 +969,7 @@
as they restrict themselves to a common SQL subset. There
are many tools which can use <application>Firebird</application> through ODBC
drivers.
- <!-- TODO: provide links to existing drivers --></para>
+ <emphasis>TODO: provide a link to existing drivers.</emphasis></para>
<para>Using an OLE DB drivers lets developers use
Microsoft's popular ADO API. This allows the <application>Firebird</application>
database to be reached from tools such as
@@ -1007,6 +1036,11 @@
<entry><para>IBConsole</para></entry>
</row><row>
<entry><para>Enterprise Manager</para></entry>
+ <entry><para>
+ <ulink url="http://sourceforge.net/projects/ibaccess">IBAccess</ulink>
+ </para></entry>
+ </row><row>
+ <entry><para>Enterprise Manager</para></entry>
<entry><para><ulink url="http://www.ibexpert.com/">IBExpert</ulink>
(commercial)</para></entry>
</row><row>
@@ -1039,6 +1073,132 @@
<ulink url="http://www.ibphoenix.com/ibp_contrib_download.html">
IBPhoenix Contributed Downloads</ulink></para>
</section>
+ </section>
+
+ <section id="migration-mssql-faq">
+ <title>Frequently Asked Question</title>
+ <para>Look here if you have any questions, before asking them to the
+ community. In all likelihood, they have been asked before and you
+ can find the answer right here. If you still have doubts, though,
+ don't hesitate to ask - <application>Firebird</application> has a strong user
+ community, willing to give a hand.</para>
+ <qandaset>
+ <qandaentry>
+ <question><para>
+ Are IDENTITY fields supported in Firebird?
+ </para></question>
+ <answer><para>
+ No, the field behaviour is not bound to the field type in
+ Firebird. There are many ways to achieve the same behaviour,
+ though. See <xref linkend="migration-mssql-data-types-identity"/>.
+ </para></answer>
+ </qandaentry>
+ <qandaentry>
+ <question><para>
+ Can I use ADO to access my Firebird database?
+ </para></question>
+ <answer><para>
+ Yes, you can. Microsoft offers an ODBC Provider, and you
+ can use this provider to connect to a Firebird ODBC data source.
+ </para></answer>
+ </qandaentry>
+ <qandaentry>
+ <question><para>
+ How can I access Firebird from ActiveX Server Pages?
+ </para></question>
+ <answer><para>
+ To access a Firebird database from ASP pages, you have two
+ methods. The first one is to write an ActiveX library using
+ Delphi or a similar tool, and access this object via
+ ASP scripts.
+ </para><para>
+ The second method is to create an ODBC data source, and
+ access it directly using ADO. For example, if you create
+ an ODBC data source named my_fb_db, you can access it
+ with the following script.
+ </para><warning><para>
+ Remember to use localhost: when specifying a local database,
+ otherwise you will not be able to connect.
+ </para></warning>
+ <programlisting><![CDATA[
+<% Option Explicit %>
+<%
+ Dim cnn, rst
+ ' Create a Connection
+ Set cnn = CreateObject("ADODB.Connection")
+ cnn.Open "Provider=MSDASQL.1;Data Source=my_fb_db;" _
+ & "Password=masterkey;User ID=sysdba"
+ ' Get the results of a query.
+ Set rst = cnn.Execute _
+ "SELECT FIRST_NAME, LAST_NAME, SALARY " _
+ & "FROM EMPLOYEES"
+%>
+<html>
+<head>
+ <title>My Firebird Database</title>
+</head>
+<body>
+<h1>My Firebird Database</h1>
+<p>The following table lists the employees.</p>
+<table>
+ <tr>
+ <th>First Name</th>
+ <th>Last Name</th>
+ <th>Salary</th>
+ </tr>
+<%
+ While Not rst.Eof
+ Response.Write "<tr>" _
+ & "<td>" & rst("FIRST_NAME") & "</td>" _
+ & "<td>" & rst("LAST_NAME") & "</td>" _
+ & "<td>" & rst("SALARY") & "</td>" _
+ & "</tr>"
+ rst.MoveNext
+ WEnd
+%>
+</table>
+</body>
+</html>
+]]></programlisting><para>
+ For more information on accessing Firebird from ASP, you can browse to <ulink
+ url="http://community.borland.com/article/0,1410,27152,00.html">
+ Connecting to InterBase using an Active Server Page</ulink>.
+ For more information on using ASP, you can browse to <ulink
+ url="http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/iisref/aspguide.htm">
+ Active Server Pages Guide</ulink>.
+ For more information on using ADO, you can browse to <ulink
+ url="http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/dasdk/ados4piv.htm">
+ MS ADO Start Page</ulink>.
+ </para></answer>
+ </qandaentry>
+ <qandaentry>
+ <question><para>
+ Which is better: superserver or classic?
+ </para></question>
+ <answer><para>
+ This is a tough one. Warning: personal opinion ahead.
+ Apparently, the classic version offers better performance
+ on multiprocessor nodes and is the first version to be ported
+ to any platform. However, the superserver version
+ will eventually have better performance on most platforms.
+ </para></answer>
+ </qandaentry>
+ <qandaentry>
+ <question><para>
+ Is Firebird free?
+ </para></question>
+ <answer><para>
+ Yes, Firebird is free. To download, use, deploy, rebuild. You
+ can't sell the database server, claim it as your own, or
+ change it and continue to call it Firebird -
+ other than that, you can do pretty much what you want to.</para>
+ <warning><para>I'm not a lawyer. If you want the
+ original documents, you can find a link to
+ both the original InterBase license and the Firebird
+ license at <ulink url="http://www.ibphoenix.com/">IBPhoenix</ulink>.
+ </para></warning></answer>
+ </qandaentry>
+ </qandaset>
</section>
</section>
|
