Should I put my .mdf and .ldf files from my code into Visual Studio Team Services repository

Question

If I create a .sqlproj file that contains all my sql objects, why would I need to put my mdf, ldf files into VS Team Services source repository? Doesn't the .mdf contain all the data? If that is true then I probably wouldn't want to store all the data in my repo? I can always publish the database to localdb if I need to recreate the database right? What are the best practices here?

Answer 1

You would not source control the actual .MDF and .LDF files; they contain your actual data.

However, there are use cases when it is desirable to control where on disk your .MDF and .LDF file exist. In such a case, it may make sense to include a FileGroup File defining the location of your MDF/LDF files in your .sqlproj file.

For example, assume you have a large D: drive where you wish to store data, and a speedy E: drive made up of SSDs where you wish to store your indexes. From your .sqlproj Storage folder, you might:

• Add New Item > FileGroup, and create a file group called Data
• Add New Item > FileGroup, and create a file group called Index
• Add New Item > FileGroup File, and create a file called $(DatabaseName).Data.ldf
• Add New Item > FileGroup File, and create a file called $(DatabaseName).Indexes.ldf

Then, you can modify your tables to include an ON {FileGroup} clause like this:

CREATE TABLE [dbo].[MyTable] (
  MyTableID INT IDENTITY(1,1) NOT NULL,
  OtherIndexField NVARCHAR(50) NULL,
  ...
  CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ([MyTableID] ASC) 
) ON [Data];

CREATE NONCLUSTERED INDEX [IX_MyTableByOtherIndexField]
    ON [dbo].[MyTable]([OtherIndexField] ASC)
    ON [Index];