Physical architecture of Microsoft Business Intelligence Solution

I. Physical Infrastructure

Before developing a bi project, you must consider the allocation and placement of key servers and the services installed in the development, testing, and production environments. Although mssqlservice2008 installed all bi components on a physical server, this was rarely done in a production environment.

1.1 create an accurate baseline survey

* Physical server name, actual location, IP address of all network interface cards, and domain members.

* The operating system configurations, operating system versions, installed service packages, Administrator Logon creden。, installed core operating system services, and effective group policy object settings for each physical server.

* Logical servers and services installed on each physical server.

* Development tools.

* Example and optional download.

1.2 evaluate the current SLA

SLA is increasingly used to provide higher predictability in the IT infrastructure. If the Organization has adopted the SLA, the table in the review should be part of the baseline survey. Of course, our goal is to improve the report performance by introducing BI solutions in the existing environment.

If the company does not adopt the SLA, you can consider establishing a bi-specific SLA in the Bi project. An important reason for doing so is to lay the foundation for project acceptance as soon as possible in the project. Creating an SLA also creates a high level of evaluation standards. A simple example is to evaluate the appropriate query time. Then add the measurement standard. For example, you can describe the measurement standard as follows: "The query time cannot exceed 3 s under normal load (no more than 1000 concurrent connections ." What if there is no SLA or plans to use it. You can still evaluate the existing operating environment and create meaningful baseline information. This evaluation should at least briefly list the difficulties related to data and reports. At the beginning of the project, you can use this information to form the Problem description section of the Specification document. Of course, eliminating or significantly reducing these difficulties is the ultimate goal of the Bi project. Microsoft recommends that you use the reliability and performance monitoring tool of windows to understand the current situation. After SSAs is installed, if you continue to collect information about SSAs, multiple groups of counters dedicated to it can be used. The names of these counters are in the format of MSAs 2008: <counter Name>. After SSIS or SSRS is installed, the performance counters specific to them are also added.

Here we need to consider the possible problems:

* The current OLTP data is far optimized, resulting in slow report presentation.

* Slow query execution is caused by unoptimized OLTP queries or poor optimization.

* Core OLTP services are overloaded due to competition between OLTP and concurrent reporting activities. This overload may be related to contention for CPU, memory, disk access, or their combination of contention.

* The OLTP maintenance window is too short due to long-running backup jobs and other maintenance tasks.

After completing the baseline environment survey, the next step is to set the number of physical servers required to initialize the development environment. There are two ways to perform this operation: the first is to first establish a development environment, establish or upgrade the production environment in the future, and the second is to build all the environments at a time, including development, testing and production environments. Most companies choose to buy one or two new servers as the development environment and upgrade as the project progresses.

2. determine the optimal number and location of servers

When planning a new server, consider the Logic Server and service and the following points:

* Source System.

* Analusis services. Where can I install the core bi services? When creating a development environment, sqlservice and Analysis Service are usually installed on the same machine.

* Integration services. Where can I install this service? The most common configuration is to install the ssis component of the sqlservice instance on an independent physical server to reduce contention on the SSAS server.

* Reporting Service. In the production environment, install SSRs on a dedicated server.

The best configuration is to install SSAs and sqlservice on the first server and SSRs on the second server. (Currently, I personally agree with this configuration. In actual work, Microsoft Bi has been installed on the same server, resulting in slow report Presentation and 64 GB memory! .)

This article is from the "Tears alone" blog and is not reposted!