April 3, 2018 at 2:22 am
Hi Experts,
We are moving objects of a particular schema to a new database. What all things need to check while moving objects.
I have observed that we have some tables with FK referring other schema tables. What needs to be done for this?
Application code is referring to other schema objects ,this can be resolved by specifying the new dbname.
April 3, 2018 at 2:42 am
You can't do cross-DB referential integrity, so just have to hope the app is written correctly (or use triggers, but they have problems too)
Why is this being done? If you're hardcoding DB names in queries, then it won't be possible to move either DB to a different server.
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
April 3, 2018 at 4:15 am
GilaMonster - Tuesday, April 3, 2018 2:42 AMYou can't do cross-DB referential integrity, so just have to hope the app is written correctly (or use triggers, but they have problems too)Why is this being done? If you're hardcoding DB names in queries, then it won't be possible to move either DB to a different server.
Thanks Gail.
Yes cross referential integrity wont work.
This is done as the app related to that particular schema is storing files and can store N number of files which security team find it as a threat for other applications using that database.
Can you please let me know why the hardcoding with make it impossible to move the databases to different server? If it be an issue if we use same database name in another server as well?
April 3, 2018 at 5:07 am
VastSQL - Tuesday, April 3, 2018 4:15 AMCan you please let me know why the hardcoding with make it impossible to move the databases to different server? If it be an issue if we use same database name in another server as well?
Because if your connection string points at Server1 and your queries connecting to Server1 reference DB2.dbo.SomeTable, and DB2 is on Server2, you'll get an error.
Why are stored files a threat to the applications that don't use them?
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
April 3, 2018 at 5:35 am
GilaMonster - Tuesday, April 3, 2018 5:07 AMVastSQL - Tuesday, April 3, 2018 4:15 AMCan you please let me know why the hardcoding with make it impossible to move the databases to different server? If it be an issue if we use same database name in another server as well?Because if your connection string points at Server1 and your queries connecting to Server1 reference DB2.dbo.SomeTable, and DB2 is on Server2, you'll get an error.
Why are stored files a threat to the applications that don't use them?
Security team feels the file growth can be a problem for other applications as all are sharing the same disk. The team is working on to move the files to FTP that way the concern will be eliminated, but when thats going to happen is a big Question Mark.
April 3, 2018 at 5:49 am
You can put the tables with the file in specific filegroups and move the files in those filegroups to a different drive, or set limits on file sizes.
Growth of data is not a good reason to split databases.
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
April 3, 2018 at 6:33 am
GilaMonster - Tuesday, April 3, 2018 5:49 AMYou can put the tables with the file in specific filegroups and move the files in those filegroups to a different drive, or set limits on file sizes.
Growth of data is not a good reason to split databases.
Thanks Gail.
Another disadvantage of all those object if not using FTP is that a user can upload N number of files and there is no restriction for that. They are already using FTP to keep some huge files.
Have already shared the option you shared.
April 3, 2018 at 6:54 am
VastSQL - Tuesday, April 3, 2018 6:33 AMGilaMonster - Tuesday, April 3, 2018 5:49 AMYou can put the tables with the file in specific filegroups and move the files in those filegroups to a different drive, or set limits on file sizes.
Growth of data is not a good reason to split databases.Thanks Gail.
Another disadvantage of all those object if not using FTP is that a user can upload N number of files and there is no restriction for that.
And how is a different database on the same instance going to fix that?
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
April 3, 2018 at 11:20 pm
GilaMonster - Tuesday, April 3, 2018 6:54 AMVastSQL - Tuesday, April 3, 2018 6:33 AMGilaMonster - Tuesday, April 3, 2018 5:49 AMYou can put the tables with the file in specific filegroups and move the files in those filegroups to a different drive, or set limits on file sizes.
Growth of data is not a good reason to split databases.Thanks Gail.
Another disadvantage of all those object if not using FTP is that a user can upload N number of files and there is no restriction for that.
And how is a different database on the same instance going to fix that?
Thats correct only if the database is in different drive make sense. But by moving to another DB we can at least restrict the files of that database.
April 4, 2018 at 1:53 am
VastSQL - Tuesday, April 3, 2018 11:20 PMGilaMonster - Tuesday, April 3, 2018 6:54 AMVastSQL - Tuesday, April 3, 2018 6:33 AMGilaMonster - Tuesday, April 3, 2018 5:49 AMYou can put the tables with the file in specific filegroups and move the files in those filegroups to a different drive, or set limits on file sizes.
Growth of data is not a good reason to split databases.Thanks Gail.
Another disadvantage of all those object if not using FTP is that a user can upload N number of files and there is no restriction for that.
And how is a different database on the same instance going to fix that?
Thats correct only if the database is in different drive make sense. But by moving to another DB we can at least restrict the files of that database.
By moving the tables with the files to a different filegroup you get exactly the same without the drawbacks of losing referential integrity.
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
April 4, 2018 at 2:42 am
GilaMonster - Wednesday, April 4, 2018 1:53 AMVastSQL - Tuesday, April 3, 2018 11:20 PMGilaMonster - Tuesday, April 3, 2018 6:54 AMVastSQL - Tuesday, April 3, 2018 6:33 AMGilaMonster - Tuesday, April 3, 2018 5:49 AMYou can put the tables with the file in specific filegroups and move the files in those filegroups to a different drive, or set limits on file sizes.
Growth of data is not a good reason to split databases.Thanks Gail.
Another disadvantage of all those object if not using FTP is that a user can upload N number of files and there is no restriction for that.
And how is a different database on the same instance going to fix that?
Thats correct only if the database is in different drive make sense. But by moving to another DB we can at least restrict the files of that database.
By moving the tables with the files to a different filegroup you get exactly the same without the drawbacks of losing referential integrity.
Agree with you but we need to recreate all the Clustered Indexes in this particular schema objects to a new filegroup right?
April 4, 2018 at 6:01 am
VastSQL - Wednesday, April 4, 2018 2:42 AMGilaMonster - Wednesday, April 4, 2018 1:53 AMVastSQL - Tuesday, April 3, 2018 11:20 PMGilaMonster - Tuesday, April 3, 2018 6:54 AMVastSQL - Tuesday, April 3, 2018 6:33 AMGilaMonster - Tuesday, April 3, 2018 5:49 AMYou can put the tables with the file in specific filegroups and move the files in those filegroups to a different drive, or set limits on file sizes.
Growth of data is not a good reason to split databases.Thanks Gail.
Another disadvantage of all those object if not using FTP is that a user can upload N number of files and there is no restriction for that.
And how is a different database on the same instance going to fix that?
Thats correct only if the database is in different drive make sense. But by moving to another DB we can at least restrict the files of that database.
By moving the tables with the files to a different filegroup you get exactly the same without the drawbacks of losing referential integrity.
Agree with you but we need to recreate all the Clustered Indexes in this particular schema objects to a new filegroup right?
If they're LOB columns, that won't work, the lob pages don't get moved when the clustered index moves. Easiest would be to create new tables with the TEXTIMAGE_ON option to specify what filegroup the LOB columns go onto.
Then you'll need to copy the data over, and create the constraints and indexes
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
April 4, 2018 at 11:02 pm
GilaMonster - Wednesday, April 4, 2018 6:01 AMVastSQL - Wednesday, April 4, 2018 2:42 AMGilaMonster - Wednesday, April 4, 2018 1:53 AMVastSQL - Tuesday, April 3, 2018 11:20 PMGilaMonster - Tuesday, April 3, 2018 6:54 AMVastSQL - Tuesday, April 3, 2018 6:33 AMGilaMonster - Tuesday, April 3, 2018 5:49 AMYou can put the tables with the file in specific filegroups and move the files in those filegroups to a different drive, or set limits on file sizes.
Growth of data is not a good reason to split databases.Thanks Gail.
Another disadvantage of all those object if not using FTP is that a user can upload N number of files and there is no restriction for that.
And how is a different database on the same instance going to fix that?
Thats correct only if the database is in different drive make sense. But by moving to another DB we can at least restrict the files of that database.
By moving the tables with the files to a different filegroup you get exactly the same without the drawbacks of losing referential integrity.
Agree with you but we need to recreate all the Clustered Indexes in this particular schema objects to a new filegroup right?
If they're LOB columns, that won't work, the lob pages don't get moved when the clustered index moves. Easiest would be to create new tables with
the TEXTIMAGE_ONoption to specify what filegroup the LOB columns go onto.
Then you'll need to copy the data over, and create the constraints and indexes
Thanks a lot Gail.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply