Microsoft SQL Server 2012 Consulting: Planning, Deployment, Software Development, Troubleshooting
SQL Server 2012 is the latest incarnation of Microsoft's primary database management platform. This SAP-certified and cloud-ready database server delivers enterprise-class availability, performance, expandability, and security while offering native integration with Microsoft SharePoint and Office plus comprehensive support for Microsoft's popular family of application development tools. Significant enhancements over previous versions of SQL Server include the virtual elimination of planned and unplanned downtime via Microsoft's integrated AlwaysOn high-availability/disaster recovery (HADR) technology, ultra-fast performance with xVelocity in-memory technologies for Data Warehousing and business intelligence, and managed self-service analytics and reporting with PowerPivot and Power View.
Progent offers affordable online and on-premise consulting services for SQL Server 2012 that cover all facets of an advanced database management solution by providing services that include database administration (DBA) and project management outsourcing and co-sourcing, database application development, data and application migration services, server and client configuration and troubleshooting, multi-site network infrastructure design and implementation, security and compliance expertise, virtualization strategies, and disaster recovery/business continuity planning.
Availability Enhancements: Microsoft's AlwaysOn HADR Solution
Prior to the release of SQL Server 2012, Microsoft's solution for high-availability and disaster recovery (HADR) for SQL Server involved using a combination of separate techniques such as database mirroring, clustering, and log shipping. The AlwaysOn HADR solution introduced in SQL Server 2012 integrates these individual mechanisms into a cohesive environment that is easier to configure, manage, and monitor. Key components of SQL Server 2012 AlwaysOn include AlwaysOn Availability Groups, AlwaysOn Failover Cluster Instances, AlwaysOn Active Secondaries, AlwaysOn Availability Groups Listener Capabilities, and AlwaysOn AutoStat.
AlwaysOn Availability Groups (AG) is Microsoft's innovative approach to database mirroring to ensure the uptime of application databases. AG offers a variety of options that include manual and automatic failover of a specified group of databases, the ability to have as many as four Secondaries, instant application failover, and automatic page repair. An availability group is a container for a defined set of user databases, referred to as availability databases, that failover together as a single logical entity. An AlwaysOn Availability Group can have multiple failover targets, or secondary replicas, which you can set up to allow read-only access to secondary databases and as backups for secondary databases. This eliminates the need for shared SAN or NAS disk storage for creating a Failover Cluster Instance.
The AlwaysOn Failover Clustering Instances feature in SQL Server 2012 offers a significant enhancement to the database mirroring capability of SQL Server 2008 by allowing log-based data movement without shared disks and thereby enabling zero data loss in case of a fail over condition. AlwaysOn Availability Groups improve database availability as well as resource utilization by allowing you to define sets of user databases that fail over as a single logical unit and to create a set of primary and secondary availability replicas to host copies of each availability database. Availability Groups can have up to four secondary replicas and feature listeners for fast application failover over, automatic page repair to protect against page corruption, and the ability to force a Windows Server Failover Clustering (WSFC) quorum. To improve hardware utilization and productivity, secondary replicas can be used actively for read-intent applications and backup.
AlwaysOn Active Secondaries allow you to use secondary instances for report queries and backup. This permits more efficient use of server resources and improves overall system performance.
AlwaysOn Availability Groups Listener Capabilities include the ability of client applications to failover across up to 64 AlwaysOn subnets. Also new is read-only intent, which provides the flexibility to specify the workloads that run on high-availability servers and the ability to manage resources efficiently.
Other availability enhancements included with SQL Server 2012 include Windows Server Core Support to reduce patching and OS reboots by up to 60%, improved support for online operations such as online index build to allow database maintenance without downtime, default values for application upgrades to avoid long-term exclusive table locks, and Split File Backup for faster and more manageable backup operations.
Enhancements for Managed Self-Service Business Intelligence
SQL Server 2012 provides a managed self-service business intelligence environment that allows users at any skill level to analyze data from multiple sources, create interactive, tabular, graphical, or free-form reports, and securely share presentations and reports with colleagues. To accomplish this, SQL Server 2012 introduces Power View for interactive web-based data discovery and visualization, improves PowerPivot with enhanced analytic capabilities, and adds a powerful end-user alerting feature to SQL Server Reporting Services
PowerPivot provides a managed self-service business intelligence solution that uses the popular Excel interface for complex data aggregation and analysis and Microsoft SharePoint to publish reports securely and collaborate with team members across the organization. PowerPivot can work with data pulled from many sources including various database platforms, SQL Server Reporting Services, Excel data, text files, and the web. PowerPivot's integration with SharePoint not only facilitates collaboration but also provides the benefits of SharePoint's support for role-based security, compliance policies, workflows, scheduled refreshes, and user-defined alerts. New features of PowerPivot include the ability to create Key Performance Indicators (KPIs), high-end sorting options, perspectives, advanced business logic, Top N, hierarchies, perspectives, plus the ability to build Excel-type analytical functions with Data Analysis Expressions (DAX).
Power View is a new feature of SQL Server 2012 that allows users to perform fast web-based data analysis to understand the business implications of massive amounts of information from multiple sources by interactively visualizing the relationships between different tables in the underlying Business Intelligence Semantic Model. Power View simplifies information sharing and allows users to preview and publish presentations instantly to SharePoint Server so stakeholders can interact with the information. To enhance performance, Power View extracts only the data currently needed for visualization. Users can launch Power View over a PowerPivot model or a tabular model running on SSAS directly from a browser.
SQL Server Reporting Services End User Alerting is a new tool to increase information worker (IW) productivity when working with Reporting Services reports published to SharePoint. The Alert runs in the background and monitors data changes, sending an Alert Message when triggered. The Alert trigger is based on a customizable data condition set by the IW, thereby increasing responsiveness to important data changes.
Increased Performance and Capacity
SQL Server 2012 offers significantly higher performance and improved scalability thanks to the addition of features such as xVelocity in-memory technologies as well support for more resource pools, higher CPU utilization, expanded table partition support, and the ability to accommodate up to 250 logical cores.
SQL Server 2012's new xVelocity technology combines an in-memory column store index with enhanced in-memory compression and a new paradigm for vector-oriented SQL query handling to deliver dramatically improved performance for typical data warehouse queries.
Analysis Services has also been enhanced by xVelocity technology to improve the performance of SQL Server 2012 Business Intelligence (BI) by supporting scan rates of 10s of billions of rows per second on conventional hardware.
Resource Governance for concurrent and mixed workloads has been improved in SQL Server 2012 to allow faster, more predictable performance for critical database applications. Administrators can assign critical workloads a percentage of performance for a given resource. To provide more flexibility for managers to assure performance for specific applications, SQL Server 2012 increases maximum number of resource pools from 20 to 60 and also increases the maximum capacity setting on CPU utilization.
Full-text search performance has been dramatically enhanced in SQL Server 2012 through improved query execution and concurrent index update techniques. In addition, an improved NEAR operator and updated word-breakers enhance the functionality of full-text search in SQL Server 2012.
Table Partitioning in SQL Server 2012 has been expanded from 1,000 to 15,000, enabling large sliding windows capability. This allows snapshot-intensive applications such as SAP to hold data longer before having to push it out to clear the way for new data. The result is improved performance, simpler management of large amounts of data, and streamlined data warehouse maintenance.
Extended Events, the general event-handling infrastructure for SQL Server 2012, has enhanced event selection, logging and filtering for profiling information and a new user interface to simplify the work required for performance tuning such as analyzing query execution plans, capturing database activity, and optimizing indexes and structures.
Security and Compliance Improvements
Improvements to SQL Server 2012 that strengthen security and facilitate compliance include audit enhancements, default schema for Windows Groups, user-defined server roles, contained database authentication, alignment with SharePoint and Active Directory security models, and integration with System Center Operations Manager for security and health monitoring.
Audit Enhancements help organizations meet government and industry compliance regulations by supporting user-defined audits that allow an application to enter custom events into the audit log, audit filtering to prevent the audit log from filling up with relevant events, and the ability to recover auditing data from temporary file and network issues.
Cryptography enhancements in SQL Server 2012 include the ability to create certificates from bytes, default for Server Master Key (SMK), Database Master Key (DMK), backups key using AES256, expanded support for SHA2 (256 and 512), and the use of SHA512 for password hashes.
Default Schema for Windows Groups allows administrators to tie database schema to Windows Group instead of individual users, streamlining schema management and avoiding query errors due to the assignment of wrong schema to users who change groups.
Support for User-Defined Server Roles allows the creation of new server roles in order to delegate the responsibility for managing data access permissions to various administrators based on assigned roles. This feature reduces reliance on sysadmin for database administration and allows server roles to be nested for flexibility in mapping according to an organization's hierarchical structures.
Contained Database Authentication makes it possible to store user login information directly in user databases rather than master login. This improves compliance by permitting users to perform Data Manipulation Language (DML) operations only within the user databases rather than database instance-level operations. This also reduces orphaned or unused logins in the database instance. AlwaysOn utilizes contained database authentication to improve the portability of user databases among servers by eliminating the need to configure logins for all database servers in the cluster in case of a failover.
Alignment with SharePoint and Active Directory Security Models helps protect a user's data analytics reports published in SharePoint and provides integrated security control at row and column levels.
SQL Server 2012 Consulting and Support Services Available from Progent
Progent offers cost-effective remote and on-premise consulting and support services to help you derive maximum business value from your SQL Server 2012 environment. Consulting and support services available from Progent include:
Evaluate the Value of SQL Server 2012 for Your Organization: Progent can provide the support of experienced IT professionals who have held executive positions with organizations ranging from Big 4 firms to dot-com startups to state government agencies. Progent's part-time chief information officers can work with you to create TCO and ROI models to help you evaluate the risks and benefits of adopting SQL Server 2012.
Outsourced and Co-sourced Project Management Support: Progent's project management experts can help you follow industry best practices to complete major SQL Server 2012 projects on time and within budget by providing services such as preparedness analysis, requirements definition, system architecture, testing under realistic conditions at your site or at Progent's piloting lab, performance optimization, project documentation, and webinar or on-premise training.
Database Administration Expertise: Progent's database administration experts are available for you to outsource or co-source DBA responsibilities for SQL Server 2012. Skills available from Progent's seasoned DBA experts include database tuning, information security strategies, automating client configuration and account management, backup and restore solutions, managing patches and upgrades, system monitoring, and database consolidation.
Business Analytics and Reporting: Progent's management reporting specialists can show you how to deploy SQL Server tools like SSRS and Report Builder for SQL Server 2012 to create and securely share Business Intelligence reports. Progent can help you use new reporting utilities like PowerPivot and Power View to create ad hoc reports, or assist you to use SQL Server 2012 databases to create reports with more traditional management reporting platforms such as Crystal Reports, FRx, and Report Writer.
Application Development: Progent's database application developers can assist you to plan, create, test, support, enhance, or troubleshoot mission-critical database applications based on SQL Server 2012. Progent can help with every facet of your application's life cycle via services that include requirements analysis, logical and physical modeling, ETL development, programming with T-SQL and other popular development tools, partitioning design, plus synchronization and replication strategies.
Multi-site Network Design: Progent's Cisco-certified CCIE network consultants can show you cost-effective ways to create and maintain a safe, fault-tolerant, and high-performance network architecture that accommodates multiple sites as well as local, remote, and mobile users.
Security and Compliance Consulting: Progent's data security and compliance experts have earned major security certifications including CISA, CISSP-ISSAP, and CRISC. Progent can provide a variety of security assessment and threat mitigation services to guard sensitive data and resources and help you comply with industry security standards and regulatory requirements.
Virtualization Strategies: Progent's Hyper-V experts can help you save money and improve database availability by deploying Windows Server 2008 R2 Hyper-V to virtualize SQL Server 2012 servers. This allows you to benefit from the Live Migration capability of Hyper-V, which can enhance the availability of SQL Server databases by allowing you to take a physical host offline for maintenance without interfering with users who are running database applications.
Operations Manager-based Monitoring: Progent's System Center Operations Manager experts can help you install and configure the Monitoring Pack for SQL Server 2012, which enables Operations Manager 2007 R2 or Operations Manager 2012 to monitor the performance, uptime, security, and configuration of SQL Server 2012, collect performance data, and take advantage of key thresholds to provide system managers with early warnings about issues that could impact SQL Server 2012 performance.
Business Continuity Planning: Progent's senior business continuity planning consultants can assist you to develop, test, and document a comprehensive business continuity plan that addresses not only the prompt restoration of your SQL Server database applications and other IT functions but also covers issues related to business logistics such as emergency notification systems, alternate office space and transportation, and regulatory reporting. In addition, Progent's disaster recovery consultants can help you create and validate a practical plan to restore basic SQL Server operations with minimal downtime and little or no data loss after a catastrophic event.