The maximum size for a SQL Server 2016 database is 524,272 TB. Hardware limitations and physical management issues will mean that databases are likely to be restricted to a fraction of this size.
Each database can contain up to 32,767 file groups, and the maximum size of each file is 16TB.
The maximum size of Log files is 2 TB.
A maximum of 32,767 databases can be created on an SQL server and 50 instances can be defined on one physical server.
Microsoft recommends that SQL Server 2016 databases be placed on an NTFS file system. FAT32 is supported but not recommended. Physical storage can be local disk, shared storage or SMB. If used, a Windows file server should be 2012 or later.
A database file is made up of a number of 8 KB pages, and a Disk I/O operation will read or write a whole 8 KB page at a time. Pages are managed in extents, which consist of eight contiguous pages. At the beginning of each page is a 96-byte header header record that contains metadata about the page, including the page number, and the amount of free space in the page.
A database can consist of three types of file:
The .mdf, .ndf, and .ldf file naming standards are not enforced, but if you use them, then anyone can identify the file type from the extension. These are the names of the physical files on disk, called the os-file-names. You should not confuse these with the logical-file-names, the name used by applications.
When you define an SQL Server file, you can specify that it should grow automatically if it fills up its initial space allocation. You say how much space you want added to the file, and each time the file fills, it increases its size by that space amount. If you have multiple files in a filegroup, they will not autogrow until all the files are full. Growth then occurs using a round-robin algorithm.
If you don't state what growth parameters you want when you create a database then you will get the ones that are specified for the model database and they might not be appropriate. When a database adds an extent, all database processing stops until the add extent process completes. New extents will probably be allocated to different parts of the physical disk, so as extents are added, the database becomes fragmented and this can affect performance. Bottom line is that you do not want the database to be extending too often. If the database is growing rapidly, you want to be adding larger extents to reduce the adding frequency. If the database grows slowly, consider adding smaller extents to use space effectively.
You can also specify a maximum size for each file, otherwise the file can continue to grow until the disk is full. If you use autogrow, then you do not need to spend so much administration time monitoring the state of the database files and increasing them manually when they start to fill up.
It is possible to group database files together in filegroups to simplify management. It may be possible to improve performance by storing tables and indexes on separate disks, to spread the IO workload and prevent head contention. If you define a separate filegroup for every disk you can simplify the management of the files.
Log files are never members of a filegroup. Log space is managed separately from data space.
The primary filegroup is the default. It contains the primary data file and any other files that are not specifically assigned to another filegroup. System tables are allocated in the primary filegroup.
You can place specific files into User-defined filegroups by using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.
You can specify a default user-defined-filegroup for each database, so pages which are not given a specific filegroup are assigned to it, instead of the primary file group. If you do not specify a default filegroup, then the primary filegroup is the default.
It is possible to make all the files in a user-file-group read only, and it is also possible to detach a file group from one server, then reattach it to another server. This means that it is possible to copy an SQL database to removable media, then distribute it to remote clients, who would be able to read the data using their own SQL server.
You do not need to use file groups at all, especially if you are running a smaller system. In this case all files are included in the primary filegroup.