September 20, 2011 at 10:09 am
I'm a database/BI/data warehouse architect but not a DBA...
I'm working on a data model for a federated clinical data warehouse that is fed by more than 29 SQL server databases containing more than 3800 tables capturing data from over 100 heterogeneous databases. There is no documentation, and very little "tribal history", so I'm on my own here.
Here's the problem: When I attempt to reverse-engineer this system with either ER/Studio or Oracle SQL Developer Data Modeler, I am successful at loading the table defs, domains and RI relationships into either product, but only for some of the databases. Neither product is able to access the metadata for 13 of the databases. The problem databases seem to have multiple users/security roles. These are copies of live databases, so I've set them all to simple logging, SQL Server 2000 compatibility, and read-only. There's nothing esoteric going on like replication etc. I did not rebuild indices. Removing all security is certainly an option, since this is a staging system meant to feed the data warehouse, and will never be accessed by end users.
I have the SA account/password, am able to fully access all objects and data in these these databases from MS SQL Server Management Studio, and have been able to export all data and XML formats from all tables and all databases using the BCP utility. I can import all the data into a newly-created database, even to a different RDBMS, if necessary. I am also able to generate DDL scripts to create these databases using the Right click on database -> choose Tasks -> choose Generate Scripts approach in MS SQL Server Management Studio.
For my database architecture work, I use an old version of ER/Studio -- v5.1 running under a VirtualBox implementation of Windows 2000 Pro on Windows 7 Ultimate 64-bit (I paid for ER/Studio once back in 2002 and don't see why I should pony up $2K/year for maintenance fees). I don't think that's the problem, as I installed JDBC and Oracle SQL Developer Data Modeler v3.0 and ran into the very same reverse engineering issues.
So what could the problem be?
I suspect there's some sort of SQL Server security issue at work here.
Any help would be really appreciated.
Thanks in advance.
September 20, 2011 at 1:01 pm
Doc Brian (9/20/2011)
For my database architecture work, I use an old version of ER/Studio -- v5.1 running under a VirtualBox implementation of Windows 2000 Pro on Windows 7 Ultimate 64-bit (I paid for ER/Studio once back in 2002 and don't see why I should pony up $2K/year for maintenance fees). I don't think that's the problem, as I installed JDBC and Oracle SQL Developer Data Modeler v3.0 and ran into the very same reverse engineering issues.
Perhaps this would be a good reason to have paid the maintenance fee -- you'd have paid support. 😀
Snarky answer aside. Did you run the tool as sa on the dbs that are causing problems?
September 20, 2011 at 2:09 pm
are these databases that can't be accessed sql 2005 - 2008 ?
and your tool that was created before then can't understand the format of the system tables.
September 20, 2011 at 2:17 pm
Also - keep in mind that switching the compatibility back to 2000 doesn't turn the DB into a SQL 2000 DB. If you have new data types in place (i.e. data types that didn't exist in 2000), etc..., and your tool will only understand 2000 data types, you will get some kind of error.
Same thing will unfortunately happen if it's reverse engineering by directly reading the contents of the DB file itself (which are in 2008 format).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 20, 2011 at 3:07 pm
Yes, I accessed the meta data from the sa account.
September 20, 2011 at 3:10 pm
These are databases that were migrated to SQL Server 2008 R2 from SQL Server 2000, some from SQL Server 7.0.
September 20, 2011 at 3:32 pm
Hi,
Don't want to steal your time, and if your sure that it is just an issue with access rights, please feel free to ignore the following.
If you feel however that the problem may be related to the modeling tools that you mentioned, please do not hesitate to try SILVERRUN RDM. You can download the tool at http://www.silverrun.com. It is fully functional, so you will see immediately, if it works for you. However, you will not be able to save the result for larger models. Please let me know, and we will provide you a 15-days trial key.
Regards,
Axel Troike
Grandite
September 20, 2011 at 3:52 pm
as you are using 'sa' then then it shouldn't be a permission issue, I would compare the db options of one db that works with one that does not to see if there are any differences that Erwinn does not like.
you could also try a sql trace to see the commands that are being used to populate the meta-data and try to trace the problem there. though this may be a long-winded affair and it may be easier or less-painful to go for an upgrade of Erwin..
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply