Cargando…
Professional Microsoft SQL Server 2012 administration /
Annotation
| Clasificación: | Libro Electrónico |
|---|---|
| Autor principal: | |
| Otros Autores: | , |
| Formato: | Electrónico eBook |
| Idioma: | Inglés |
| Publicado: |
Indianapolis, Ind. : Sons,
John Wiley & amp ;
©2012.
|
| Colección: | Wrox programmer to programmer
|
| Temas: | |
| Acceso en línea: | Texto completo (Requiere registro previo con correo institucional) |
Tabla de Contenidos:
- Machine generated contents note: ch. 1 SQL Server 2012 Architecture
- SQL Server 2012 Ecosystem
- New Important Features in 2012
- Production DBA
- Development DBA
- Business Intelligence DBA and Developer
- SQL Server Architecture
- Database Files and Transaction Log
- SQL Native Client
- Standard System Databases
- Schemas
- Synonyms
- Dynamic Management Objects
- SQL Server 2012 Data Types
- Editions of SQL Server
- Edition Overview
- Licensing
- Summary
- ch. 2 Installing SQL Server 2012 Best Practices
- Planning the System
- Hardware Options
- Software and Install Options
- Installing SQL Server
- New Installs
- Side-by-Side Installs
- Upgrades
- Unattended Installs
- Attended Installations
- Installing Analysis Services
- Multidimensional and Data Mining Mode (UDM Mode)
- Tabular Mode
- Installing Power Pivot for SharePoint
- Burning in the System
- Post-Install Configuration
- Configuring SQL Server Settings for Performance
- tempdb
- Configuring SQL Server Setting for Security
- Best Practices Analyzer (BPA)
- SQL Server Configuration Manager
- Back It Up
- Uninstalling SQL Server
- Uninstalling Reporting Services
- Uninstalling Analysis Services
- Uninstalling the SQL Server Database Engine
- Troubleshooting a Failed Install
- Summary
- ch. 3 Upgrading SQL Server 2012 Best Practices
- Why Upgrade to SQL Server 2012?
- Risk Mitigation
- the Microsoft Contribution
- Independent Software Vendors and SQL Community Contributions
- Upgrading to SQL Server 2012
- In-Place Upgrading
- Side-by-Side Upgrade
- In-Place Upgrade versus Side-By-Side Upgrade Considerations
- Pre-Upgrade Steps and Tools
- Pre-Upgrade Steps
- Pre-Upgrade Tools
- Backward Compatibility
- Unsupported and Discontinued Features in SQL Server 2012
- SQL Server 2012 Deprecated Database Features
- Other SQL Server 2012 Changes Affecting Behavior
- SQL Server Component Considerations
- Upgrading Full-Text Catalog
- Upgrading Reporting Services
- Upgrading to 64-Bit
- Post-Upgrade Checks
- Poor Query Performance After Upgrade
- Summary
- ch. 4 Managing and Troubleshooting the Database Engine
- Configuration and Administration Tools
- SQL Server Configuration Manager
- Startup Parameters
- Startup Stored Procedures
- Partially Contained Databases
- Troubleshooting Tools
- Dedicated Administrator Connection
- Rebuilding the System Databases
- Management Studio
- Reports
- Configuring SQL Server in Management Studio
- Filtering Objects
- Error Logs
- Activity Monitor
- Monitoring Processes in T-SQL
- sp_who and sp_who2
- sys.dm_exec_connections
- sys.dm_exec_sql_text
- Multiserver Management
- Central Management Servers and Server Groups
- SQL Server Utility
- Trace Flags
- Getting Help from Support
- SQLDumper.exe
- SQLDiag.exe
- Summary
- ch. 5 Automating SQL Server
- Maintenance Plans
- Maintenance Plan Wizard
- Maintenance Plan Designer
- Automating SQL Server with SQL Server Agent
- Jobs
- Schedules
- Operators
- Alerts
- SQL Server Agent Security
- Service Account
- Access to SQL Agent
- SQL Server Agent Proxies
- Configuring SQL Server Agent
- General Properties
- Advanced Properties
- Alert System Properties
- Job System Properties
- Connection Properties
- History Properties
- Database Mail
- Architecture
- Security
- Configuration
- Archiving
- Multiserver Administration
- Using Token Replacement
- Event Forwarding
- Using WMI
- Multiserver Administration
- Using Master and Target Servers
- Summary
- ch. 6 Service Broker in SQL Server 2012
- Asynchronous Messaging
- SQL Service Broker Overview
- SQL Server Service Broker Versus Other Message Queues
- Configuring SQL Server Service Broker
- Enabling
- Message Types
- Contracts
- Queues
- Services
- Routes
- Priorities
- Conversation Groups
- Using SQL Server Service Broker
- Sending Messages
- Receiving Messages
- Sending Messages Between Databases
- Sending Messages Between Instances
- External Activation
- Summary
- ch. 7 Sol Server CLR Integration
- Introduction to the CLR
- SQL Server as a .NET Runtime Host
- Application Domains
- T-SQL versus CLR
- Enabling CLR Integration
- Creating CLR Assemblies
- Non-Visual Studio Way
- Using Microsoft SQL Server Data Tools
- Securing CLR
- Performance Monitoring
- Windows System Monitor
- SQL Profiler
- Dynamic Management Views (DMVs)
- CLR Integration Design Goals
- Summary
- ch. 8 Securing the Database Instance
- Authentication Types
- SQL Authentication
- Windows Authentication
- SQL Versus Windows Authentication
- Authorizing Securables
- Server Securables
- Database Securables
- Permission Chains
- Cross Database Permission Chains
- Row Level Security
- Summary
- ch. 9 Change Management
- Creating Solutions and Projects
- Creating a Connection
- Creating a Project Query
- Policy-Based Management
- Policy-Based Management Overview
- Policy-Based Management Step by Step
- Scripting Policy-Based Management
- Policy-Based Management Implementation
- DDL Trigger Syntax
- Database Triggers
- Server Triggers
- Trigger Views
- Scripting Overview
- sqlcmd
- PowerShell
- Creating Change Scripts
- Data-Tier Applications
- SQL Server Data Tools
- Version Tables
- Summary
- ch. 10 Configuring the Server for Optimal Performance
- What Every DBA Needs to Know About Performance
- Performance Tuning Cycle
- Defining Good Performance
- Focus on What's Most Important
- What the Developer DBA Needs to Know About Performance
- Users
- SQL Statements
- Data Usage Patterns
- Robust Schema
- What the Production DBA Needs to Know About Performance
- Optimizing the Server
- Hardware Management
- CPU
- x64
- Cache
- Hyper-threading
- Multicore
- System Architecture
- Memory
- Physical Memory
- Physical Address Space
- Virtual Memory Manager
- Page File
- Page Faults
- I/O
- Network
- Disks
- Storage Considerations
- Designing a Storage System
- Large Storage System Considerations: SAN Systems
- Server Configuration
- Fragmentation
- Summary
- ch. 11 Optimizing SQL Server 2012
- Application Optimization
- Defining a Workload
- System Harmony Is the Goal
- Silent Killer: I/O Problems
- SQL Server I/O Process Model
- Database File Placement
- tempdb Considerations
- Table and Index Partitioning
- Why Consider Partitioning?
- Creating a Partition Function
- Creating Filegroups
- Creating a Partition Scheme
- Creating Tables and Indexes
- Data Compression
- Row Compression
- Page Compression
- Estimating Space Savings
- Monitoring Data Compression
- Data Compression Considerations
- CPU Considerations
- Cache Coherency
- Affinity Mask
- Max Degree of Parallelism (MAXDOP)
- Affinity I/O Mask
- Memory Considerations and Enhancements
- Tuning SQL Server Memory
- 64-bit Versions of SQL Server 2012
- Data Locality
- Max Server Memory
- Index Creation Memory Option
- Minimum Memory per Query
- Resource Governor
- Basic Elements of Resource Governor
- Using Resource Governor from SQL Server 2012 Management Studio
- Monitoring Resource Governor
- Summary
- ch.
- 12 Monitoring Your SQL Server
- Goal of Monitoring
- Determining Your Monitoring Objectives
- Establishing a Baseline
- Comparing Current Metrics to the Baseline
- Choosing the Appropriate Monitoring Tools
- Performance Monitor
- CPU Resource Counters
- Disk Activity
- Memory Usage
- Performance Monitoring Tools
- Monitoring Events
- Default Trace
- system_health Session
- SQL Trace
- Event Notifications
- SQL Server Extended Events
- Monitoring with Dynamic Management Views and Functions
- What's Going on Inside SQL Server?
- Viewing the Locking Information
- Viewing Blocking Information
- Index Usage in a Database
- Indexes Not Used in a Database
- View Queries Waiting for Memory Grants
- Connected User Information
- Filegroup Free Space
- Query Plan and Query Text for Currently Running Queries
- Memory Usage
- Buffer Pool Memory Usage
- Monitoring Logs
- Monitoring the SQL Server Error Log
- Monitoring the Windows Event Logs
- Management Data Warehouse
- System Data Collection Sets
- Viewing Data Collected by the System Data Collection Sets
- Creating Your Own Data Collection Set
- Examining the Data You Collected
- SQL Server Standard Reports
- System Center Management Pack
- SQL Server Best Practice Analyzer
- System Center Advisor
- Summary
- ch. 13 Performance Tuning T-SQL
- Physical Query Processing Part One: Compilation and Recompilation
- Compilation
- Recompilation
- Tools and Commands for Recompilation Scenarios
- Parser and Algebrizer
- Optimization
- Physical Query Processing Part Two: Execution
- Database I/O Information
- Working with the Query Plan
- Estimated Execution Plan
- Actual Execution Plan
- Index Access Methods
- Fragmentation
- Statistics
- Join Algorithms
- Data Modification Query Plan
- Query Processing Enhancements on Partitioned Tables and Indexes
- Gathering Query Plans for Analysis with SQL Trace
- Summary
- ch. 14 Indexing Your Database
- Noteworthy Index-Related Features in SQL Server
- What's New for Indexes in SQL Server 2012.
- Note continued: Index Features from SQL Server 2008R2, SQL Server 2008, and SQL Server 2005
- Partitioned Tables and Indexes
- Understanding Indexes
- Creating Indexes
- Why Use Both Partitioned Tables and Indexes?
- Creating Partitioned Tables
- Index Maintenance
- Monitoring Index Fragmentation
- Cleaning Up Indexes
- Improving Query Performance with Indexes
- Database Tuning Advisor
- Too Many Indexes?
- Summary
- ch. 15 Replication
- Replication Overview
- Replication Components
- Replication Types
- Replication Enhancements in SQL Server 2012
- Replication Models
- Single Publisher, One or More Subscribers
- Multiple Publishers, Single Subscriber
- Multiple Publishers Also Subscribing
- Updating Subscriber
- Peer-to-Peer
- Implementing Replication
- Setting Up Snapshot Replication
- Setting Up Distribution
- Implementing Snapshot Replication
- Implementing Transactional and Merge Replication
- Peer-to-Peer Replication
- Setting Up Peer-to-Peer Replication
- Configuring Peer-to-Peer Replication
- Scripting Replication
- Monitoring Replication
- Replication Monitor
- Performance Monitor
- Replication DMVs
- sp_replcounters
- Summary
- ch. 16 Clustering SQL Server 2012
- Clustering and Your Organization
- What Clustering Can Do
- What Clustering Cannot Do
- Choosing SQL Server 2012 Clustering for the Right Reasons
- Alternatives to Clustering
- Clustering: The Big Picture
- How Clustering Works
- Clustering Options
- Upgrading SQL Server Clustering
- Don't Upgrade
- Upgrading Your SQL Server 2012 Cluster In Place
- Rebuilding Your Cluster
- Back-Out Plan
- Which Upgrade Option Is Best?
- Getting Prepared for Clustering
- Preparing the Infrastructure
- Preparing the Hardware
- Clustering Windows Server 2008
- Before Installing Windows 2011 Clustering
- Installing Windows Server 2008 Failover Clustering
- Preparing Windows Server 2008 for Clustering
- Clustering Microsoft Distributed Transaction Coordinator
- Clustering SQL Server 2012
- Step by Step to Cluster SQL Server
- Installing the Service Pack and Cumulative Updates
- Test, Test, and Test Again
- Managing and Monitoring the Cluster
- Troubleshooting Cluster Problems
- How to Approach Windows Failover Clustering Troubleshooting
- Doing It Right the First Time
- Gathering Information
- Resolving Problems
- Working with Microsoft
- Summary
- ch. 17 Backup and Recovery
- Types of Failure
- Hardware Failure
- Data Modification Failure
- Software Failure
- Local Disasters
- Making Plans
- Backup/Recovery Plan
- Disaster Recovery Planning
- Creating the Disaster Recovery Plan
- Maintaining the Plan
- Overview of Backup and Restore
- How Backup Works
- Copying Databases
- Backup Compression
- Comparing Recovery Models
- Choosing a Model
- Switching Recovery Models
- Backing Up History Tables
- Permissions Required for Backup and Restore
- Backing Up System Databases
- Full-Text Backup
- Verifying the Backup Images
- How Restore Works
- Preparing for Recovery
- Recoverability Requirements
- Data Usage Patterns
- Maintenance Time Window
- Other High-Availability Solutions
- Developing and Executing a Backup Plan
- Using SQL Server Management Studio
- Database Maintenance Plans
- Using Transact-SQL Backup Commands
- Managing Backups
- Backup and Restore Performance
- Performing Recovery
- Restore Process
- SQL Server Management Studio Restore
- T-SQL Restore Command
- Restoring System Databases
- Archiving Data
- SQL Server Table Partitioning
- Partitioned View
- Summary
- ch. 18 SQL Server 2012 Log Shipping
- Log Shipping Deployment Scenarios
- Log Shipping to Create a Warm Standby Server
- Log Shipping as a Disaster Recovery Solution
- Log Shipping as a Report Database Solution
- Log-Shipping Architecture
- Primary Server
- Secondary Server
- Monitor Server
- Log Shipping Process
- System Requirements
- Network
- Identical Capacity Servers
- Storage
- Software
- Deploying Log Shipping
- Initial Configuration
- Deploying with Management Studio
- Deploying with T-SQL Commands
- Monitoring and Troubleshooting
- Monitoring with Management Studio
- Monitoring with Stored Procedures
- Troubleshooting Approach
- Managing Changing Roles
- Synchronizing Dependencies
- Switching Roles from the Primary to Secondary Servers
- Switching Between Primary and Secondary Servers
- Redirecting Clients to Connect to the Secondary Server
- Database Backup Plan
- Integrating Log Shipping with Other High-Availability Solutions
- SQL Server 2012 Data Mirroring
- Windows Failover Clustering
- SQL Server 2012 Replication
- Removing Log Shipping
- Removing Log Shipping with Management Studio
- Removing Log Shipping with T-SQL Commands
- Log-Shipping Performance
- Upgrading to SQL Server 2012 Log Shipping
- Minimum Downtime Approach
- With Downtime Approach
- Deploy Log Shipping Approach
- Summary
- ch. 19 Database Mirroring
- Overview of Database Mirroring
- Operating Modes of Database Mirroring
- Database Mirroring In Action
- Preparing the Endpoints
- Preparing the Database for Mirroring
- Initial Synchronization Between Principal and Mirror
- Establishing the Mirroring Session
- High-Safety Operating Mode Without Automatic Failover
- High-Safety Operating Mode with Automatic Failover
- High-Performance Operating Mode
- Database Mirroring and SQL Server 2012 Editions
- Database Mirroring Catalog Views
- sys.database_mirroring
- sys.database_mirroring_witnesses
- sys.database_mirroring_endpoints
- Database Mirroring Role Change
- Automatic Failover
- Manual Failover
- Forced Failover
- Database Availability Scenarios
- Principal Is Lost
- Mirror Is Lost
- Witness Is Lost
- Mirror and Witness Are Lost
- Monitoring Database Mirroring
- Monitoring Using System Monitor
- Monitoring Using Database Mirroring Monitor
- Setting Thresholds on Counters and Sending Alerts
- Troubleshooting Database Mirroring
- Troubleshooting Setup Errors
- Troubleshooting Runtime Errors
- Automatic Page Repair
- Preparing the Mirror Server for Failover
- Hardware, Software, and Server Configuration
- Database Availability During Planned Downtime
- SQL Job Configuration on the Mirror
- Database Trustworthy Bit on the Mirror
- Client Redirection to the Mirror
- Mirroring Multiple Databases
- Database Mirroring and Other High-Availability Solutions
- Database Mirroring versus Clustering
- Database Mirroring versus Transactional Replication
- Database Mirroring versus Log Shipping
- Database Mirroring Versus Availability Groups
- Mirroring Event Listener Setup
- Database Snapshots
- Summary
- ch.
- 20 Integration Services Administration and Performance Tuning
- Tour of Integration Services
- Integration Services Uses
- Main Parts of Integration Services
- Project Management and Change Control
- Administration of the Integration Services Service
- Overview of the Integration Services Service
- Configuration
- Event Logs
- Monitoring Activity
- Administration of Integration Services Packages in Package Deployment Model
- Using Management Studio for Package Management
- Deployment
- Administration of Integration Services Packages in Project Deployment Model
- Configuring the SSIS Catalog
- Deploying Packages
- Configuring Packages
- Execution and Scheduling
- Running Packages in SQL Server Data Tools
- Running Packages with the SQL Server Import and Export Wizard
- Running Packages with DTExec
- Running Packages with DTExecUI (Package Deployment Model)
- Running Packages with the Execute Package Tool (Project Deployment Model)
- Scheduling Execution with SQL Server Agent
- Running Packages with T-SQL
- Applying Security to Integration Services
- Overview of Integration Services Security
- Securing Packages in Package Deployment Model
- Summary
- ch. 21 Analysis Services Administration and Performance Tuning
- Tour of Analysis Services
- MOLAP Components
- Tabular Model Components
- Analysis Services Architectural Components
- Administering Analysis Services Server
- Server Properties
- Required Services
- Analysis Services Scripting Language
- Administering Analysis Services Databases
- Deploying Analysis Services Databases
- Processing Analysis Services Objects
- Backing Up and Restoring Analysis Services Databases
- Synchronizing Analysis Services Databases
- Analysis Services Performance Monitoring and Tuning
- Monitoring Analysis Services Events
- Creating Traces for Replay
- Using Flight Recorder for After-the-Fact Analysis
- Management of Analysis Services MOLAP Model Storage
- Storage Modes
- Partition Configuration
- Designing Aggregations in the MOLAP Model
- Applying Security to Analysis Services in the MOLAP Model
- Server Role
- Database Role
- Database Role Permissions
- Applying Security to Analysis Services in the Tabular Model
- Summary
- ch. 22 SQL Server Reporting Services Administration
- SQL Server Reporting Services Configuration Manager
- Service Account
- Web Service URL
- Reporting Services Databases
- Report Manager URL
- E-mail Settings
- Execution Account
- Encryption Keys
- Scale-out Deployment
- Report Server Properties
- General Properties Page
- Execution Properties Page
- History Properties Page
- Logging Properties Page.
- Note continued: Security Properties Page
- Advanced Properties Page
- Report Execution Log
- Report Builder
- Report Manager
- Managing Report Manager
- Managing Reports
- Summary
- ch. 23 SQL Server 2012 Sharepoint 2010 Integration
- Components of Integration
- PowerPivot
- Reporting Services
- Power View
- Service Application Architecture
- Data Refresh
- Using Data Connections in Excel
- PerformancePoint Data Refresh
- Visio Services Data Refresh
- PowerPivot Data Refresh
- Summary
- ch. 24 SQL Azure Administration and Configuration
- Introduction to SQL Azure
- SQL Azure Architecture
- Client Layer
- Services Layer
- Platform Layer
- Infrastructure Layer
- Configuring SQL Azure
- Server and Database Provisioning
- Throttling and Load Balancing
- Configuring SQL Azure Firewalls
- Connecting to SQL Azure
- Administering SQL Azure
- Creating Logins and Users
- Assigning Access Rights
- Working with SQL Azure
- Backups with SQL Azure
- Object Explorer for SQL Azure
- What's Missing in SQL Azure
- Summary
- ch. 25 Alwayson Availability Groups
- Architecture
- Availability Group Replicas and Roles
- Availability Modes
- Types of Failover Supported
- Allowing Read-Only Access to Secondary Replicas
- Availability Group Example
- Configures New Availability Group
- Configure an Existing Availability Group
- Availability Group Failover Operation
- Suspend an Availability Database
- Resume an Availability Database
- Client Application Connections
- Active Secondary for Secondary Read-Only
- Read-Only Access Behavior
- Secondary Replica Client Connectivity
- Performance
- Backup on the Secondary Replica
- Evaluate Backup Replicas Metadata
- AlwaysOn Group Dashboard
- Monitoring and Troubleshooting
- Summary.