Best way to move database data files to the same size

Question

Best way to move database data files to the same size

nsunderman

SSC Enthusiast

Points: 103
More actions
May 22, 2018 at 3:40 pm

#346229

I have inherited an approximately 800GB SQL 2016 database (recently migrated) with 35 data files ranging in size from 150GB to 3GB, all in 7 different file groups. Because of this layout of data files, there are hot spots on the data files and the hyper-converged storage infrastructure can't keep up with the I/O requests. This database is not using SQL compression but TDE is being used on this database.
What I would like to do is refactor this database to 24 data files of the same size, all in the same file group with SQL page level compression enabled, to comply with the hyper-converged vendor's best practices. I know there are identity columns, foreign keys to take into account and other interesting impacts of doing this.
My question is what is the best way to migrate the data from the existing database to a new database?
Import/Export?
Generate Scripts?
Select * from old DB to new one?
Something else I am not thinking of?
Thanks in advance for any suggestions and comments.
-n
May 23, 2018 at 12:38 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 1

Rebuild indexes on the new partition schema? https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-2017

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

nsunderman - Tuesday, May 22, 2018 3:40 PM
I have inherited an approximately 800GB SQL 2016 database (recently migrated) with 35 data files ranging in size from 150GB to 3GB, all in 7 different file groups. Because of this layout of data files, there are hot spots on the data files and the hyper-converged storage infrastructure can't keep up with the I/O requests. This database is not using SQL compression but TDE is being used on this database.
What I would like to do is refactor this database to 24 data files of the same size, all in the same file group with SQL page level compression enabled, to comply with the hyper-converged vendor's best practices. I know there are identity columns, foreign keys to take into account and other interesting impacts of doing this.
My question is what is the best way to migrate the data from the existing database to a new database?
Import/Export?
Generate Scripts?
Select * from old DB to new one?
Something else I am not thinking of?
Thanks in advance for any suggestions and comments.
-n

What are the key columns involved on the hotspots?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

goher2000 SSCertifiable Points: 5479 More actions · Answer 3

why only page compression, where is compression best practice ?

nsunderman SSC Enthusiast Points: 103 More actions · Answer 4

What are the key columns involved on the hotspots?

--Jeff Moden

* I am not sure what key columns are involved on the hotspots at this point, only which files are generating the bulk of the I/Os. I will search up some info on this and let you know what I find out.

nsunderman SSC Enthusiast Points: 103 More actions · Answer 5

why only page compression, where is compression best practice ?

In section 7.15 of the Nutanix Best Practices Microsoft SQL Server 2014 it recommends:
* Carefully evaluate using SQL compression for all databases
* Look at using the page-level compression option

-n