Oracle SQL Developer Data Modeler and Microsoft SQL Server

Even though my target database in my new job is Microsoft SQL Server rather than Oracle RDBMS, I still use an Oracle tool for a key part of my work - data modeling.  My tool of preference is Oracle SQL Developer Data Modeler (SDDM).  While there are other data modeling tools out there, and some (ERWin and TOAD Data Modeler are two I've used) are very good, I prefer SDDM.

• It is free to use. (Thank you, Oracle!)
• I haven't been allowed administrative authority on my government issued laptop, so I can't install software.  But to install SDDM, you just unzip - which I CAN do.
• It can reverse engineer from SQL Server - all you have to do is point it at a SQL Server JDBC driver.
• It supports DDL generation for databases other than Oracle RDBMS - including SQL Server.
• It understands the difference between Logical, Relational, and Physical Modeling.  Some tools just model databases (relational) - tables, columns, foreign keys.  They call it Entity/Relationship diagramming (logical) but it isn't really.
• Whatever SDDM can't do, you can probably script - more on this in a later post.
• There is good information and support available even if you don't have a contract with Oracle Support.  Some resources:
• Jeff Smith - Oracle's Product manager for the tool.
• Heli from Finland - Author of an excellent book about SDDM.
• Dave Schleis - expert on SDDM scripting, especially with Groovy
• Kent Graziano - especially good at using SDDM to design Data Warehouses.
• SDDM Forum - The people listed above, other users and even SDDM's developers, especially Phillip Stoyanov answer your questions.

Using SDDM with SQL Server

So, as I said, SDDM works perfectly well with a SQL Server database. All you need to do is register a JDBC driver for it.  While Microsoft does have a JDBC driver that you can download, and it does work with SDDM's stand-alone version, SDDM also comes bundled with Oracle SQL Developer and that DOESN'T work with Microsoft's driver.  Neither does Eclipse, which I'm currently using as my primary IDE.  So instead, I'm using the free open source jTDS JDBC driver for SQL Server and Sybase (Sybase is a close cousin to SQL Server).  You can get jTDS here: http://jtds.sourceforge.net/.  Just download and unzip.  Now open SDDM and select Tools/Preferences and find "Third Party JDBC Drivers":

Or the steps are similar if you are using SDDM embedded in SQL Developer.  Now when you configure a connection to a database, you will have an additional tab for a connection type of SQL Server:

Fill in the blanks, and off you go: you can now import from the SQL Server Database Dictionary to reverse engineer your database into a Relational Design.  Or if you are using SDDM embedded in SQL Developer, you can open a connection to your SQL Server database in SQL Developer, and do many of the normal SQL Developer tasks.  Just one warning - SQL Developer doesn't really understand T-SQL, Microsoft's counterpart to both SQL*Plus style scripting and PL/SQL.  So there are limitations to what you can do in the SQL Worksheet.  But one thing you CAN do is open a relational diagram in SDDM and drag objects onto it from the SQL Server connection in SQL Developer.

Reporting

SDDM has a pretty good reporting capability and I'm only beginning to learn its full capabilities.  But if you want to write SQL queries or reports against your data model, you will want to export your model to a Reporting Repository.  The Reporting Repository needs to be in an Oracle database - sorry.  This is why I asked my system administrator to set up a server with an Oracle Express Edition (Oracle XE) database, just for my private use for SDDM reports.  Like SDDM, Oracle XE is free to use, and while limited, it has no trouble hosting an SDDM reporting repository.

SQL Server DDL

You can also create a Physical Database design for SQL Server and SDDM will create DDL appropriate for your database - it understands the differences.  Only problem I've had with it, is that I usually need to manually edit the output a bit.  The worst problem is that the "GO" command needs to appear on a separate line and sometimes the newline is missing.  Also, SDDM tends to output UNIX/Linux style newlines, not the standard carriage return/linefeeds expected by Windows.  There is a little bit of a problem between the Schema in Relational Design and its implementation in Physical design - Physical doesn't always pick up the mapping you specified. But this may be something I'm doing wrong.

So - if Oracle isn't a dirty word for you SQL Server database architects, you may want to check out Oracle SQL Developer Data Modeler.  It is a great, cost-free way to design your data model.  I plan to write another post soon to talk about some SDDM scripting in Dave Schleis's favorite language: Groovy.