Understanding connection string syntax

Question

I am following How To Create a SQL Server Database Programmatically but find limited resources explaining the syntax is greater detail other than MSDN: Filegroup Options.

Take the following code on MSDN for example:

SqlConnection myConn = 
new SqlConnection ("Server=localhost;Integrated security=SSPI;database=master");
str = "CREATE DATABASE MyDatabase ON PRIMARY " +
 "(NAME = MyDatabase_Data, " +
 "FILENAME = 'C:\\MyDatabaseData.mdf', " +
 "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
 "LOG ON (NAME = MyDatabase_Log, " +
 "FILENAME = 'C:\\MyDatabaseLog.ldf', " +
 "SIZE = 1MB, " +
 "MAXSIZE = 5MB, " +
 "FILEGROWTH = 10%)"

Questions:

1. Back in SQL Server 2012, I would have in code Server=localhost\SQLExpress where Configuration Manager would show the instance as SQL Server (SQLExpress). Now with SQL Server 2016, I do not understand why it is simply Server=localhost because in Configuration Manager there is SQL Server (MSSQLServer) and I thought the syntax should be Server=localhost\MSSQLServer instead. Is it related to the Client Protocol? (but the Protocol did not differentiate the two version of SQL Server.
2. What is the difference between Database's name (MyDatabase) and NAME (MyDatabase_Data)? Why the need to assign a different name to a database which already has a name. What is the purpose?
3. How does the file grow in FILEGROWTH? Does it grow per SQL query? (For example I am now allowed to inserting large enough of textual data exceeding 1MB (10% of MAXSIZE) in one query) Or, does it mean the rate of growth (1MB per second)?

Answer 1

1. The reason that localhost is acceptable would be because it is using a default instance of SQL Server. The default instance, by name, would be MSSQLServer, however you do not need to provide that. Previously the express was a named instance, which requires you to pass along the instance name (or port) in order to connect.
2. The database name is MyDatabase. The assignment of MyDatabase_Data is for the data file inside the database. In this example a data and log file are designated and named. You can name these however you like (although you cannot have duplicate names within a database). You can also rename them after the database has been created should you so wish using an ALTER DATABASE statement.
3. Filegrowth is dependant upon data being written to the database. Log files will continue to grow if the database is in full or bulk-logged recovery unless you take log backups. However if in simple recovery then the log will grow as large as is required to support open transactions that happen, or to prevent a loss of data with CDC or replication (if a log continues to grow you can check for reasons using the log_reuse_desc column of sys.databases). For the data file, that will grow as you insert data, as an when needed. It is generally recommended to not use percentage based filegrowths as this can lead to unexpectedly large file growths, not to mention performance problems.

A further note to filegrowth. If you do not have IFI enabled then you will have IO stalls on writes to the data file that cause it to grow. Those stalls will last as long as it takes to grow out the file and zero it out to ensure the space is clear (2016 does not use zeroes, however the concept is the same). With IFI enabled (requires Perform Volume Maintenance Tasks permission for the service account, this can be done at install time with SQL 2016) then the data file can grow instantly and does not need zeroing out. Log files do not have the option for IFI (for security and data safety reasons), so any growth on that file will cause IO stalls while that completes, impacting performance.

The MAXSIZE settings in the create database statement will prevent the files from growing beyond those values. This would mean that you could not store more an a total of 10MB of data (including system data), and that no individual transaction could be larger than 5MB in size. Both of these values can be adjusted using an ALTER DATABASE statement.

• KMC: From reading "default instance", it leads me to think localhost\MSSQLServer = localhost, where the instance name is simply hidden by default. But when I try connecting SQL Server through cmd with sqlcmd -S locahost (1> ...) and sqlcmd -S localhost\MSSQLServer (Error: Connection string is not valid), neither of the commands works but each yield different result. I cannot understand.
• KMC: "...you cannot have duplicate names within a database..." well I just changed my connection string and have the same name for database, data file and log file, the code allows me to create my database without exception - is it something new in 2016 that I can use duplicate name?
• Nic: You cannot use duplicate file names in a database. See msdn.microsoft.com/en-us/library/… "logical_file_name Is the logical name used in SQL Server when referencing the file. Logical_file_name must be unique in the database and comply with the rules for identifiers. The name can be a character or Unicode constant, or a regular or delimited identifier."

Answer 2

A related note, not necessarily an answer, but it could be a bit clearer for your code, .Net has a class for SQLConnectionStringBuilder which might come in handy here. Instead of just concatenating your string together, you might want to look into passing the values to that object and use the output connection string property. .Net SQL Connection String Builder