DB Files reversed on deployment

Question

Hello,

I've been using VS2008 with the latest GDR R2 to create a SQL 2008 database.  I've been testing deployment and have noticed an issue.  I have 2 file groups (Primary and Staging). So under Schema Objects\Database Level Objects\Storate\Files I have 5 files:
log.sqlfile.sql
Primary_1a.sqlfile.sql
Primary_1b.sqlfile.sql
Staging_1a.sqlfile.sql
Staging_1b.sqlfile.sql


Primary_1a.sqlfile.sql code: 

-- Do not change the database name.
-- It will be properly coded for build and deployment
-- This is using sqlcmd variable substitution
ALTER DATABASE [$(DatabaseName)]
ADD FILE 
(
NAME = [$(DatabaseName)_1a], 
FILENAME = '$(DefaultDataPath)$(DatabaseName)_1a.mdf', 
SIZE = 6500 MB, 
MAXSIZE = UNLIMITED, 
FILEGROWTH = 5 MB
) 
TO FILEGROUP [PRIMARY];

 

 

 

 

Primary_1b.sqlfile.sql code:

 

-- Do not change the database name.
-- It will be properly coded for build and deployment
-- This is using sqlcmd variable substitution
ALTER DATABASE [$(DatabaseName)]
ADD FILE 
(
NAME = [$(DatabaseName)_1b], 
FILENAME = '$(DefaultDataPath)$(DatabaseName)_1b.ndf', 
SIZE = 6500 MB, 
MAXSIZE = UNLIMITED, 
FILEGROWTH = 5 MB
) 
TO FILEGROUP [PRIMARY];

 

 

The problem occurs when I deploy, the first file is actually the Primary_1b.ndf not Primary_1a.mdf.  I discovered this when I did a detach database and then tried to attach the database again, it gave me an error that Primary_1a.mdf was not the correct file while Primary_1b.ndf was.  I did a new deployment straight from VS2008 and this occurs everytime, but I do see any option to set it.

Any ideas?

Thanks,

--Aaron

Answer 1

The order of creation is inside the deployment script, can you look in your sql\debug\<project name>.sql file and see what the ordering and commands are in the deployment file. Please post that piece.

---

GertD @ www.DBProj.com

Answer 2

I am already able to repro this, the result is:

CREATE DATABASE [$(DatabaseName)]
    ON 
    PRIMARY(NAME = [$(DatabaseName)_1b], FILENAME = '$(DefaultDataPath)$(DatabaseName)_1b.ndf', MAXSIZE = UNLIMITED, FILEGROWTH = 5 MB), PRIMARY(NAME = [$(DatabaseName)_1a], FILENAME = '$(DefaultDataPath)$(DatabaseName)_1a.mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 5 MB) COLLATE SQL_Latin1_General_CP1_CS_AS
GO

I am not sure why the order is picked this way. As far as I know this does not change the way the database file work, they are both part of the PRIMARY filegroup, am I missing something?

SSMS displays based on file_id:

1	23B0932A-D9C2-4A16-8C00-F45AC2A93278	0	ROWS	1	gftest_1b	   d:\dbs\MSSQL10.MSSQLSERVER\MSSQL\DATA\gftest_1b.ndf
2	BF1947BF-A5E8-458C-882B-A487290FFC40	1	LOG	0	gftest_log  d:\dbs\MSSQL10.MSSQLSERVER\MSSQL\DATA\gftest_log.LDF
3	AF640BBE-8C67-4028-B0F9-5D5F055B57F5	0	ROWS	1	gftest_1a	   d:\dbs\MSSQL10.MSSQLSERVER\MSSQL\DATA\gftest_1a.mdf

---

GertD @ www.DBProj.com

Answer 3

Essentially there is no issue from a functional standpoint (i.e., it works properly and they are both part of the primary filegroup) and yes I understand that names and extensions don't matter when it comes to SQL Server databases. 

My concern was more along the lines of some future time when someone gets confused and wants to "talk" to me. :) 

But if there's no workaround/fix other than manually changing the names after deployment via detach/attach, then it's not a huge deal, but thought I'd see.

Thanks,

--Aaron

Answer 4

The developer off this provided the logic and reasoning behind this.

They choose the largest file first, this was because of a bug where if they choose the smallest one the content of model would not fit on a new database creation.

Sure enough when I use MDF size 11 and NDF of 10MB for example I get what you expected.

ALTER DATABASE [$(DatabaseName)]
ADD FILE 
(
NAME = [$(DatabaseName)_1a], 
FILENAME = '$(DefaultDataPath)$(DatabaseName)_1a.mdf', 
SIZE = 11 MB, 
MAXSIZE = UNLIMITED, 
FILEGROWTH = 5 MB
) 

ALTER DATABASE [$(DatabaseName)]
ADD FILE 
(
NAME = [$(DatabaseName)_1b], 
FILENAME = '$(DefaultDataPath)$(DatabaseName)_1b.ndf', 
SIZE = 10 MB, 
MAXSIZE = UNLIMITED, 
FILEGROWTH = 5 MB
) 

CREATE DATABASE [$(DatabaseName)]
    ON 
    PRIMARY(NAME = [$(DatabaseName)_1a], FILENAME = '$(DefaultDataPath)$(DatabaseName)_1a.mdf', SIZE = 11 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5 MB), PRIMARY(NAME = [$(DatabaseName)_1b], FILENAME = '$(DefaultDataPath)$(DatabaseName)_1b.ndf', SIZE = 10 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5 MB) COLLATE SQL_Latin1_General_CP1_CS_AS
GO

At least we know it was expected.

---

GertD @ www.DBProj.com

Answer 5

That makes sense, however in my example above and your repro the files were identical in size so I wonder why it would be consistently choosiing the second file.  However now I know there's a workaround at least. :) 

Thanks,

--Aaron

Answer 6

If you look closely the size is 11 and 10 MB, they are not the same.

---

GertD @ www.DBProj.com