Getting Started with SQL Server 2014 Administration

Preface 1Chapter 1: SQL Server 2014 and Cloud 5Windows Azure SQL Database 5

Creating a Windows Azure SQL database 6

Connecting to a Windows Azure SQL database withManagement Studio 9

Migrating an existing database to Microsoft Azure 11

Integrating Microsoft Azure Storage 13

Creating a database that uses the Microsoft Azure Storage integration 13

Creating a Microsoft Azure Storage account and container 14

The container policy and Shared Access Signature 15

Creating a credential 17

Creating a database using Microsoft Azure Storage 17

Microsoft Azure Virtual Machines 18

Creating a Microsoft Azure Virtual Machine 18

Summary 21

Chapter 2: Backup and Restore Improvements 23

Database backups to a URL and Microsoft Azure Storage 23

Backing up a SQL Server database to a URL 25

Restoring a backup stored on Windows Azure Storage 28

SQL Server Managed Backup to Microsoft Azure 30

Configuring SQL Server-managed backups to Microsoft Azure 30

Encryption 31

Creating an encrypted backup 31

Creating a database master key for the master database 32

Creating a certificate or asymmetric key 32

Creating an encrypted database backup 32

Summary 33

Chapter 3: In-Memory Optimized Tables 35

Requirements 35

Virtualized environments 36

Memory-optimized tables 37

Memory-optimized tables and indexes 41

Transact-SQL and natively compiled stored procedures 44

Concurrency 46

Some concurrency terminologies 46

Summary 47

Chapter 4: Delayed Durability 49

Understanding the full transaction durability 50

Understanding the delayed transaction durability 50

Full Durability versus Delayed Durability 51

Using delayed transaction durability 52

Transaction level Delayed Durability – Transact-SQL 53

Delayed Durability and Natively Compiled Stored Procedures 54

Delayed Durability and transaction log flush 55

Delayed Durability and potential data loss 55

Delayed Durability and other SQL Server components 61

Summary 62

Chapter 5: AlwaysOn Availability Groups 63

Introducing AlwaysOn Availability Groups 63

Enhancements in AlwaysOn Availability Groups 64

Using Microsoft Azure Virtual Machines as replicas 65

The commit mode 65

Reporting and administrating on secondary replicas 65

Building AlwaysOn Availability Groups 66

Windows Server Failover Cluster 66

Configuring the instance service 67

The Availability Group listener 68

Creating Availability Group 68

Troubleshooting Availability Groups 72

Creating a cloud-based AlwaysOn Availability Group 74

Creating an AlwaysOn Availability Group in Microsoft Azure 75

Creating a Hybrid Availability Group 75

Summary 77

Chapter 6: Performance Improvements 79

Partition switching and indexing 79

Columnstore indexes 80

Creating a clustered columnstore index 81

Updating a table with a clustered columnstore index 82

Buffer pool extensions 84

Cardinality estimator and query plans 86

Statistics 86

Resource Governor 86

Summary 87

Index 89