| Description |
1 online resource. |
| Series |
Wrox programmer to programmer
|
| Summary |
Annotation An essential how-to guide for experienced DBAs on the most significant product release since 2005!Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security. Serves as a must-have how-to guide on the new features of the 2012 release of Microsoft SQL ServerWalks you through ways to manage and monitor SQL Server and automate administrationGuides you through SQL Azure for cloud computing, data replication, and data warehouse performance improvementsAddresses client-connectivity enhancements, new features that ensure high availability of mission-critical applications, and tighter integration with Excel and SharePoint for data analysis and reportingHighlights major changes to the suite of SQL Server BI tools, including integration, reporting, and analysis servicesIncludes real-world case examples and best practices from Microsoft insidersIf you're a database administrator eager to get started with the newest version of SQL Server, then this book is essential reading. |
| Contents |
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. |
| Subject |
SQL server.
|
|
SQL server. |
|
SQL server |
|
Database management.
|
|
Bases de données -- Gestion. |
|
Database management |
|
Engineering & Applied Sciences. |
|
Computer Science. |
| Added Author |
Jorgensen, Adam. Author.
|
|
Strate, Jason. Editor.
|
|
Cherry, Denny. Editor.
|
| Other Form: |
Print version: Professional Microsoft SQL server 2012 administration. 1st ed. Indianapolis, IN : Wiley Pub., Inc., 2012 9781118106884 (DLC) 2012933629 (OCoLC)794981006 |
| ISBN |
9781118286845 (electronic bk.) |
|
1118286847 (electronic bk.) |
|
9781118282182 (electronic bk.) |
|
1118282183 (electronic bk.) |
|
9781118283882 (electronic bk.) |
|
1118283880 (electronic bk.) |
|
1118106881 |
|
9781118106884 |
|
More Resources