May 21, 2015 at 2:42 pm
I’m involved in an application where a database is to be migrated over to a new server.
I have been told that numerous applications each today with their own database are to be migrated to a common database thereby requiring that all table/object names be prefixed with a unique name for each application. My application tables will app be prefixed with AppName_ + existing table name. This is to be shared with 30 other applications in the same database.
I understand the security login will be set up to automatically limit to just those database objects with the same prefix
This is to be done using the latest SQL Server version
My question is should I be concerned with this approach
Can backups and restores occur within a selected prefixed set of tables.
Can one tune with the same kind of controls?
Is there additional maintenance in releasing new changes requiring an additional security step or can this be automated to have access to similarly prefixed tables
Are there other SQL Server issues which I should be concerned?
Do you know of a documented case which lists these pros/cons ?
I'm trying to argue against this move, and need to limit to just database design reasons
Thanks
May 21, 2015 at 3:45 pm
I have been told that numerous applications each today with their own database are to be migrated to a common database thereby requiring that all table/object names be prefixed with a unique name for each application.
That sounds like a horrific idea for numerous reasons; I don't even know where to start.
The first question when somebody comes up with an idea like this is, "what problem is doing this going to solve?" There is no benefit that I can think of in doing this. Now for some of the con's for which there are more than I have time for:
1. Backing up/restoring will become more complicated and tedious. When backing up/restoring transaction logs you now get to back up and restore transactions for all 30+ applications.
2. You can't have different transaction log backup schemes for different applications.
3. It will be much harder to determine what application is blowing up your data or log files.
4. One application causes database corruption then guess what application is now attached to a corrupt database? All of them.
5. Will changing the names of the tables in the way you described not break one of the 30+ applications? Ask yourself that question and see what your gut says.
6. It will be harder to secure the applications.
This is to be done using the latest SQL Server version
7. And all 30 applications have been tested against the latest version of SQL Server? Which cumulative update? :hehe:
Don't do it. Don't let them do it. Update your resume/CV in case they decide to do it. That's my suggestion.
-- Itzik Ben-Gan 2001
May 21, 2015 at 4:53 pm
I agree with all that Alan B says about this idea, except that I don't think "horrific" is realluy strong enough to be accurate. It is a downright stupid idea. In the unlikely event that the initial transition to this lunatic scheme doesn't result in utter catastrophe, you will be stuck with a system which is has all the faults that Alan describes. So all the applications have the same backup, recovery, and disaster recovery requirements? If not, merging their databases is pure insanity. Would you really be happy if something in one application goes wrong in such a way as to seriously corrupt the database causes all the applications to stop working? If loss of one disc page (to hardware failure) causes all applications to cease functioning, instead of just one? And so on, and so forth.
Unless there's something about this which isn't visible to us that mitigates the risks here, Alan's advice to get your resumé up to date if you can't persuade TPTB to abandon this is eminently sensible if you can't persuade them not to do it. If the company goes bust because it wrecks its IT capability you'll be looking for a job, and you would be better off looking before the collapse than after (when your prospects would be tainted by association with the failure).
Tom
May 21, 2015 at 5:57 pm
Alan.B (5/21/2015)
I have been told that numerous applications each today with their own database are to be migrated to a common database thereby requiring that all table/object names be prefixed with a unique name for each application.
That sounds like a horrific idea for numerous reasons...
Don't do it. Don't let them do it. Update your resume/CV in case they decide to do it. That's my suggestion.
I would have to agree. Why not let all of the applications continue to be held in separate databases but on the same instance?
Or do they ascribe to the same definition of database as being the instance instead of a database within that instance?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 21, 2015 at 6:02 pm
There are lots and lots of issues here, but speaking from personal experience, let me tell you what the #1 biggest problem is going to be.
You're going to have X number of development teams with completely different requirements and completely different test and release cycles all attempting to get their code out the door. Meanwhile each of the teams is stepping on the other, referring to one of their tables or stored procs or views which then can't be touched without negotiations that would make the Portsmouth Treaty ending the Russo-Japanese War look like child's play.
And yes, there are all sorts of performance issues, security concerns (tons of security concerns) and a whole bunch of other problems to boot.
I'd need a massively huge justification for doing something like this and even then I think I'd fight it.
Trust me, you don't want to do this. I'm saying this as someone who made the mistake of sticking five distinct applications into a single database, on purpose. Huge mistake.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2015 at 12:44 am
Have to agree with every one else here.
In short: No, just no
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
May 22, 2015 at 1:30 am
Sounds for me like some one is coming from oracle...
Sorry have to explain...
I often have the problem with wording if i speak to a solution architect who is more comfortable with oracle than ms sql.
Here they they always use the word database instead of instance and schema instead of database...
if this is not the case... Don't do something like this!
May 22, 2015 at 1:49 am
Quick question, is there a dinosaur in the decision making team, haven't seen this kind of architecture since AS/400?
😎
May 22, 2015 at 4:37 am
The first question to ask them is "Have all the devs agreed to change their code so that every last function, proc, view, job, and ad hoc query will go to the proper new tables?"
If not, then everything will break the instant you transfer over to this new schema.
That being said... Yes, you could do backups for individual subsets of tables, assuming you put each applications schema on a different file. But as has been mentioned, backups and restores will be infinitely more complicated.
Also, ditto on verifying if they are talking about the same db or just the same server / instance.
I've actually worked in an environment that already had this sort of thing set up. It was more of a "same app, 30+ different clients" setup, though, as opposed to 30 different applications. I was a on a 6 month consultant contract to set up replication because this place didn't have a real DBA. The Dev manager did all the DBA work before I got there and after I left.
From experience I can tell you that IF (notice the capital letters) they get it set up right, security is going to be a major headache. I cannot tell you the number of times that clients accidentally got access to another client's data because something the devs did referenced the wrong table or because someone gave the wrong security access to a client login.
So, depending on your business, this whole setup not only could be a maintenance headache, but think of all the SOX / HIPAA / PII nightmares just waiting in the wings to swoop down and cost your business millions of dollars.
Put a dollar figure on those security issues. Take that to your boss. Compare the cost savings of consolidation to the cost of fines and lawsuits and the business's reputation / brand destruction. I bet (s)he'll sit up and take notice if you can cite examples of how this can torpedo the business.
May 22, 2015 at 5:18 am
Brandie Tarvin (5/22/2015)
That being said... Yes, you could do backups for individual subsets of tables, assuming you put each applications schema on a different file. But as has been mentioned, backups and restores will be infinitely more complicated.
You can do *backups* of subsets of tables. You cannot however do restores of subsets of tables.
A filegroup restore has to be followed by log restores to bring the newly restored filegroup back up to the same point in time as the rest of the database.
Hence, if all apps are in the same database, the question "Can you restore App3's data to yesterday 4pm as there was a mistake in the overnight processing" has the answer "No."
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2015 at 5:48 am
GilaMonster (5/22/2015)
Brandie Tarvin (5/22/2015)
That being said... Yes, you could do backups for individual subsets of tables, assuming you put each applications schema on a different file. But as has been mentioned, backups and restores will be infinitely more complicated.You can do *backups* of subsets of tables. You cannot however do restores of subsets of tables.
A filegroup restore has to be followed by log restores to bring the newly restored filegroup back up to the same point in time as the rest of the database.
Hence, if all apps are in the same database, the question "Can you restore App3's data to yesterday 4pm as there was a mistake in the overnight processing" has the answer "No."
All good points. Thank you for the clarification.
May 22, 2015 at 6:55 am
Thanks so much for all your all the responses. Very informative and now have some detail reasons/ concerns to pose. The migration effort is not an argument unfortunately because modifications will be mostly automated with find/replace effort. Retesting of course could be significant. However as an application developer I was lacking some of the details surrounding the database concerns. Thanks so much to all
May 22, 2015 at 7:14 am
mrl6254 (5/21/2015)
I’m involved in an application where a database is to be migrated over to a new server.I have been told that numerous applications each today with their own database are to be migrated to a common database thereby requiring that all table/object names be prefixed with a unique name for each application. My application tables will app be prefixed with AppName_ + existing table name. This is to be shared with 30 other applications in the same database.
...
My question is should I be concerned with this approach?
...
What you described above is so colossally stupid from a design perspective; you should really be concerned about your oganization's viability going forward and your job. That's what's really at stake here.
Everything the database architect is planning to do is wrong. Multiple application databases can be consolidated on one SQL Server instance; no problem. If you have a few related applications that you want consolidated on one common database, then the approach should be one schema for each application, perhaps with connom reference tables contained in a separate schema. Security should be implmented using roles and object permissions.
I'm glad that a smart SQLServerCentral member like yourself would recognize the potential disaster about to happen.
I'm curious, where did the architect of this plan these ideas?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 22, 2015 at 7:39 am
Other than reducing the number of databases to manage, I cannot see any benefits.
This would mean fewer databases to manage, but this database could, if not would, be a nightmare to manage.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply