| Author |
Topic |
|
dtrivedi
Posting Yak Master
153 Posts |
 Posted - 2010-07-27 : 09:58:23
|
| I was just hired as a /DBA to primarly move our system from firebird to SQL 2008 I have oracle background and no sql background at all my boss knows that he hired me on and told me traning would be providedi need to start thinking about this migration any ideas on where i would start? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-07-27 : 11:28:02
|
| I'll dip in (very gingerly)1. Research SQL (language) differences between the two. Some Oracle SQL language constructs are not valid on MSSQL. There's a FAQ on the MS or Oracle somewhere about this.2. Oracle Cursors <> MSSQL Cursors - the latter particularly are evil.3. Do you have to move data, data & code or code (and data structures) only?4. Research data type differences (especially any boundary cases for numeric values)5. Are you changing O/S? I'm not sure if Oracle is available on "Wintel".6. Do you have international characters? Japanese, Swedish, Chines, Russian, etc? This may matter in moving your data.7. Is this a one-off exercise, or will you have to sync data on an on going basis?8. Establish some sort of reconciliation process to ensure all data maps & is converted over properly.9. What's your front end? Oracle forms would need to be re-coded into some sort of other UI. MSSQL doesn't have that tight integration with the DB that Oracle does.Others will have far more practical experience in this area, but above is a start. I have programmed with MSSQL for 8+ years and Oracle for 3 years, but haven't had to migrate anything from one to the other.Most of the programming and system setup concepts of one are nearly identical compared to the other. Backup/repair strategies may need to be implemented different. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-07-27 : 12:21:13
|
| Are you migrating from the RDBMS Firebird to SQL Server?I never heard of Firebird before I googled it, so I doubt you are going to find a lot of help here for Firebird to SQL Server conversions.I would concentrate on learning SQL Server as much as you can so that you can setup a quality data model in SQL Server, and learn as much about Firebird as you need to understand the current schema and be able to extract data to load into SQL Server.CODO ERGO SUM |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-07-27 : 14:09:38
|
| Michaelyes RDBMS firebird to SQL Server 2008 I googled it also and came up with nothing something that did spike my interest and would help with the migration i think is a service called Spectral CoreFull Convert Enterprise has anyone heard of that? |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-07-27 : 14:11:04
|
Andrew it is a physical move of the data the firebird rdbms is so old it isn't useful to the retail side of usthanks for you list i will start there atleast and see where it leads mehave you heard of specteal corefull convert enterprise? quote:
Originally posted by AndrewMurphy
I'll dip in (very gingerly)1. Research SQL (language) differences between the two. Some Oracle SQL language constructs are not valid on MSSQL. There's a FAQ on the MS or Oracle somewhere about this.2. Oracle Cursors <> MSSQL Cursors - the latter particularly are evil.3. Do you have to move data, data & code or code (and data structures) only?4. Research data type differences (especially any boundary cases for numeric values)5. Are you changing O/S? I'm not sure if Oracle is available on "Wintel".6. Do you have international characters? Japanese, Swedish, Chines, Russian, etc? This may matter in moving your data.7. Is this a one-off exercise, or will you have to sync data on an on going basis?8. Establish some sort of reconciliation process to ensure all data maps & is converted over properly.9. What's your front end? Oracle forms would need to be re-coded into some sort of other UI. MSSQL doesn't have that tight integration with the DB that Oracle does.Others will have far more practical experience in this area, but above is a start. I have programmed with MSSQL for 8+ years and Oracle for 3 years, but haven't had to migrate anything from one to the other.Most of the programming and system setup concepts of one are nearly identical compared to the other. Backup/repair strategies may need to be implemented different.
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-27 : 15:19:32
|
| "have you heard of specteal corefull convert enterprise? "Nope :(Folk here can no doubt help with specific problems that you encounter along the way though ... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-07-27 : 15:24:31
|
quote:
Originally posted by dtrivedi
Michaelyes RDBMS firebird to SQL Server 2008 I googled it also and came up with nothing something that did spike my interest and would help with the migration i think is a service called Spectral CoreFull Convert Enterprise has anyone heard of that?
You should look at this link if you haven't seen it already:http://www.firebirdsql.org/It seems to have a lot of documentation for Firebird/Interbase, as well as links to support resources.CODO ERGO SUM |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-07-27 : 15:31:04
|
| I would start by...1. Figuring out how to export all of the DDL (and I me ALL) from Firebird2. Convert that to SQL Server DDL3. Figure out how to export all of the data to Delimited files (I'd suggest |~|)4. I'd the create the structure in SQL Server...I might consider making everything varchar as staging stables)5. The I'd bcp the data in6. If I made everything varchar, I'd audit the data based on the actual data typesDo you have ERWin?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-27 : 15:36:27
|
"I'd suggest |~|"Sorry, but "Yuck"! We get people registering on our eCommerce sites as ||||||me@domain.com ... they are expecting you Brett! Delimited needs to be something not found in the data, or a proper CSV that SQL will understand (e.g. field wrapped in double-quote if it contains comma, and then if it also contains double-quotes they should be doubled up. Still pretty manky, and it won't hack line-breaks in the data.Binary transfer database-to-database preferred.Agree with the staging tables. The data may be numeric / date / whatever but may be stored in char data types  or have things like 99/99/9999 with special significance, or even perhaps allow 31-Feb (like MySQL used to ...) |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-27 : 17:18:50
|
| "You've seen that before?"Nope, but its certainly a whole pile better than a single pipe, provided the 3-char delimiter doesn't appear in the data itself. We had a client recently who's "data transfer specialist" insisted on single-pipe delimiter. I asked for a specification for escaping embedded pipe characters and I was told it was a well recognised standard that I should be familiar with. The data I looked at had enough pipe characters in it to spoil their day; each conversion took them something like 3 days to process, which I suppose is how "data transfer specialists" make their money.P.T.P still won't handled line breaks (maybe BCP would take |~~| for a row-delimiter - instead of CR/LF?)">> field wrapped in double-quote if it contains comma,How do you propose to load that? SSIS?"Never tried it, always do Binary/database-to-database transfers to avoid the problem of delimiters and line breaks (except when clients insist on giving me XLS files with half their 6 digit product codes formatted as dates, and the other half missing a leading zero that turns out to be critical ...)But, ghastly as it appears, it is well recognised as a format; doesn't clash with whatever is in the data fields; but won't handle line breaks ...Actually, for simple delimited data I would favour TAB as the separator (if it will not appear in the data itself) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-07-27 : 17:25:01
|
| I would imagine a TAB is mor likely tat |~|And that Bozo...Pipe is the standard...MoronMust have had an Oracle background...had a woman sell me the same set of goods...it was her data to deliverEverytime she came up with an idea so she didn't have to create |~| I threw it back her to "fix" itI think she was using TOAD and didn't know anything but a wizard...she finally relented....Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-27 : 17:38:01
|
"And that Bozo...Pipe is the standard...Moron"Well, he was the laughing stock of OUR office everytime we extracted data for him ..."Oh, and to this day...I have NEVER had |~| in the data.."Yup, I condescendingly conceded you that when you patiently explained it was a 3-char-delimiter; but I didn't let on  . Its a fair choice for sure. Or perhaps we should use GUIDs for delimiters?  I think my SQL Spliiter Function would need some reworking  |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-07-27 : 17:40:14
|
| Seems like a premature discussion. The question is how the data will get out of Firebird, and what is available for that.It could be as simple as configuring an ODBC driver and then doing a direct SQL select from Fribird to load into a SQL Server table. Maybe even setup a link server.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-27 : 17:50:27
|
| Sorry, I was getting off track ....database-to-database gets my vote too. |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-07-28 : 10:53:27
|
| oh wow now i'm at a whole new level of confused :) |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-29 : 02:11:46
|
| I think if anyone had used it they would have spoken up (there are a couple of regulars absent at present, but most regulars see most threads).At a quick glance this tool is only going to move the data, isn't it?That's a job to be done, for sure, but SQL has pretty good tools for getting data from A.N.Other source built in (SSIS)Firebird (two minute guide) says it has Stored Procedures and Triggers ... if they are being used in the database you are converting then there is no doubt some significant work to convert them to SQL Server.Plus (if it were me) I would be wanting to embrace some SQL Server specific features to enhance the application (otherwise, what's the point of moving?). Doing a rough and ready conversion and then enhancing has never worked on all the projects I've been involved in - the rough and ready conversion creates a bowl of spaghetti - maybe fine for like-for-like operation in the new environment, but its a crap place to start from when enhancing something. Rewrite is what eventually happens (and smart projects do the rewrite at the get-go IMHO) |
|
|
|
|
firebird database