Migrating from Intebrase to SQL Server

Hi.
We've been having many problems with our current Interbase database
(v6.0.2.0). These are mainly corruption issues with the constant need to
backup and restore, and the fact that it is almost impossible to reliably
change metadata in a live environment.
We need to change servers, and our two options are either upgrading to
Interbase 7.1, or migrating to SQL Server 2000.
These are our requirements:
- the ability to reliaby and efficiently cope with huge amounts of data
- the server must be able to cope seemlessly with clients not disconnecting
cleanly and transactions left open (IB seems to have trouble with this), or
at least provide some mechanism to view such transactions and kill them
- the ability to change metada in a live environment with many connected
users (create/drop tables, foreign keys, etc - is this possible with any DB
at all?)
- it is critical that the database is up 24/7 - we can afford no downtime -
is this something SQL Server can provide?
I have also read a lot about how Interbase is unique in that it provides a
multi-generational architecture - presumably this means SQL Server does not,
but what are the implications of this? Could someone explain how SQL's
architecture differs from IB's with respect to this?
Has anyone else had any experience (good or bad) migrating from IB to SQL?
Perhaps our problems would not be solved by this and therefore it would be a
waste of time and money?
We basically need as much info on the subject as possible so we can make an
informed decision.
MikeAs you haven't gotten any replies yet, I'll only chime in with my little comment (I haven't worked
with Interbase):
> I have also read a lot about how Interbase is unique in that it provides a
> multi-generational architecture - presumably this means SQL Server does not,
> but what are the implications of this? Could someone explain how SQL's
> architecture differs from IB's with respect to this?
The multi-generation stuff means that when you start reading, you get a "personal snapshot" of the
data. You work with that over time, and other modifying the real data will not block you (but you
have the be extremely careful if you then have to do modifications based on this old data). SQL
Server doesn't have this, so you have blocking. If you try to read something that someone else has
modified but not committed, you will be blocked until the modifier ends the transaction. So, in SQL
Server, you want to use short transactions.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike Tremendous" <noone@.hotmail.com> wrote in message news:ONxqCMmrDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi.
> We've been having many problems with our current Interbase database
> (v6.0.2.0). These are mainly corruption issues with the constant need to
> backup and restore, and the fact that it is almost impossible to reliably
> change metadata in a live environment.
> We need to change servers, and our two options are either upgrading to
> Interbase 7.1, or migrating to SQL Server 2000.
> These are our requirements:
> - the ability to reliaby and efficiently cope with huge amounts of data
> - the server must be able to cope seemlessly with clients not disconnecting
> cleanly and transactions left open (IB seems to have trouble with this), or
> at least provide some mechanism to view such transactions and kill them
> - the ability to change metada in a live environment with many connected
> users (create/drop tables, foreign keys, etc - is this possible with any DB
> at all?)
> - it is critical that the database is up 24/7 - we can afford no downtime -
> is this something SQL Server can provide?
> I have also read a lot about how Interbase is unique in that it provides a
> multi-generational architecture - presumably this means SQL Server does not,
> but what are the implications of this? Could someone explain how SQL's
> architecture differs from IB's with respect to this?
> Has anyone else had any experience (good or bad) migrating from IB to SQL?
> Perhaps our problems would not be solved by this and therefore it would be a
> waste of time and money?
> We basically need as much info on the subject as possible so we can make an
> informed decision.
> Mike
>

Migrating AS/400 DB2 to SQL Server

The client I'm working at right now is currently moving from their current
ERP contained on AS/400 to a MS SQL Server based ERP known as SYSPRO. In
doing so they are migrating 2 physical AS/400's with 4 business units (2 on
each 400) over to SYSPRO. They have moved one out of the four and a
preparing to move the the second. When migrating over to SYSPRO they only
moved the needed information to go live on the new system hence leaving years
of sales, orders, customer and product information.
So my question is there anything just blazingly important that I should know
when using DTS to pull all of this data over to SQL Server?
I know that the structure of the 400 DB2 isn't exactly the same as SQL
Server. From what I understand multiple Libraries make up a database in the
400. Each of those contain files and logicals. Files look to be the same as
a tables and logicals look to be the same as views.
ThxYou're pretty much on the ball with the file vs table and logical vs view
deal, for what you're trying to do, they're affectively equivalent. I ran
into some trouble with this in a past life, though. There was a lot of
trailing white space in the character fields I pulled out of the 400. It was
a different ERP, and it may have been human error causing it (I'm no DB2
guru), but I wound up having to RTRIM a lot of stuff on it's way over. Only
issue I ran into, though.
"Chris Stevenson" wrote:
> The client I'm working at right now is currently moving from their current
> ERP contained on AS/400 to a MS SQL Server based ERP known as SYSPRO. In
> doing so they are migrating 2 physical AS/400's with 4 business units (2 on
> each 400) over to SYSPRO. They have moved one out of the four and a
> preparing to move the the second. When migrating over to SYSPRO they only
> moved the needed information to go live on the new system hence leaving years
> of sales, orders, customer and product information.
> So my question is there anything just blazingly important that I should know
> when using DTS to pull all of this data over to SQL Server?
> I know that the structure of the 400 DB2 isn't exactly the same as SQL
> Server. From what I understand multiple Libraries make up a database in the
> 400. Each of those contain files and logicals. Files look to be the same as
> a tables and logicals look to be the same as views.
> Thx

Migrate SQL 2000 to New SQL 2005 Installation.

Hi all, in my current environment, I have a single server SQL 2000 setup that's being replaced. I'm in the process of installing a new SQL 2005 cluster with the thought of taking advantage of 2005 mirroring and clustering, but have a few questions.

1. My thinking is that I can migrate my 2000 databases to 2005, but leave the databases in 8.0 (2000) mode. Are there any issues with this? I know they won't take advantage of the 2005 performance boost

2. Some of the apps don't support 2005 yet so I need to leave them in 2000 mode until they do. Is SQL 2005 fully backwards compatible with a SQL 2000 database?

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode?

Thanks for any assistance with this!

Start by running the SQL Server 2005 upgrade advisor against your SQL 2000 installation. This will give you good information on problems that you might need to address. Also, if your applications are purchased, check with the vendors for advice on compatibility. But the bottom line is that every application is unique and the only way you'll know for sure is by carefully testing.

Sorry, I don't know the answer to your question on mirroring.

Paul

|||Thanks for the response Paul, Doesn't the upgrade advisor only tell me info in regards to actually upgrading the database? I'm looking at keeping them at 8.0 compatibility on SQL 2005. Basically, I'm looking at keeping the databases at the SQL2000 level on a SQL2005 box. I guess my real question is: Are databases that are running in SQL2000 compatibility mode on a SQL2005 box fully backwards compatible?|||

Most applications will upgrade with no problems but there are a small number of breaking changes in 9.0, even if you stay in 8.0 compat mode. Look up "compatibility" and "sp_dbcmptlevel" in BOL for details.

|||

hi Tynman

Yes u can always keep SQL2000 database compatibility level i.e. 80 in sql 2005 by specifying compatibility level in system stored procedure "sp_dbcmptlevel" and give compatibility level as 80 with database name.

Ur command will be: sp_dbcmplevel dbname, 80

|||

BUMP!

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode (compatibility level 80)?

Do anyone have an answer to this question?

|||I am trying to restore a SQL 2005 DB into SQL 2000 and it errors out "too many objects on 64 allowed......blah..blah..blah.." something to that effect.

Migrate SQL 2000 to New SQL 2005 Installation.

Hi all, in my current environment, I have a single server SQL 2000 setup that's being replaced. I'm in the process of installing a new SQL 2005 cluster with the thought of taking advantage of 2005 mirroring and clustering, but have a few questions.

1. My thinking is that I can migrate my 2000 databases to 2005, but leave the databases in 8.0 (2000) mode. Are there any issues with this? I know they won't take advantage of the 2005 performance boost

2. Some of the apps don't support 2005 yet so I need to leave them in 2000 mode until they do. Is SQL 2005 fully backwards compatible with a SQL 2000 database?

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode?

Thanks for any assistance with this!

Start by running the SQL Server 2005 upgrade advisor against your SQL 2000 installation. This will give you good information on problems that you might need to address. Also, if your applications are purchased, check with the vendors for advice on compatibility. But the bottom line is that every application is unique and the only way you'll know for sure is by carefully testing.

Sorry, I don't know the answer to your question on mirroring.

Paul

|||Thanks for the response Paul, Doesn't the upgrade advisor only tell me info in regards to actually upgrading the database? I'm looking at keeping them at 8.0 compatibility on SQL 2005. Basically, I'm looking at keeping the databases at the SQL2000 level on a SQL2005 box. I guess my real question is: Are databases that are running in SQL2000 compatibility mode on a SQL2005 box fully backwards compatible?|||

Most applications will upgrade with no problems but there are a small number of breaking changes in 9.0, even if you stay in 8.0 compat mode. Look up "compatibility" and "sp_dbcmptlevel" in BOL for details.

|||

hi Tynman

Yes u can always keep SQL2000 database compatibility level i.e. 80 in sql 2005 by specifying compatibility level in system stored procedure "sp_dbcmptlevel" and give compatibility level as 80 with database name.

Ur command will be: sp_dbcmplevel dbname, 80

|||

BUMP!

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode (compatibility level 80)?

Do anyone have an answer to this question?

|||I am trying to restore a SQL 2005 DB into SQL 2000 and it errors out "too many objects on 64 allowed......blah..blah..blah.." something to that effect.sql

Migrate SQL 2000 to New SQL 2005 Installation.

Hi all, in my current environment, I have a single server SQL 2000 setup that's being replaced. I'm in the process of installing a new SQL 2005 cluster with the thought of taking advantage of 2005 mirroring and clustering, but have a few questions.

1. My thinking is that I can migrate my 2000 databases to 2005, but leave the databases in 8.0 (2000) mode. Are there any issues with this? I know they won't take advantage of the 2005 performance boost

2. Some of the apps don't support 2005 yet so I need to leave them in 2000 mode until they do. Is SQL 2005 fully backwards compatible with a SQL 2000 database?

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode?

Thanks for any assistance with this!

Start by running the SQL Server 2005 upgrade advisor against your SQL 2000 installation. This will give you good information on problems that you might need to address. Also, if your applications are purchased, check with the vendors for advice on compatibility. But the bottom line is that every application is unique and the only way you'll know for sure is by carefully testing.

Sorry, I don't know the answer to your question on mirroring.

Paul

|||Thanks for the response Paul, Doesn't the upgrade advisor only tell me info in regards to actually upgrading the database? I'm looking at keeping them at 8.0 compatibility on SQL 2005. Basically, I'm looking at keeping the databases at the SQL2000 level on a SQL2005 box. I guess my real question is: Are databases that are running in SQL2000 compatibility mode on a SQL2005 box fully backwards compatible?|||

Most applications will upgrade with no problems but there are a small number of breaking changes in 9.0, even if you stay in 8.0 compat mode. Look up "compatibility" and "sp_dbcmptlevel" in BOL for details.

|||

hi Tynman

Yes u can always keep SQL2000 database compatibility level i.e. 80 in sql 2005 by specifying compatibility level in system stored procedure "sp_dbcmptlevel" and give compatibility level as 80 with database name.

Ur command will be: sp_dbcmplevel dbname, 80

|||

BUMP!

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode (compatibility level 80)?

Do anyone have an answer to this question?

|||I am trying to restore a SQL 2005 DB into SQL 2000 and it errors out "too many objects on 64 allowed......blah..blah..blah.." something to that effect.

Migrate MSDE 2000 to SQL 2000

I would like to know how I can move current MSDE 2000
database to SQL 2000 database. I want to put all
databases on my 1 SQL server.
Thanks.
The simplist way to do this is to backup and restore the databases to your
new server. You will then need to add any SQL Server logins to your new
server that are users in your databases. Then run sp_change_users_login (see
BOL) to synch the database users with the logins.
Jim
"SQL_Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:12dc01c50419$1e354bf0$a601280a@.phx.gbl...
>I would like to know how I can move current MSDE 2000
> database to SQL 2000 database. I want to put all
> databases on my 1 SQL server.
> Thanks.

Migrate from DB2 to MSSQL

Dear All,

I am exploring the feasibility of migrating from DB2 (v7) to MSSQL server 2005. My current applications are using UDB, but the DBA wants to change to MSSQL. I believe the impact is huge. For example, how to create schema in MSSQL, how to migrate the data...

Anyone had the experience before? Could kindly suggest some reference for such a migration? Thanks!

Regards,From the database perspective, this is almost trivial.

Use ErWin, Visio, or another modeling tool to harvest your schema from the DB2 database, then project that (empty) schema into an MS-SQL database), then use DTS or a similar tool to migrate your data. If you aren't as finicky about the schema details as I am, you can simply use DTS all by itself, which will get you pretty close.

A much, much larger issue is the difference in string and date handling. DB2 syntax for both string and date operations is significantly different than MS-SQL syntax for the same operations. Unless you've been abnormally careful to avoid any manipulation of strings and dates within your SQL, there will be a potentially enormous amount of effort needed for this conversion.

-PatP|||String Manipulation?

Dates, OK, you are going to have to trim the DB2 dates as SQL Server only supports 3 ms...and only cpu clock speeds...I believe every .333 ms

But strings/chars?

Huh?

And what, besides sheer whim or only knowledge of sql sever does the dba want to move off DB2|||Sorry for my ignorance: my table in DB2 is something like "db2n1.tuser", while db2n1 is the schema and tuser is the table name. Is there such a corresponding schema name in MSSQL?

From the database perspective, this is almost trivial.

Use ErWin, Visio, or another modeling tool to harvest your schema from the DB2 database, then project that (empty) schema into an MS-SQL database), then use DTS or a similar tool to migrate your data. If you aren't as finicky about the schema details as I am, you can simply use DTS all by itself, which will get you pretty close.

A much, much larger issue is the difference in string and date handling. DB2 syntax for both string and date operations is significantly different than MS-SQL syntax for the same operations. Unless you've been abnormally careful to avoid any manipulation of strings and dates within your SQL, there will be a potentially enormous amount of effort needed for this conversion.

-PatP|||Sorry for my ignorance: my table in DB2 is something like "db2n1.tuser", while db2n1 is the schema and tuser is the table name. Is there such a corresponding schema name in MSSQL?Yes, there is. Your DBA can tell you for sure, but my first guess is "dbo".

You are treading very near one of the confusing points in the switch from DB2 to MS-SQL... In MS-SQL 2000 and all earlier versions, there was a logical concept called a "user" that actually spanned across many of the concepts such as schema, permissions, etc in the purely relational world. Each user could logically own objects within the database, giving them something quite close to their own schema. There was a kind of "uber user" named dbo (an acronym for DataBase Owner) that was the default user under the name resolution rules.

In MS-SQL 2005, true schemas were introduced into the product. The schemas don't behave quite the way that you are accustomed to in DB2, but they're pretty close. For the moment, I wouldn't worry too much about them unless you have to make adjustments to cope with them.

-PatP

Migrate database objects to new server

How to migrate all SQL 2000 objects (tables, views, SPs)
and users from current SQL server to a new SQL 2000
server in different domain? DO I need to modify DTS
packages?
Thanks in advance.
-DonHi
You could restore a backup or detach/attach the database. This should be
alot quicker.
See
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
For DTS packages you can either follow the instructions in the second link
or use DTS backup utility from
http://www.sqldts.com/default.aspx?242
John
"Don Walter" <anonymous@.discussions.microsoft.com> wrote in message
news:8e2c01c3b5cf$38e49cb0$a601280a@.phx.gbl...
> How to migrate all SQL 2000 objects (tables, views, SPs)
> and users from current SQL server to a new SQL 2000
> server in different domain? DO I need to modify DTS
> packages?
> Thanks in advance.
> -Don|||Thank you John for your advice.
-Don
>--Original Message--
>Hi
>You could restore a backup or detach/attach the
database. This should be
>alot quicker.
>See
>http://support.microsoft.com/default.aspx?scid=kb%3ben-
us%3b224071
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;Q314546
>For DTS packages you can either follow the instructions
in the second link
>or use DTS backup utility from
>http://www.sqldts.com/default.aspx?242
>John
>"Don Walter" <anonymous@.discussions.microsoft.com> wrote
in message
>news:8e2c01c3b5cf$38e49cb0$a601280a@.phx.gbl...
>> How to migrate all SQL 2000 objects (tables, views,
SPs)
>> and users from current SQL server to a new SQL 2000
>> server in different domain? DO I need to modify DTS
>> packages?
>> Thanks in advance.
>> -Don
>
>.
>