MICROSOFT SQL SERVER ADMINISTRATION
Installing Microsoft SQL Server 2012
- Understanding SQL Server 2012 Editions
- Installing a Default, Named, or Multiple Instances of SQL Server 2012
- Determining When to Use Multiple
- Determining Service Accounts
- Choosing an Authentication Mode
- Troubleshooting the Installation
Creating and Configuring Databases
- Data Files
- Log Files
- Configuring Filegroups
- How to Configure Data Files and Log Files
- Introducing RAID-0 , RAID-1, RAID-5
- Configuring Database Files with RAID Systems
- Introducing Extents and Pages
- Creating and Configuring Databases
- Recovery Models Overview
- How to Configure Recovery Models
Implementing Security in SQL Server
- Using Windows Authentication Mode
- Using Mixed Mode
- Setting the Authentication Mode
- Understanding SQL Server Logins
- Understanding Fixed Server Roles
- Understanding Permissions
- Understanding Database Roles
- Managing Schemas
- Understanding Permission States
- Introducing Ownership Chains
- Introducing Linked Server Security
Working with Tables
- Introducing Built-in Data Types
- Introducing Computed Columns
- Nullability
- Identity
- Creating Tables
- Creating Temporary Tables
- Table Variables
- Assigning Permissions
- Implementing Rules
- Implementing Constraints
- Check Constraints
- Default Constraints
- Unique Constraints
- Primary Key Constraints
- Foreign Key Constraints
- Creating User-Defined Types
Performing Indexing and Full-Text Searching
- Understanding Clustered Indexes
- Understanding Nonclustered Indexes
Working with Transact-SQL
- Construct SQL queries to return data
- Querying multiple tables using Join
- Determining the Columns to Return
- Modifying Data
- Creating Local and Global Temporary Tables
- Using the SELECT INTO Command
- Working with Transactions
- Beginning and Committing or Rolling Back Transactions
Creating Partitions
- Implement partitions
- Creating a Partition Function
- Creating a Partition Scheme
- Partitioning Tables and Indexes
- Querying Partitions
- Managing Partitions
Implementing Views
- How to Create a View
- Understanding ENCRYPTION, SCHEMABINDING,
- Understanding Ownership Chains
Creating Functions, Stored Procedures, and Triggers
- Creating Stored Procedures
- Recompiling Stored Procedures
- Assign Permissions to a Role for a Stored Procedure
- Understanding Triggers
- Introducing Functions
- Table-Valued Functions
Working with Flat Files
- Importing Data Using Bulk Insert
- Importing Data Using the bcp Utility
- Copying Data Using SSIS
Backing Up, Restoring, and Moving a Database
- Backing Up a Database
- Performing Full Backups
- Performing Differential Backups
- Transaction Log Backups
- Performing Filegroup Backups
- Restoring a Database
- Restoring a Full Backup
- Restoring a Differential Backup
- Restoring a Transaction Log Backup
- Moving a Database
- Moving a Database by Using Detach/Attach
Using Transact-SQL to Manage Databases
- Managing Index Fragmentation
- Understanding Index Fragmentation
- Identifying Index Fragmentation
- Managing Index Fragmentation
- Using DBCC CHECKDB
Working with HTTP Endpoints
- Understanding HTTP Endpoint Security
- Creating an HTTP Endpoint
Working with SQL Server Agent Jobs
- How to Create a SQL Server Agent Job
- How to Create Job Steps
- How to Create Job Schedules
- How to Configure an Operator
- Creating Alerts
Monitoring and Troubleshooting SQL Server Performance
- Working with SQL Server Profiler
- Defining a Trace
- Starting, Pausing, and Stopping a Trace
- Saving a Trace Log
- Creating a Replay Trace
- Using the Database Engine Tuning Advisor
- Understanding Locking
Managing Database Snapshots
- Creating a Database Snapshot
- Database Snapshot Structure
- Copy-On-Write Technology
- Retrieving Data from a Database Snapshot
Implementing Database Mirroring
- Understanding Database Mirroring Roles
- Principal Role
- Mirror Role
- Witness Server
- Database Mirroring Endpoints
- Understanding Operating Modes
- High Availability Operating Mode
- High Performance Operating Mode
- High Protection Operating Mode
- Failing Over a Database Mirror
- Removing Database Mirroring
Implementing Log Shipping
- Configuring Log Shipping Options
- How to Enable the Primary Database
- Defining Log Shipping Backup Options
- How to Configure Secondary Databases
- Configuring the Copy Files Task
- Configuring Log Shipping Restore Options
Managing Replication
- Understanding Replication Types
- Understanding Replication Terminology
- Replication Types
- Replication Agents
- Setting Up Replication
- How to Set Up the Distributor
- How to Create a Publication
- How to Subscribe to the Publication
