SQL Server---a Technical Reference Guide for Designing Mission-Critical OLTP and DW Solutions
Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.
The Microsoft SQL Server database management system (DBMS), as a result of continual enhancements, has matured into a platform proven to be capable of handling enterprise-level workloads. Data warehousing (DW), business intelligence (BI), and operational workloads are made possible by the availability of table partitioning, online indexing operations, Fast Track reference architectures, the Parallel Data Warehouse (PDW) appliance, self-service BI, and by the management of non-structured data types (such as spatial data or binary large objects [BLOBs]), and discrete files. Both scale-up and scale-out solutions are available for operational systems, as well as the potential of adopting a public cloud solution, Microsoft SQL Azure. While no relational database management system (RDBMS), including SQL Server, is risk-free for a large mission-critical system, having an appropriate architecture, design, development, and execution environment that takes advantage of the features and functionalities available in SQL Server can help you to achieve your business objectives.
This guide provides helpful pointers for the initial discussions with the customers’ architecture and design team, and for the design and deployment phases.
The following resources provide examples of customer scenarios for implementing SQL Server RDBMS, in addition to general SQL Server RDBMS reference material. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)
Review Using SQL Server to Build a Hub-and-Spoke Enterprise Data Warehouse Architecture1 to understand how to effectively use various solutions together for DW.
The article An Introduction to Fast Track Data Warehouse Architectures2 provides an overview of SQL Server Fast Track DW.
The Data Warehouse Lifecycle Toolkit,3 by Ralph Kimball, is an extremely useful book to help you understand the complexities of process modeling and to help you architect systems that address business requirements.
For a formal reference on online transaction processing (OLTP) systems, consider reading Transaction Processing: Concepts and Techniques.4
Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications
5 discusses the significance of input/output (I/O) characteristics on SQL Server database applications.
View High Performance SQL Server Workloads on Hyper-V6 to see if virtualization is appropriate for your expected workload.
The article SQL Azure Customer Best Practices7 contains useful advice for customers who are interested in utilizing SQL Server capabilities in an off-premises mode. For a comparison of SQL Azure and SQL Server, refer to the Microsoft SQL Azure FAQ.8
Case Studies and References
The following case studies and references show how SQL Server is effectively deployed by customers:
Global Online Gaming Company Deploying SQL Server 2008 to support 100 Terabytes
First American Title Insurance Runs Mission Critical Application on SQL Server 2008
SQL Server 2008 R2 Helps Credit Card Company Scale and Secure Mission-Critical Systems
How Microsoft IT Uses SQL Server 2005 to Power a Global Forensic Data Security Tool
Credit Card Company Runs its Business with 17-Terabyte Mission Critical BI Solution
Questions and Considerations
This section provides questions and issues to consider when working with your customers.
Determine at a high level if the system will be predominantly an OLTP or DW system. Systems are seldom pure OLTP. Most systems also include some form of reporting and data store, often called a reporting DB, an open data service (ODS), or a data mart. Understand user requirements and use scenarios, including the number of users and peak transactions. Also understand the performance, scalability, high availability (HA), and disaster recovery (DR) requirements, including the time windows available (if any) for software and hardware maintenance and upgrades.
Decide which platform is best-suited for DW (for example, custom design, SQL Server Fast Track, or PDW) and similarly decide on operational systems (for example, high-end appliance or a custom approach). Evaluate whether Windows Azure or SQL Azure can be used to meet system needs.
For large, mission-critical applications, in addition to implementing best practices for scale-up architecture, evaluate whether scale-out architecture is needed. Scale-out solutions may provide additional scalability options at potentially lower cost than scale-up in some cases. Be aware that scale-up solutions are more commonly deployed because they are easier to develop and because more development experience exists for them. Scale-out solutions often require more design effort, but can provide scalable solutions at potentially lower cost. For example, as the user base of a large, well-known social web site quickly grew within a few years to hundreds of millions, at least five solutions were deployed. When the current scale-out solution was finally implemented, it proved to be robust enough for the social site's needs.
Success depends on strategy, people, process, and technology. SQL Server is only one component of solution architecture; other components are equally important. A thorough understanding of the deployment and operational environment is required for long-term success.
Following are the full URLs for the hyperlinked text.
1 Using SQL Server to Build a Hub-and-Spoke Enterprise Data Warehouse Architecturehttp://msdn.microsoft.com/en-us/library/dd458815(SQL.100).aspx
2 An Introduction to Fast Track Data Warehouse Architectureshttp://msdn.microsoft.com/en-us/library/dd459146(SQL.100).aspx
3 The Data Warehouse Lifecycle Toolkit by Ralph Kimblehttp://www.amazon.com/Data-Warehouse-Lifecycle-Toolkit/dp/0470149779
4 Transaction Processing: Concepts and Techniqueshttp://www.amazon.com/Transaction-Processing-Concepts-Techniques-Management/dp/1558601902/ref=cm_cr_pr_product_top
5 Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applicationshttp://sqlcat.com/whitepapers/archive/2010/05/10/analyzing-i-o-characteristics-and-sizing-storage-systems-for-sql-server-database-applications.aspx
6 High Performance SQL Server Workloads on Hyper-Vhttp://sqlcat.com/whitepapers/archive/2010/05/27/high-performance-sql-server-workloads-on-hyper-v.aspx
7 SQL Azure Customer Best Practiceshttp://sqlcat.com/msdnmirror/archive/2010/05/28/sql-azure-customer-best-practices.aspx
9 Global Online Gaming Company Deploying SQL Server 2008 to support 100 Terabyteshttp://www.microsoft.com/casestudies/Microsoft-SQL-Server-2008/bwin/Global-Online-Gaming-Company-Deploying-SQL-Server-2008-to-support-100-Terabytes/4000001470
10 First American Title Insurance Runs Mission Critical Application on SQL Server 2008http://www.microsoft.com/casestudies/Microsoft-Windows-Server-2003-Datacenter-Edition-for-Itanium-Based-Systems/First-American-Title-Insurance-Company/First-American-Title-Insurance-Runs-Mission-Critical-Application-on-SQL-Server-2008/4000007625
11 SQL Server 2008 R2 Helps Credit Card Company Scale and Secure Mission-Critical Systemshttp://www.microsoft.com/casestudies/Microsoft-Office-SharePoint-Server-2007/PREMIER-Bankcard-LLC/SQL-Server-2008-R2-Helps-Credit-Card-Company-Scale-and-Secure-Mission-Critical-Systems/4000007028
12 How Microsoft IT Uses SQL Server 2005 to Power a Global Forensic Data Security Toolhttp://sqlcat.com/spotlights/archive/2008/01/24/how-microsoft-it-uses-sql-server-2005-to-power-a-global-forensic-data-security-tool.aspx
13 Credit Card Company Runs its Business with 17-Terabyte Mission Critical BI Solutionhttp://www.microsoft.com/casestudies/Microsoft-Office-SharePoint-Server-