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.

Best Practices

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.)

Case Studies and References

The following case studies and references show how SQL Server is effectively deployed by customers:

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.

Appendix

Following are the full URLs for the hyperlinked text.

1 Using SQL Server to Build a Hub-and-Spoke Enterprise Data Warehouse Architecturehttps://msdn.microsoft.com/en-us/library/dd458815(SQL.100).aspx

2 An Introduction to Fast Track Data Warehouse Architectureshttps://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

8 Microsoft SQL Azure FAQhttps://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=ffc9536c-c548-453a-92b8-c6614c63a26e

9 Global Online Gaming Company Deploying SQL Server 2008 to support 100 Terabyteshttps://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 2008https://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 Systemshttps://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 Solutionhttps://www.microsoft.com/casestudies/Microsoft-Office-SharePoint-Server-