Ah, much better format than the old Daily News. Thanks Chris.

Has the Daily News, or whatever the thing that was at the bottom of the homepage with links to other sites with tech-ish news, gone away? Has it moved?

I know I'm asking a lot and you should be paid for this kind of info. So thanks again. Don't get me wrong, if it were up to me I would more than follow your guidance. The only issue here is that I'm going to be a mouthpiece for your ideas and I don't have much standing in the company. I'm a new hire. Given that, what concrete examples/reasons can I give my PM to convince him of the folly of the current approach and the correctness of the 'move data to SQL Server and then do translation into the new schema' strategy?

Excellent. How do I convince my PM that this is the way to go? What rationale should be used? (My PM has managed enterprise type data migration but only with SSIS/tools. I think some of the other developers managed the offshore team and either guided them to the scrub in code scheme or at least allowed it.) Keep in mind this, scrub/translate in SQL Server, might be a very hard sell as I'm not experienced in writing SQL scripts, and have written C++ and C# code for 10+ yrs. Thanks so much for the information. p.s. There are a number of files which make up the ISAM database.

The customer owns the data in real terms. The data is only supposed to be changed by the older version of the application so it’s not as if it is an open database where others are allowed to change it. (On the 1000s of 'databases', my PM, thank goodness, understands that this is not the right way to go and will 'force' the developers to use an extra DB table so there will be just one DB.) On the mandate issue, I know the new application is going to go to SQL Server no matter what. Well, unless the entire scheme is completely unworkable and there is a vast amount of evidence to show the critical problems. If this is true, I can probably kiss my job goodbye. No this won't be just an interface; it’s supposed to literally be a scrub, translation, write data to disk and then upload with the tool to the new SQL Server DB. Scrubing and translating data makes me nervous - if I were a customer and I knew this was happening, I would have serious reservations. What’s the yikes factor given the information above? High I’m guessing :-O .

No one ever lost a fortune by underestimating the intelligence of the American public.

Thanks for the info – that seems like a sane way to go. Of course, things are a little more involved as we don’t have the actual data that will be fed to the tool. The tool is supposed to run on customer data, possibly 1000’s of customers with 10-100,000 ‘databases’ each. ‘database’ == ISAM database. The company of course has sample ‘databases’ and can create ‘databases’ but it does not have the actual data that this tool will be fed. Using a script seems like a much better way to go as the script could be modified by a DB admin at the customer site whereas doing it in code means rewriting/refactoring and recompiling (well unless they could somehow jam the change into the XML file – yeah, I know doubtful). I’m naïve about SQL scripting, how would one go about actually stuffing the ISAM data into the new SQL server tables? I’ve only ever written C# code to call stored procs. Is this what you mean? The company already tried to have this project done offshore with terrible results (they completely ignored memory considerations). Unfortunately, I’m not sure I would be able to convince them to go the script way as they seem to want this done in code. Hopefully this all makes some sort of sense – I’m not even close to being DB savvy.

Thanks for the info. Are there any online articles, etc. that might be helpful for me to read? Since you have done this many times, is there any wisdom you might impart? This will be my first time. What things should I expect to run into other than memory issues? Unfortunately, the data is coming in as a DataSet. Is that even reasonable? I’ve been told that DataSet is space inefficient. Are there other problems with DataSet to watch out for? My idea was to work with the data in place: 1. Scrub the data so that it passes the value constraints of the new schema (rules come from an XML file) 2. Rename tables and columns 3. Create new tables, filling them with columns and rows of data (not sure this will even work - maybe have to create a new set and move data into it) 4. Fix up foreign key constraints 5. Either drop data for rows that have duplicate primary keys or somehow, make them unique. Both of these options seem very expensive time-wise. 6. Binary serialize the data to a file For the upload, I was going to use the BulkCopy class. I considered just writing out table data in a format that bcp.exe could understand but bcp.exe only allows 263 rows. (Wow, that limit is weird, http://msdn.microsoft.com/en-us/library/ms162802.aspx).

I know it is possible to use SSIS and other tools to translate data from one schema to another but does anyone know of any well-known patterns or code examples of doing this? One wrinkle to the problem is that the original constraints, foreign keys, unique keys, etc., were not enforced at the database level and so translation has to consider this. In other words, fix things up as best as possible by doing things like inserting rows into parent tables when foreign keys are missing. The data in question isn't very large; a max of around 200MB. The spec would like the solution to be in two phases, one to translate into the new schema with data written out to the file system and another phase where the data is loaded into the new database, MS SQL Server. Unless there is a free tool that can do this and do it well, the solution will need to be hand coded. Someone had already looked at using a mapping tool with poor results.

Imagine an app that where modification of values in the GUI are immediately updated to a SQL database. Given this scenario, would you still open and dispose of a SqlConnection object for each update? My guess would be yes because the real connection to the database should be managed via connection pools. Any rationale for either tactic would be greatly appreciated.

Exactly, 'technically possible' but a horrendous idea. I'm guessing this is how development 'convinced' the product owner. Any good web pages to convince them that this is a ridiculous idea?

Good options, though I'm pretty sure they are going to use SQL Server no matter what. The customers of the product are typically Microsoft shops so they really want SQL Server. I just can't imagine how the developers convinced the product owner that creating tens of thousands of databases would be okay.

Thanks for the link to the great article i.j.russell, I'm still reading and learning. Ah, I see, my description of the question wasn't very good. I should expand a bit on what I mean by profile. A better description would be an item where multiple, think thousands or tens of thousands, of these items are associated with a single tenant. The new software being developed copied a lot of code from the old version where an item, single file, was represented as a database using an API provided by an antiquated database product. The new software is trying to use this same scheme in SQL Server, one database per item. Since there can be tens of thousands of items, there will be tens of thousands of databases. I can hear the murderous laughter of database administrators everywhere. I think this scheme breaks a number of well-established guidelines but I’d like to give the product owner ammunition, concrete definitive documentation, to force the developers to change the product to something more reasonable. (Oh, and I’m one of the developers – just showing up late in the development cycle of the product.) The product owner is not very technical and so may have been convinced for some reason or other that this was an okay solution.

Would anyone happen to know of any public web pages or documents, by respected authorities in the field that would definitively convince a product owner that creating tens of thousands of databases in a single SQL Server is not a reasonable thing to do? In addition, having Joe-blow users creating and deleting databases is not a good idea, i.e. these are administrator only tasks. The product in question is an update of a previous product where there was a single ‘database’ per profile. Only in the old version, the ‘database’ was just a single file with no constraint checking.