February 11, 2019 at 3:46 am
Hi,
I need to add some new tables to an existing database which will have:
1 - much more read/write operations than the current database tables;
2 - some IDs on these new tables will refer to existing IDs on existing database tables with static data;
Which is the best option to have more performance, and also keeping data integrity?
A - Create a new database with these new tables, replicating the tables with static data for PK/FK constraints, keeping the read/write operations separate from the current one (on different mdf and ldf files)
B - Add these new tables to the existing database, but in a different filegroup, keeping the read/write operations separated
Best Regards
February 11, 2019 at 11:20 am
Separate new filegroup(s) are fine, you don't really need a separate db, and you don't want to deal with the three-part-naming issues that a separate db would require.
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 11, 2019 at 2:55 pm
Agree with Scott.
February 11, 2019 at 3:28 pm
Thanks for your help!
What do you mean by 'three-part-naming issues'?
If I just add the new tables to the existing database in a different filegroup, the read/write operations will not delay operations between filegroups, even with both logging to the same ldf file?
Best Regards
February 11, 2019 at 4:29 pm
What do you mean by 'three-part-naming issues'?
If the table resides in a separate db, then you reference the table you will need to include the db name, either in a synonym or view, or in the SELECT itself, like this:
SELECT
FROM dbo.table_in_db1
INNER JOIN db2.dbo.table_in_db2 ...
The log file will not be directly affected. Logging would be less overhead in a single db than if it had to go across 2 dbs, which causes a distributed transaction.
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 11, 2019 at 4:46 pm
I see. Would be a reasonable idea to replicate these tables with static data also in the new DB, so the distributed transaction does not need to exist, and this new database would work almost completely separated?
Thanks, best regards!
February 12, 2019 at 10:25 am
Why replicate? I'm not sure what you want to do. The distributed transaction is less of an issue on the same server, with SELECT queries, and more with writes.
If you use a new filegroup, you do a few things. One is you keep the data together and consistent with backup/restore, recovery, simple transactions for changes, etc. Second, no code changes for queries. Third, you can get the same performance with a separate filegroup that you would get with a new database on the same server. Separate the IO out to different storage.
February 14, 2019 at 10:51 am
Thanks. I ended up opting for creating a new database, since the data on this database would have a different concept, even though they would have some tables and IDs in common.
Best Regards
February 14, 2019 at 10:59 am
amns - Thursday, February 14, 2019 10:51 AMThanks. I ended up opting for creating a new database, since the data on this database would have a different concept, even though they would have some tables and IDs in common.Best Regards
If the databases get too much for one server you could always then move one of them onto as seperate server to reduce the load.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply