February 16, 2021 at 12:52 pm
Hello Fellow DBA friends,
Currently I am working on a project where a system has to be migrated to our system. I just want to know whether I should go with creating a new DB schema for its objects in current database or should I create a new database altogether ? What are the specific factors we have to consider while taking the decision ?
Note: There is absolutely no connection of data between the two systems. Just some data has to be loaded from existing system to new system as snapshot during the month end process.
February 16, 2021 at 1:13 pm
Does this 'system' already have its own database? What is the reason for this migration and why are you considering throwing away the existing database?
As the data in the 'system' being migrated is unrelated to that in the existing database, I would probably keep it in a separate database.
The word 'system' means different things to different people, and that is why I have surrounded it with quotation marks – it is poorly defined.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 16, 2021 at 1:35 pm
By system here means application. We have application "A" with database "D". Now another application say "B" with its own database "DB1" on another server is getting merged with our application due to organizational strategy. Both our current application "A" and other application "B" have SQL server as databases.
Our current system/application will remain intact but this new application is coming our way as well. I was wondering if creating new database and porting it's table structures will be good or creating new schema in existing database "D". I want to see pros & cons from management, performance and maintenance perspective which option should be better.
February 16, 2021 at 1:49 pm
if the application is going to remain active (or while it remains active until its usage/data is migrated to another application) I would leave it on its own database - you do not wish to mix things and risk the issue of one application blocking/causing issues to the other.
what you need to worry is usage of the server - will the usage of one application have an impact on the usability of the other from CPU/IO point of view.
imagine this scenario
Application A on Server A - 4 cpus 8 gb ram - always maxed in CPU and with high Disk IO
Application B on Server B - 8 cpus 12 GB ram - cpu average of 20% low disk IO
if you move application/database from A to B it is likely that server B will now have a significantly higher CPU and Disk usage - this will impact the users of application B.
February 16, 2021 at 1:54 pm
... creating new database and porting it's table structures ...
Why would you even consider this, when you have BACKUP/RESTORE as the obvious alternative?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 16, 2021 at 2:06 pm
Phil, glad I asked. The other system is somewhat very poorly written so we are re engineering it while moving to our system. So lots of table structures will be redefined.
Bottom line is as of now we have to choose out of the two options and brainstorming on pros and cons of both options.
February 16, 2021 at 2:24 pm
OK, that's a good reason, thank you.
To be honest, I can see only a few pros of putting the new tables and structures into the same database as your current one:
Some cons:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 16, 2021 at 2:29 pm
One additional thought.
You can future-proof the separate database idea to a large extent by using database schemas which are different from those in your existing database.
If you do this and then decide in future that the databases should be merged, it should be a straightforward task, as there should be no collisions or overlaps between object names when merging.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 16, 2021 at 3:13 pm
To add to the idea of future-proofing, the OP might also want to consider using different ranges of IDs for related data to avoid ID collision and make merging into the same table much easier if ever desired.
February 16, 2021 at 3:14 pm
If there are existing overlapping object names, I'd resolve them -- i.e. rename one of the objects -- as part of the rework. So that, even if stored in separate dbs, the object names would not overlap.
The easiest way to do that might be to use synonyms to refer to the other db's objects. For example, on db "D", "DB1" objects would be referred to using:
CREATE SYNONYM dbo.object_abc FOR DB1.dbo.object_abc
and vice versa. This will insure that an overlapping object name cannot be created in either db.
Of course there are other methods available to do that too.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 16, 2021 at 3:35 pm
But as of now what do you think Phil ? I really want to go with new database (with new schema in new DB is good idea) but want to know the pros and cons of it. If possible, please shed some light on it.
February 16, 2021 at 5:50 pm
I can't speak for anyone else but I've found that different applications and their different requirements frequently come and go on our systems. Some even have different Recovery Models for various good reasons. Some have common data and most also have their own data. The same goes for security and related logins.
With that, we generally use a "common" database for all of the common data and give each app it's own database (to be sure, DRI can sometimes be an issue so be aware of that). If nothing else, it makes tearing down an app for discontinued use, replacement, etc, a whole lot easier.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2021 at 5:33 am
Hi
im trying to do the math on the best purchase option. Does anyone know if a client access license is simply an individual user? If i have 100 connections all with the same user (eg intranet) is this one cal?
If i buy say 20 CALs and it turns out to be too few is there an easy and fast way to buy more, and if i end up needing to need more than 30 CALs can i switch to the 2 core license ?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply