SqlServer-QA.net

Its the holiday season!

All is well and year long working chores will make you down… looking forward to spend quality time with family and friends in tis’ season!

This makes less availability of staff and higher risk of recovery in case of any disaster on your data.

SQL Server administration is one of the key responsibilities for every DBA user to keep up the availability, performance & scalability of data platform. The release since SQL Server  2008 introduces new features and enhancements to existing features on the areas of Availability, Manageability, Programmability, Scalability and Security that will ensure the keep up of data platform.

Not only the high availablity and disaster recovery, the security is also a paramount. Not being a DBA did not make your data platform secure. You must ensure necessary users only will have member of SYSADMIN privileges on the SQL Server, that will be used in case you are not available.

Talking on High Availability (HA) and Disaster Recovery (DR) SQL Server 2000 onwards log shipping helping as cold-standby and since 2005 version Database Mirroring offered closer to failover clustering capability (poor man’s clustering). Both has been enhanced in 2008 & 2008 R2 versions, but now 2012 HA solutions aims higher with Availability groups and robust features.

• Perform periodic disaster recovery – failover and failback – tests on the database platform.
• For High Availability and Disaster Recovery purposes, it is ideal to consider Database Mirroring and Log Shipping on mission-critical databases. In case of high Recovery Point Objective, failover clustering is the best option.
• Schedule the Log backup before the full backup. If you schedule the Log backup before the full backup, you will only have to restore the last full backup and one transaction log.
• Scheduling after the full backup will demand the restoration of the last full backup and two transaction logs backups. 
• Establish an Auditing Policy on the Backup Folder.
• Enabling file access auditing to the Backup Folder will monitor user access to the backups. 
• After the database is backed up in a file, compress and encrypt the files before moving the contents to tape backups or other forms of long-term storage.

The handy reference links:

• http://www.microsoft.com/sqlserver/2008/en/us/high-availability.aspx
• http://sqlcat.com/tags/Availability/default.aspx
• http://blogs.msdn.com/psssql/
• http://blogs.technet.com/dataplatforminsider/default.aspx

More information for highly tested recipes from real-world experience & best practices, refer to SQL Server 2008 R2 Administration cookbook.

What this book covers

Chapter 1

, Getting Started with SQL Server 2008 R2, begins with SQL Server 2008 R2 version’s new features and enhancements such as master data services, data-tier applications, and adding the service pack features using Slipstream technology. We will run through the designing best practice approach in upgrading to SQL Server 2008 R2 and take advantage of federated servers enhancements.

2

Chapter 2

, Administrating the Core Database Engine, covers the new SQL Server 2008 R2 feature utility administration and enhancements within DBA tasks such as availability, security, performance monitoring, tuning, and automated administration practices. The recipes also emphasize the usage of SQL Server Agent scheduled jobs for ETL and replication processes.

Chapter 3

, Managing the Core Database Engine, enables the user to produce a resilient data platform, which is possible with new features of SQL Server 2008 R2 such as Utility Control point, multi-server management, and implementing central management feature enhancements. The recipes cover the key best practices that combine a streamline of services for deployment, migration, and management of data platform.

Chapter 4

, Administering core Business Intelligence Services, emphasizes on the process and procedures in preparing, installing, and implementing scale-out deployment of core business intelligence services—Analysis Services (AS), Reporting Services (RS), and Integration Services (IS), and finally key practices in managing the ETL process efficiently.

Chapter 5

, Managing Core SQL Server 2008 R2 Technologies, dives into the implementation phases of SQL Server 2008 R2 new features and essential steps in planning and implementing self-service BI services, SQLAzure connectivity, StreamInsight technologies, and deploying the master data services feature. These recipes involve the planning, design, and implementation of features that are important to the management of the core technologies.

Chapter 6

, Availability and Programmability Enhancements, is the biggest among all the chapters, and it covers the key SQL Server 2008 R2 availability enhancements within failover clustering, database mirroring, log shipping, and replication. The recipes also highlight the programmability enhancements such as compressed storage feature, data partitioning solutions, sparse columns, spatial data storage methods, and management of data-tier applications.

Chapter 7

, Implementing New Manageability Features and Practices, spotlights the new manageability features and practices such as auditing, security, compression, change tracking, policy-based management, transparent data encryption, implementing best practices analyzer, and PowerShell for SQL Server features.

Chapter 8

, Maintenance and Monitoring, is based on the typical day-to-day tasks of a DBA. It will go through the the aspects of maintenance and monitoring the best practices with real world examples. The recipes will help the DBAs to adopt best methods with available technologies to keep up the database functionality at peak performance.

Chapter 9

, Troubleshooting, covers the brainier task of administration which helps the users to solve the problems proactively, and this chapter spotlights the essential chores of troubleshooting. The recipes begin with monitoring methods and various available features such as filtered indexes, table hints, and query hints, and perform parallel query processing methods by taking advantage of hardware and system resources.

Chapter 10

, Learning the tricks of the trade, emphasizes on the important aspects of configuration, administration, and management of the core data platform.

Appendix

, More DBA Manageability best practices, contains a list of DBA Best Practices that are important to handle day-to-day tasks of the SQL Server 2008 R2 environment.