Interview with the author of SQL Server 2000 for the Oracle DBA
Last updated: January 20th '03 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

This month's 2 click survey:
Is .NET important for a database professional?

SQL Server Articles New

Click here to find out the top 15 SQL Server books purchased by this site's visitors! NEW

Featured Book:
Real-World SQL-DMO for SQL Server, by Allan Mitchell and Mark Allison

Click here to read a sample chapter from the book:
SQL Server Security Distilled
NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email with 'subscribe' in the subject line

Oracle to SQL Server Migration/Conversion Tool

Interview with Chris Kempster, the author of "SQL Server 2000 for the Oracle DBA"

SQL Server 2000 for the Oracle DBA
by Chris Kempster
Click here for more information, sample chapters, or to purchase this e-book.

Chris Kempster is the author of the new ebook "SQL Server 2000 for the Oracle DBA". He is currently working as a Senior DBA for ASG Pty Ltd in Perth, Western Australia. I had an opportunity to interview him recently. In this interview, Chris will point out best practices and provide advice for DBAs working with both SQL Server and Oracle.

Q. Chris, tell us about yourself and your career.

A. I started my IT career with a University, developing a variety of internal applications in Oracle Forms 4.5 and Reports 2.5. Then an year later, developing the University's web enrollment system with Oracle's first release of their web application server technology.

From here I moved into a DBA position, assisting in the management of Oracle Financials, Concept HR and a variety of smaller Oracle databases running on AIX and SUN platforms. Shortly after that, I joined ASG Pty Ltd as a Technical Consultant, specialising in application development, database administration and to a lesser degree, systems development architecture.

For the last four years I've been working for a variety of clients using both Oracle 8i, SQL Server 7 and SQL Server 2000. The core focus though has been on SQL Server 2000, with large-scale applications development teams, building mission critical web based applications using purely Microsoft technologies.

Q. What motivated you to write this ebook "SQL Server 2000 for the Oracle DBA"?

A. Over the past three years I have been collecting articles and partaking in numerous forums on SQL Server. To speed my understanding in this area, I began collating ideas into an e-book and taking it from site to site; adding to it as issues arose and problems were solved. After spending numerous hours after work on the book, my better half warned me that unless it came to something, I might as well sleep with the computer. So after 8 months I am back in favour with the wife and kids, and have a great book for other DBA's to leaver off.

Q. Who is your intended audience for this book?

A. The SQL Server RDBMS has seen incredible market growth over the last three to five years, mainly due to the release of SQL Server 7.0 and then SQL Server 2000 (including its OLAP and Data Mining technology). In turn, the DBA must be agile to market expectations, levering off core concepts from other database systems such as Oracle and DB2, and provide appropriate support and best practice in SQL Server. The multi-skilled DBA, along with the developer, system administrator and other technical professions, is a highly sought after resource for employers and employees.

The release of my e-book provides an essential resource for the professional DBA wanting to quickly adopt such a skill set. The book covers a wide variety of subjects all of which can be seen in the sample download of the contents page. It should be noted that whilst directed at Oracle DBAs, the book will provide the seasoned SQL Server or DB2 administrator with detailed information for fast problem resolution and best practices.

Q. What are your top suggestions for DBAs shifting from Oracle world into SQL Server?

A. There is a range of important issues and new concepts the DBA must understand when moving to SQL Server, but some of the core concepts and issues to start with are:

  • Database file placement strategies, RAID issues and basic SQL tuning principals and very similar for both databases.

  • Use profiler as much as possible, especially when using Enterprise Manager (EM) for basic administration tasks. This tracing tool is essential for day to day administration and problem solving.

  • Take time to clearly understand the SQL Server transaction and associated locking model.

  • Don't assume that SQL Server's lack of configurable parameters is a bad thing.

  • Spend time learning how to read, explain plan output via Query Analyser. Many basic tuning principals used in Oracle are very similar to SQL Server.

  • Understand the SQL Server storage model and its statistics collection paradigm.

  • Get backups running quickly via Database Maintenance Plans, remembering that a full or bulk logged database recovery model will require transaction log backups and are the only two models that allow point in time recovery.

  • Use Enterprise Manager extensively where possible, to begin with.

  • Use DTS to import/export data into/from SQL Server.

  • Clearly understand the difference between databases and instances and the dependencies between each, especially the system databases.

Q. What are the most common mistakes made by developers and DBAs, while migrating from Oracle to SQL Server?

A. When we talk about migration, I am also thinking of the core principals underlying the DBMS and how the DBA and developer moves not only their data, but their application components to a Microsoft development environment (if need be!). The new DBA needs to remember these basic SQL Server facts, which are easy to forget or take for granted:

  • Writers will block readers, transactions isolation level is critical along with short transactions.

  • Bulk logged and full recovery models require transaction log backups.

  • All backups are hot backups.

  • Manually configure instance memory settings to be 10-15% less than maximum server memory.

  • Take care while porting datetime data to SQL Server, being aware of time truncation. Remember that SQL Server has limited SQL-92 support.

  • Be aware of character datatype length restrictions and maximum row size restriction for non-blob data type rows.

  • There is no exception handling in T-SQL unlike PL/SQL.

  • There is only one optimizer in SQL Server, that being cost based analysis.

  • Statistics (histograms) are automatically collected by SQL Server.

  • All database objects should be owned by dbo.

  • The SQL Server listener is started when the instance starts and is managed via the server network utility.

I would highly recommend that any development team relatively new to the Microsoft game, hire a senior application architect for a few months to assist in class design, the over-arching security framework and how the application will hang together in terms of the business and data tiers. This is especially important for ongoing maintenance and extensibility; too many times have I seen good ideas turn to major disappointments due to poor architectural decisions early on in the project.

Q. What is the best way for migrating Oracle databases and applications to SQL Server? Some companies claim to have tools, that can convert PL/SQL to T-SQL. Do you think, that type of tools are good enough or the migration must be done manually?

A. I have done a lot of PL/SQL programming over the years, and although I have no first hand experience with 'code conversion applications', I would be very surprised if they could accurately convert to T-SQL without a lot of testing thereafter. The T-SQL language is somewhat limited and as such, the DBA will need to carefully evaluate any third party product before using it to complete PL/SQL code migration. Classic examples of this are exception handling, sequences, packages and Oracle applications using the object database constructs; all of which are very difficult to port automatically.

On porting any application, I always cringe when I hear that it must be one for one with reconcilable differences. This may be fine, but it is important to ask yourself why the endeavour is underway and as such, can important "lessons learnt" be applied to improve application architecture, and perhaps system stability and performance with a slightly higher cost? Due to the differences in architecture between Oracle apps and SQL Server (or should I say Microsoft apps), I would be pushing a manual port.

With all that said, the inclusion of SQL Server's DTS, data types that are basically generic and a reasonable similarities in terms of the SQL standards implemented, porting at least at the data tier is relatively painless.

Q. Would you go with either SQL Server or Oracle, solely based on a specific application type? If so, what type of applications would those be? If not, would you say, "both SQL Server and Oracle can handle any type of application"?

A. This is a very tough question to answer. I have worked with a large number of SQL Server and Oracle based applications with a variety of read/write characteristics and application architectures, all of which perform very well. Even so, consider some of these points:

  • If there is a real need or concern about being locked into a single operating system, then use Oracle.

  • The other key area is the cost of administration. There is no doubt that SQL Server is much easier to install and manage than Oracle, with a lower TCO than Oracle.

  • If low level system configurability is critical, then use Oracle.

  • In clustered scenarios, remember that SQL Server uses are shared nothing model, as such, only one server at any one time can read/write to the database files. This can be very restrictive in large scale applications supporting thousands of users.

I don't want to jump either way. There is no doubt that SQL Server 2000 is an enterprise quality RDBMS, and the DTS, OLAP and Data Mining options are feature rich. A knowledgeable DBA in both platforms, with a solid understanding of the business, licensing issues, vendor lock-in, staffing, and supporting application issues should be the key drivers to making the final decision.

Q. Can we expect more such ebooks from you in the near future?

A. I will be completing two "add on" chapters/articles over the next few months, one being Replication and the other covering XML in more detail. I am also in the very early planning stages for a series of case study articles based on industry projects, detailing the problem, the solutions used along with supporting hardware.

Q. Apart from what is already discussed, do you have any other suggestions for your readers?

A. I would like to encourage the DBA new to SQL Server, to be open-minded and embrace the self-administering architecture. The reader may be surprised at the wide range of features that will take some time to master, and further still, will allow the DBA to venture off into the world of application architecture in the Microsoft environment.

Thanks Chris, for answering our questions and sharing your knowledge and experience!

SQL Server 2000 for the Oracle DBA
by Chris Kempster
Click here for more information, sample chapters, or to purchase this e-book.