Lecture 5 - Manage database files

Manage database files Marcin Szeliga [email protected]

Manage database files › Database Storage Options › Managing Storage for System Databases › Managing Storage for User Databases › Moving and Copying Database Files › Configuring the Buffer Pool Extension

› Configuring the Stretch Database

How Data is Stored in SQL Server › SQL Server maps a database over a set of operating-system files › Data and log information are never mixed in the same file, and individual files are used only by one database › Filegroups are named collections of files › At a minimum, every SQL Server database has two operating system files: a data file and a log file › Data files contain data and objects such as tables, indexes, stored procedures, and views › Log files contain the information that is required to recover all transactions in the database

Primary data file: .mdf Secondary data file: .ndf

Transaction log file: .ldf Extent: eight contiguous 8 KB pages

Page: 8 KB

Different Type of Database Files › Primary Data Files – Recommended file name extension is .mdf – Contains the startup information for the database and pointers to other files

› Secondary Data Files – The recommended extension is .ndf – Can be used to spread data across multiple disks by putting each file on a different disk drive – If a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow

› Transaction Logs – The recommended extension is .ldf – Hold all the information that is used to recover the database

› If you have tables that are frequently used together, you should put them on separate filegroups and physical drives

Determining File Placement and Number of Files › Isolate log and data files at the physical disk level › Determine the number and location of data files based on performance and maintenance considerations – Use additional files to spread data across storage locations – Use smaller data files when easier maintenance is needed – Use data files as units of backup and restore

› Determine log file requirements – Use a single log file in most situations, as log files are written sequentially

Ensuring Sufficient File Capacity › Estimate the size of data, log files and tempdb: – Perform load testing with the actual application – Check with the application vendor

› Set the size to a reasonable size: – – – –

Leave enough space for new data, without the need to regularly expand Monitor data and log file usage Plan for manual expansion Keep autogrowth enabled to allow for unexpected growth

Considerations for Disk Storage Devices › Direct Attached Storage – disks connected by a RAID controller › Storage Area Network – disks connected by a network and available to multiple servers

› Windows Storage Pools – commodity disk drives grouped together to create one large storage space

RAID Levels RAID 0

RAID 1

ACEGIK

ABCDEF

BDFHJL

ABCDEF

RAID 5

RAID 10

A#EG#K

ACEGIK

BC#HI#

BDFHJL

#DF#JL

ACEGIK BDFHJL

SMB Fileshare › › › ›

SMB – Server Message Block Used for file and printer sharing Considerably improved in recent versions Shares addressed using UNC paths, for example: ›

\\servername\share-name

› Can be configured in Windows Failover Clusters to provide highly available storage › Benefits of SMB Storage for SQL Server – Cost – Manageability

Implementing SMB Fileshare › SMB Share Permissions: – Full Control – SQL Server Service Account – Full Control – SQL Server Agent Service Account

› Use UNC Path in Create Database statement › Share which cannot be used: – Administrative Shares – Loopback Shares – Mapped Network Drives

› Filestream on SMB is not supported

Azure Storage › Azure has five types of storage: Blobs, Files, Disks, Tables and Queues › And four data redundancy option: – Locally Redundant Storage (LRS) makes multiple synchronous copies of your data within a single datacenter – Zone Redundant Storage (ZRS) stores three copies of data across multiple datacenters within or across regions – Geographically Redundant Storage (GRS) - same as LRS, plus multiple asynchronous copies to a second datacenter hundreds of miles away – Read-Access Geographically Redundant Storage (RA-GRS) - same as GRS, plus read access to the secondary datacenter

› Check prices at https://azure.microsoft.com/enus/pricing/details/storage/

Azure Premium Storage › Virtual machine (VM) disks that use Premium Storage store data on solid state drives (SSDs) › An Azure VM DS, DSv2 or GS series supports attaching several Premium Storage disks, so that your applications can have up to 64 TB of storage per VM – You can use both Premium and Standard storage disks for operating system and temp drive

› With Premium Storage, your applications can achieve 80,000 IOPS per VM and 2000 MB per second disk throughput per VM

Azure Premium Storage cont. › Currently, there are three types of Premium Storage disks: P10, P20, and P30 Premium Storage Disk

P10

P20

P30

Disk size

128 GiB

512 GiB

1024 GiB (1 TB)

IOPS per disk

500

2300

5000

Throughput per disk

100 MB per second

150 MB per second

200 MB per second

› Use Add-AzureRmVMDataDisk cmdlet

Disk Caching › BlobCache uses a combination of the Virtual Machine RAM and local SSD for caching › This cache is available for the Premium Storage persistent disks and the VM local disks

Disk Caching Setting

Recommendation on when to use this setting

None

Configure host-cache as None for write-only and write-heavy disks

ReadOnly

Configure host-cache as ReadOnly for read-only and read-write disks

ReadWrite

Configure host-cache as ReadWrite only if your application properly handles writing cached data to persistent disks when needed

Manage database files › Database Storage Options › Managing Storage for System Databases › Managing Storage for User Databases › Configuring the Buffer Pool Extension › Configuring the Stretch Database

SQL Server System Databases System Database

Description

master

Stores all system-level configuration

msdb

Holds SQL Server Agent configuration data

model

Provides the template for new databases

tempdb

Holds temporary data

resource

Contains system objects that are mapped to the sys schema of databases

Moving System Databases › Moving msdb and model, and tempdb – – – –

Execute ALTER DATABASE … MODIFY FILE for each file. Stop the SQL Server service Move the files Restart the SQL Server service

› Moving master – – – –

Change the –d and –l startup parameters for the SQL Server service. Stop the SQL Server service Manually move the files while the instance is stopped Restart the SQL Server service

› Misconfiguration can prevent SQL Server from starting

Considerations for tempdb › tempdb: – Contains temporary data for internal objects, row versioning, and user objects – Is truncated or rebuilt with every restart of the instance – Occupies varying amounts of space – Should be tested with real-life workloads

› Place tempdb on a fast and separate I/O subsystem to ensure good performance › Split tempdb into data files of equal size per core

Manage database files › Database Storage Options › Managing Storage for System Databases › Managing Storage for User Databases › Configuring the Buffer Pool Extension › Configuring the Stretch Database

Creating User Databases › Create databases: – In SQL Server Management Studio – By using the CREATE DATABASE statement CREATE DATABASE Sales ON (NAME = Sales_dat, FILENAME = ‘M:\Data\Sales.mdf', SIZE = 100MB, MAXSIZE = 500MB, FILEGROWTH = 20% ) LOG ON (NAME = Sales_log, FILENAME = 'L:\Logs\Sales.ldf', SIZE = 20MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB );

Configuring Database Options › Database-level options are unique to each database Option

Description

Auto options

Defines whether some operations should occur automatically within the database

Page verify

Defines how the page should be verified when read from disk; should be set to CHECKSUM

Recovery model

Defines the recovery model of the database

State options

Sets the state of the database, such as Online/Offline, Restricted Access or Read Only

Altering User Databases › Altering database options: ALTER DATABASE HistoricSales SET READ_ONLY;

› Altering database compatibility options: ALTER DATABASE Sales SET COMPATIBILITY_LEVEL = 100;

Managing Database Files › Adding space to a database: – ALTER DATABASE … ADD FILE – ALTER DATABASE … MAXSIZE

› Dropping database files: – Empty file: DBCC SHRINKFILE … EMPTYFILE – Drop file: ALTER DATABASE

› Shrinking databases: – DBCC SHRINKDATABASE – DBCC SHRINKFILE – TRUNCATE_ONLY

Resizing Database Files › Databases expand according to growth parameters › Manually expand a database by allocating additional space › Use the MAXSIZE parameter to specify maximum size – If a database exhausts the space, error 1105 is raised

› The log transaction file can be truncated › Each file within a database can be reduced – The only time a data file shrink won't affect performance is if you use the WITH TRUNCATEONLY option

› Files are always shrunk from the end › Shrink seriously affects performance while it's running › Shrink also affects performance after it's run › DBCC SHRINKFILE ( {file_name | file_id } › [, target_size][, {EMPTYFILE | NOTRUNCATE | TRUNCATEONLY} ] ) › DBCC SHRINKDATABASE (database_name [, target_percent] › [, {NOTRUNCATE | TRUNCATEONLY} ] )

Introduction to Filegroups › Creating filegroups – CREATE DATABASE … FILEGROUP () – ALTER DATABASE … ADD FILEGROUP

› Setting the default filegroup – ALTER DATABASE … MODIFY FILEGROUP DEFAULT

PRIMARY D:\Data1.mdf

Custom_FG E:\Data.mdf

› Using read-only filegroups – ALTER DATABASE … MODIFY FILEGROUP READONLY

D:\Data2.ndf

L:\Log.ldf

F:\Data.ndf

Filegroups › One filegroup always has the property of DEFAULT – By default, the primary filegroup is the also the default filegroup – The default filegroup contains the pages for all tables and indexes that aren’t placed in a specific filegroup

› When you add space to objects stored in a particular filegroup, the data is stored in a proportional fill manner – If you have one file in a filegroup with twice as much free space as another, the first file will have two extents allocated from it for each extent allocated from the second file

› Use filegroups to allow backups of parts of the database – You can back up just a certain set of critical tables – You can also restore individual files or filegroups in two ways: › Do a partial restore of a database and restore only a subset of filegroups, which must always include the primary filegroup – The database will be online as soon as the primary filegroup has been restored

› You can restore backups of the filegroups on failured disks on top of the existing database

Moving and Copying Database Files › Data and log files can be moved within the instance: – Database must be offline

› ALTER DATABASE statement: – For copying within an instance – Manually move files on the file system

› Use the Copy Database Wizard › Detaching a database unhooks the database from the instance: – – – –

Data and log files are kept intact Detached files can be attached again on the same or a different instance Use detach/attach to move databases to other instances Detach/attach is useful in disaster recovery situations

SQL Server Storage in Microsoft Azure › Benefits of SQL Server data files in Microsoft Azure – Simpler migration – Separation of compute and storage nodes – Simpler database recovery – Security – Backup strategy

On-Premise Machine

On-Premise Microsoft Azure

Microsoft Azure Virtual Machine SQL Server Data Files

Implementing SQL Server Data Files in Azure › Create an Azure storage account › Add a container › Generate SAS key › Create a credential on the SQL Server Instance › Create a database using the container credential

Implementing SQL Server Data Files in Azure cont. › › › › › › › › › › ›

-- Create a credential CREATE CREDENTIAL [https://testdb.blob.core.windows.net/data] WITH IDENTITY='SHARED ACCESS SIGNATURE', SECRET = 'your SAS key' -- Create database with data and log files in Windows Azure container. CREATE DATABASE testdb ON ( NAME = testdb_dat, FILENAME = 'https://testdb.blob.core.windows.net/data/TestData.mdf' ) LOG ON ( NAME = testdb_log, FILENAME = 'https://testdb.blob.core.windows.net/data/TestLog.ldf')

Manage database files › Database Storage Options › Managing Storage for System Databases › Managing Storage for User Databases › Configuring the Buffer Pool Extension › Configuring the Stretch Database

Introduction to the Buffer Pool Extension › Extends buffer cache to nonvolatile storage

Buffer cache (RAM)

› Improves performance for readheavy OLTP workloads › SSD devices are often more cost effective than adding physical memory › Simple configuration with no changes to existing applications

Buffer cache extension (SSD)

Clean pages Pages

Data files (Disk)

Considerations for Using the Buffer Pool Extension › Improves performance for OLTP databases where: – – – –

OLTP operations have a high volume of reads There is up to 32 GB of physical memory Buffer Pool Extension is 4x to 10x physical memory Buffer Pool Extension is on high throughput SSD storage

› Unlikely to improve performance for: – Data warehouse workloads – OLTP workloads with a high volume of write operations – Servers with more than 64 GB of physical memory for SQL Server

Configuring the Buffer Pool Extension › Enable using ALTER SERVER CONFIGURATION ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'E:\SSDCACHE\MYCACHE.BPE', SIZE = 50 GB);

› To reconfigure, disable and then re-enable

Manage database files › Database Storage Options › Managing Storage for System Databases › Managing Storage for User Databases › Moving and Copying Database Files › Configuring the Buffer Pool Extension

› Configuring the Stretch Database

Stretch Database › What is Stretch Database? – Historical data – cloud storage – Current data – onpremise

› Benefits – Quicker backups – Reduced on-premise storage requirements – Seamless to users

Azure

On-Premise

SQL Server Instance

Linked Server Connection

Stretch Database Security › Enable Stretch Database at the instance level Requires – SysAdmin or ServerAdmin

› Enable a database for Stretch Database requires – Control Database – Administrator Permissions for Remote Endpoint

› Data security – Unaffected by Stretch Database

Implement a Stretch Database › Stretch Database Advisor – Part of SQL Server 2016 Upgrade Advisor – Analyzes Databases for Stretch Database compatibility – Highlights issues and blockers

› Implement entirely in Management Studio – No need to preconfigure Azure storage or servers

› Two Ways to Implement – Wizard – Transact-SQL

MANAGE DATABASE FILES Database Storage Options Managing Storage for System Databases Managing Storage for User Databases Moving and Copying Database Files Configuring the Buffer Pool Extension Configuring the Stretch Database