The design of a solution needs guidance and planning, that has a series of collection consisting of information such as infrastructure planning, design, and implementation process which leads the sequence of core decision points. Designing the SQL Server 2008 R2 is no different to the key steps mentioned preciously. It also provides a means of validation to design and deploy the key business decisions to ensure that the solution meets the near-to-clear requirements for both technology and business stakeholders.
The design and adoption program must include technologists, such as Architects, to have an operational grasp of the technology to make the best use of new features. Also, the business team involvement is a high-level need to ensure that they understand the technology decisions being made for development and support. This can affect the business.
SQL Server 2008 R2 contains new features within the Business Intelligence stack such as PowerPivot, StreamInsight, and Master Data Services. A series of technology adoption program needs to be designed from an IT and Business perspective. A step beyond the traditional technology provision and SQL Server 2008 R2, consists of a series of best practices to implement the real-world BI solutions to leverage the best use of technologies.
The adoption programs should be designed to help users understand how the new SQL Server features interact with established technologies such as SQL Server Analysis Services, or SQL Server Integration Services, or SQL Server Reporting Services. In this recipe, we will go through the required process in designing and adopting the SQL Server 2008 R2 solutions.
The guidance must include the activities of planning a design, understanding new features, and implementing them effectively. In this case, SQL Server 2008 R2 new features. The critical design elements of planning are project scope determination, roles of people (such as dividing the technology and business decision-maker levels and infrastructure adoption of SQL Server 2008 R2 technologies), core database engine, integration services, analysis services, reporting services, and master data services.
Each user and stakeholder's needs should be classified taking the following considerations into account:
Organizations that are implementing SQL Server for a specific application
Organizations that are implementing SQL Server to provide a business intelligence platform for managers and decision makers to conduct data integration, reporting, and analysis
Organizations that are implementing SQL Server as a service that is expected to be available to multiple business units
Organizations that are upgrading an existing SQL Server implementation by migrating to new hardware
The following set of processes is involved in designing and adopting specific SQL Server 2008 R2 solutions:
1. In a typical application fetching data from a database server approach, the SQL Server infrastructure is designed to support a specific application.
2. This approach involves gathering the requirements about the application, determining which SQL Server roles will be required, and optimizing the server design for those specific applications.
3. Another approach is to design Database as a Service that makes the easy availability of new features for general platform usage.
4. Now the important aspect of design: Before proceeding to the implementation of database infrastructure, the aspect of storage and performance requirements will need to be gathered.
5. So the core service for storing, processing, and securing data is catered through the database engine that supports both relational (OLTP) and analytical (OLAP) methods for desktop requirements to data warehouse databases.
6. If the application being implemented requires one or more OLTP databases, then the same process of collection of performance requirement must be repeated.
Other services that may be selected in later tasks could determine the need for the Database Engine, such as:
Reporting Services: It requires access to SQL Server-based database server to store metadata; this metadata database can be on the same server as Reporting Services, or on another database server. Integration Services can store packages in the msdb database which is the system database—or on the filesystem.
Storage location: It will be determined by the outcome of the decision of whether or not a database may be required for Integration Services. The inner process of Integration Services are required, such as:
Merge data from heterogeneous data sources
Populate data warehouses and data marts
Cleanse and standardize data
Automate administrative functions and data loading
For Data Warehouse, SQL Server Analysis Services (SSAS) supports OLAP (online analytical processing) and data mining functionalities. This allows a database administrator to design and create multidimensional structures that contain data aggregated from other data sources, such as relational databases.
Analysis Services may be needed if the organization needs to rapidly access reports with varying degrees of granularity (for example, yearly totals, monthly totals, quarterly totals, and individual transactions) and requires that the yearly totals appear just as quickly as the daily totals.
An online transaction processing (OLTP) system would have to add up these values, while an SSAS system, in many cases, will already have the answers pre-calculated.
SQL Server Reporting Services (SSRS) delivers enterprise reporting functionality for creating reports that gather content from a variety of data sources, publishing the reports in various formats, and centrally managing their security and subscriptions. Reports can be delivered on a scheduled basis to users, accessed through a web interface from SharePoint, or from custom applications including Windows Forms applications that call through web services.
Reporting Services can be used to generate reports on OLTP databases, SSAS cubes, data warehouses, data marts, or third-party data sources such as flat files, Oracle databases, or web services.
There are potential reasons why a database might not be located within an existing instance:
Regulatory requirements: Corporate policies or government regulations may require separate storage for certain types of data or for certain parts of the organization.
Memory isolation: There may be a database application with a memory leak. It's therefore desirable to place it in its own instance and cap the memory available to this instance to protect other databases on the server from being affected.
Fault tolerance: Clustering is on the instance level; thus, it will be necessary to place a database in an instance with similar fault-tolerance requirements.
Authentication: Authentication is set at the instance level to be either an SQL Server authentication or a Windows authentication. The new database's authentication requirements need to be compatible with the instance's authentication setting.
Security concerns: Total database encryption can be used to add security to a database. The service master key is for all databases in an instance. To minimize exposure, if a key is compromised, it may be desirable to place the database in a separate instance.
Support requirements: The vendor or developer may require that the database runs in a separate instance.
Two factors that may change the number of servers running SQL Server are:
Whether or not scaling out will be implemented
Whether fault tolerance will be implemented at the database level or instance level
Scaling out is the process of distributing an SQL Server load across multiple servers; thus, the decision to scale out will increase the number of servers required. Multiple scale-out options are available, and this section will provide a brief overview. Scale-out options require design changes that occur at the database level and are outside the scope of this guide. It is expected that any decisions relative to scaling out should be made in conjunction with the database administrator.
The detailed information on the installation and configuration process is discussed in Chapter 5, Management of core SQL Server 2008 R2 technologies recipes:
Designing Data Integration Solution for Master Data Management
Designing and Deploying framework to use Master Data Services
Troubleshooting SQL Server Master Data Services