Planning a deployment of SQL Server 2022, in order to best support the business’s needs, can be a complicated task. You should make sure to consider many areas, including edition, licensing requirements, on-premises vs. cloud hosting, hardware considerations, software configuration, and even if Windows is the best platform. For example, if your new instance will be supporting a PHP Web App, hosted on Linux, then maybe your instance should also be hosted on Linux? And all of this is before you even start to consider which features of SQL Server you may need to install to support the application.
This chapter will guide you through the key decisions that you should make when you are planning your deployment. You will also learn how to perform some essential operating system configurations, should you decide to host your instance on Windows Server. This chapter will also give you an overview of the top-level features that you can choose to install and discuss why selecting the appropriate features is important.
Editions and License Models
Choosing the edition of SQL Server 2022 to support your data-tier application may sound like a simple task, but in fact, you should spend time thinking about this decision and consulting with both business stakeholders and other IT departments to bring their opinions into this decision. The first thing to consider is that there are five editions of SQL Server. These editions not only have different levels of functionality, but they also have different license considerations. Additionally, from an operational support perspective, you may find that the TCO (total cost of ownership) of the estate increases if you allow data-tier applications to be hosted on versions of SQL Server that are not deployed strategically within your estate.
SQL Server Edition License Models
Edition | License Model(s) | Comments |
|---|---|---|
Enterprise | Per-core | – |
Standard | • Per-core • Server + CAL | – |
Web | Third-party hosting only | – |
Developer | Free for noncommercial use | Not for use in a production environment |
Express | Free edition of SQL Server | Limited functionality and small capacity limits, such as a 10GB database size, a 1GB limit on RAM, and a CPU limit of one socket, or four cores |
A CAL is a client access license, where a client can refer to either a user or a device. You can choose whether to purchase user or device licenses based on which will be cheapest for your environment.
For example, if your organization had a SQL server that was supporting a call center that had 100 computers, and it ran 24/7 with three eight-hour shifts, then you would have 100 devices and 300 users, so device CALs would be the most sensible option for you to choose.
On the flip side, if your organization had a SQL server that was supporting a sales team of 25 who all connected to the sales application not only via their laptops, but also via their iPads, then you would have 25 users, but 50 devices, and therefore choosing user CALs would be the more sensible option.
SQL Server Edition Overview
Edition | Edition Overview |
|---|---|
Enterprise | Fully featured edition of SQL Server for Enterprise systems and critical apps |
Standard | Core database and BI functionality, aimed at departmental-level systems and noncritical apps |
Web | Is only available for service providers hosting public websites that use SQL Server |
Developer | A fully featured edition, to the level of Enterprise edition, but meant for development use and not allowed for use on production systems |
Express | A free, entry-level version of SQL Server geared toward small applications with local data requirements |
The version(s) of SQL Server that you choose to support in your Enterprise applications will vary depending on the project’s requirements, your organization’s requirements, and the underlying infrastructure. For example, if your organization hosts its entire SQL Server estate within a private cloud, then you are likely to only support the Enterprise edition, since you will be licensing the underlying infrastructure.
Alternatively, if your organization is predominantly utilizing physical boxes, then you most likely need to support a mix of SQL Server versions, such as Enterprise and Standard editions. This will give projects the flexibility to reduce their costs if they only require a subset of features and are not expecting high-volume workloads, and hence can live with the limits that Standard edition imposes on RAM and CPU.
The next thing you should consider before choosing which edition you will use is whether or not you will use a Windows Server Core installation of SQL Server. Installations on Server Core can help improve security by reducing the attack surface of your server. Server Core is a minimal installation, so there is less surface to attack and fewer security vulnerabilities. It can also improve performance, because you do not have the overhead of the graphical user interface (GUI) and because many resource-intensive applications cannot be installed. If you do decide to use Server Core, then it is also important to understand the impacts of doing so.
Reporting Services
SQL Server Data Tools (SSDT)
Client Tools Backward Compatibility
Client Tools SDK
SQL Server Books Online
Distributed Replay Controller
Master Data Services (MDS)
Data Quality Services (DQS)
Management Tools
Distributed Replay Client
From the broader perspective of operational support, you will need to ensure that all of your operational teams (DBAs, Windows Operations, etc.) are in a position to support Server Core. For example, if your DBA team relies heavily on a third-party graphical tool for interrogating execution plans, does this need to be installed locally on the server? Is there an alternative tool that would meet their needs? From a Windows operations perspective, does the team have the tools in place for remotely monitoring and managing the server? Are there any third-party tools they rely on that would need to be replaced?
You should also consider if your operations team has the skill set to manage systems using predominantly command-line processes. If it does not, then you should consider what training or upskilling may be required.
Hardware Considerations
When you are planning the hardware requirements for your server, ideally, you will implement a full capacity planning exercise so you can estimate the hardware requirements of the application(s) that the server will support. When conducting this exercise, make sure you take your company’s standard hardware life cycle into account, rather than planning just for today. Depending on your organization, this could be between one and five years, but will generally be three years.
This is important in order to avoid undersizing or oversizing your server. Project teams will generally want to oversize their servers in order to ensure performance. Not only is this approach costly when scaled through the enterprise, but in some environments, it can actually have a detrimental effect on performance. An example of this would be a private cloud infrastructure with shared resources. In this scenario, oversizing servers can have a negative impact on the entire environment, including the oversized server itself.
Specifying Strategic Minimum Requirements
When specifying the minimum hardware requirements for SQL Server within your environment, you may choose to specify the minimum requirements for installing SQL Server—4GB RAM and a single 2GHz CPU (based on Enterprise edition). However, you may be better served to think about operational supportability within your enterprise.
For example, if your environment consists predominantly of a private cloud infrastructure, then you may wish to specify a minimum of 2 vCores and 4GB RAM + (number of cores * 1GB) since this may be in line with your enterprise standards.
On the other hand, if you have a highly dispersed enterprise, which has grown organically, and you wish to help persuade projects to use a shared SQL Server farm, you may choose to enforce much higher minimum specifications, such as 32GB RAM and 2 sockets/4 cores. The reasoning here is that any projects without large throughput requirements would be “forced” to use your shared farm to avoid the heavy costs associated with an unnecessarily large system.
Storage
Storage is a very important consideration for any SQL Server installation. The following sections will discuss locally attached storage and SAN storage, as well as considerations for file placement.
Locally Attached Storage
If your server will use locally attached storage, then you should carefully consider file layout. By its very nature, SQL Server is often input/output (I/O) bound, and therefore, configuring the I/O subsystem is one of the critical aspects for performance. You first need to separate your user databases’ data files and log files onto separate disks or arrays and also to separate TempDB, which is the most heavily used system database. If all of these files reside on a single volume, then you are likely to experience disk contention while SQL Server attempts to write to all of them at the same time.
Typically, locally attached storage will be presented to your server as RAID (redundant array of inexpensive disks) arrays and various RAID levels are available. There are many RAID levels available, but the most common are outlined in the following pages.
RAID 0
A RAID 0 volume consists of between two and n spindles, and the data bits are striped across all of the disks within the array. This provides excellent performance; however, it provides no fault tolerance. The loss of any disk within the array means that the whole array will fail. This is illustrated in Figure 1-1.
In the diagrams in this section, the red X denotes a disk failure.
The RAID 0 array provides no redundancy
Because RAID 0 provides no redundancy, it should not be used for production systems.
RAID 1
RAID 1 provides redundancy by mirroring the disk
The formula for calculating the total IOPS (input/output per second) against a RAID 1 array is as follows: IOPS = Reads + (Writes * 2).
RAID 5
A RAID 5 volume will consist of between three and n spindles and provides redundancy of exactly one disk within the array. Because the blocks of data are striped across multiple spindles, read performance of the volume will be very good, but again, this is at the expense of write performance. Write performance is impaired because redundancy is achieved by distributing parity bits across all spindles in the array. This means that there is a performance penalty of four writes for every one write to the volume. This is regardless of the number of disks in the array. The reason for this arbitrary penalty is because the parity bits are striped in the same way the data is. The controller will read the original data and the original parity and then write the new data and the new parity, without needing to read all of the other disks in the array. This method of redundancy is illustrated in Figure 1-3.
RAID 5 provides redundancy through parity bits
The formula for calculating total IOPS against a RAID 5 array is as follows: IOPS = Read + (Writes * 4). To calculate the expected IOPS per spindle, you can divide this value for IOPS by the number of disks in the array. This can help you calculate the minimum number of disks that should be in the array to achieve your performance goals.
RAID 10
A RAID 10 volume will consist of four to n disks, but it will always be an even number. It provides the best combination of redundancy and performance. It works by creating a stripe of mirrors. The bits are striped, without parity, across half of the disks within the array, as they are for RAID 0, but they are then mirrored to the other half of the disks in the array. It is a combination of RAID 1 and RAID 0.
RAID 10 provides redundancy by mirroring each disk within the stripe
The formula for calculating total IOPS against a RAID 10 array is as follows: IOPS = Read + (Writes * 2). In the same way as for RAID 5, in order to calculate the expected IOPS per spindle, you can divide the value for IOPS by the number of disks in the array. This can help you calculate the minimum number of disks that should be in the array to achieve your performance goals.
File Placement
It is generally accepted that RAID 0 should not be used for any SQL Server files. I have known people to suggest that RAID 0 may be acceptable for TempDB files. The rational here is that a heavily used TempDB often requires very fast performance, and because it is re-created every time the instance restarts, it does not require redundancy. This sounds perfectly reasonable, but if you think in terms of uptime, you may realize why I disagree with this opinion.
- 1.
Wait for the storage team to bring the RAID 0 array back online.
- 2.
Start the instance in “minimal configuration mode” and use SQLCMD to change the location of TempDB.
By the time either of these steps is complete, you may find that stakeholders are jumping up and down, so you may find it best to avoid this option. For this reason, TempDB is generally best placed on a RAID 10 array, whenever possible. This will provide the best level of performance for the database, and because its size is significantly smaller than the user database files, you do not have the same level of cost implication.
In an ideal world, where money is no object, the data files of your user databases will be stored on RAID 10 arrays, since RAID 10 provides the best combination of redundancy and performance. In the real world, however, if the applications you are supporting are not mission critical, this may not be justifiable. If this is the situation, then RAID 5 can be a good choice, as long as your applications have a fairly high ratio of reads to writes. I would normally use a ratio of three to one in favor of reads as being a good baseline, but of course, it can vary in every scenario.
If your databases are only using basic features of SQL Server, then you will likely find that RAID 1 is a good choice for your log files. RAID 5 is not generally suitable, because of the write-intensive nature of the transaction log. In some cases, I have even known RAID 1 to perform better than RAID 10 for the transaction log. This is because of the sequential nature of the write activity.
AlwaysOn availability groups
Database mirroring
Snapshot creation
Backups
DBCC CHECKDB
Change data capture
Log shipping (both backups, and also if restoring logs WITH STANDBY)
Solid-State Drives (SSDs)
One common reason to use locally attached storage, as opposed to a storage area network (SAN), is to optimize the performance of SQL Server components, which require extremely fast I/O. These components include TempDB and buffer cache extensions. It is not uncommon to find that a database’s data and log files are stored on a SAN, but TempDB and buffer cache extensions are stored on locally attached storage.
In this example, it would make good sense to use SSDs in the locally attached array. Solid-state drives (SSDs) can offer very high I/O rates, but at a higher cost, compared to traditional disks. SSDs are also not a “magic bullet.” Although they offer a very high number of IOPS for random disk access, they can be less efficient for sequential scan activities, which are common in certain database workload profiles, such as data warehouses. SSDs are also prone to sudden failure, as opposed to the gradual decline of a traditional disk. Therefore, having a fault-tolerant RAID level and hot spares in the array is a very good idea.
Working with a SAN
Storage area network are three words that can strike fear into the heart of a database administrator (DBA). The modern DBA must embrace concepts such as SAN and virtualization; however, although they pose fundamental change, they also ease the overall manageability of the estate and reduce the total cost of ownership (TCO).
The most important thing for a DBA to remember about a SAN is that it changes the fundamental principles of the I/O subsystem, and DBAs must change their thinking accordingly. For example, in the world of locally attached storage, the most fundamental principle is to separate your data files, log files, and TempDB, and to ensure that they are all hosted on the most appropriate RAID level.
In the world of the SAN, however, you may initially be alarmed to find that your SAN administrators do not offer a choice of RAID level, and if they do, they may not offer RAID 10. If you find this to be the case, it is likely because the SAN is, behind the scenes, actually stripping the data across every disk in the array. This means that although the RAID level can still have some impact on throughput, the more important consideration is which storage tier to choose.
Many organizations choose to tier the storage on their SAN, offering three or more tiers. Tier 1 will be the highest tier and may well consist of a combination of SSDs and small, highly performing Fiber Channel drives. Tier 2 will normally consist of larger drives—potentially SATA (serial advanced technology attachment)—and Tier 3 will often use near-line storage. Near-line storage consists of a large number of inexpensive disks, such as SATA disks, which are usually stopped. The disks only spin up when there is a requirement to access the data that they contain. As you have probably guessed, you will want to ensure that any applications that require good performance will need to be located on Tier 1 of your SAN. Tier 2 could possibly be an option for small, rarely used databases with little or no concurrency, and Tier 3 should rarely, if ever, be used to store SQL Server databases or logs.
Your real throughput will be determined by these factors, but also many others, such as the number of network paths between your server and the SAN, how many servers are concurrently accessing the SAN, and so on. Another interesting quirk of a SAN is that you will often find that your write performance is far superior to your read performance. This is because some SANs use a battery-backed write cache, but when reading, they need to retrieve the data from the spindles.
Next, consider that because all of your data may well be striped across all of the spindles in the array—and even if it isn’t, the likelihood is that all files on a single server will probably all reside on the same CPG (common provisioning group)—you should not expect to see an instant performance improvement from separating your data, log, and TempDB files. Many DBAs, however, still choose to place their data, log, and TempDB files on separate volumes for logical separation and consistency with other servers that use locally attached storage. In some cases, however, if you are using SAN snapshots or SAN replication for redundancy, you may be required to have the data and log files of a database on the same volume. You should check this with your storage team.
Disk Block Size
Another thing to consider for disk configuration, whether it is locally attached or on a SAN, is the disk block size. Depending on your storage, it is likely that the default NTFS (New Technology File System) allocation unit size will be set as 4KB. The issue is that SQL Server organizes data into eight continuous 8KB pages, known as an extent. To get optimum performance for SQL Server, the block sizes of the volumes hosting data, logs, and TempDB should be aligned with this and set to 64KB.
Determine Disk Block Size
Cloud Storage
When building IaaS (infrastructure as a service) in a cloud platform, in order to host SQL Server, careful consideration should be given to the storage requirements of the data-tier application. Storage options in cloud are complex and ever changing. They also vary between cloud providers. Therefore, you should consult with your Cloud architect or DevOps engineer. However, this section will provide a brief overview of the main points of consideration.
The first consideration is shared storage. If you are building a stand-alone SQL Server instance, or indeed an AlwaysOn Availability Group topology, with no shared storage, then this is not a worry. If you intend to deploy a SQL Server failover clustered instance, however, then the type of storage you use is important. In AWS, then instead of using standard EBS (Elastic Block Storage) for your data disks, you will need to use Amazon FSx file share, which can be shared between multiple EC2 (Elastic Compute Cloud) instances. In Azure, you will still use Azure managed disks for your VMs, but you will need to implement S2D (Storage Spaces Direct) and Cluster Shared Volumes, which can be configured within Windows Server.
You will also need to consider storage performance. There are two separate components to this. Firstly, you will need to ensure that the storage you choose is fast enough to meet the requirements of the use case.
In Azure, Microsoft recommends using Premium SSDs, which are no larger than 2048 GiB for the data volume, to optimize cache support. A P40 disk (2048 GiB) provides 7500 maximum nonbursting IOPS (input/output per second) with a maximum 250 MB/s nonbursting throughput. If you step down in size to a P30 disk (1024 GiB), then the maximum nonbursting IOPS reduces to 5000 with the maximum nonbursting throughput at 200 MB/s.
If you require higher IOPS, then you would need to consider using Azure ultra disks instead. These disks can provide submillisecond latency with up to 160,000 IOPS and 4000 Mbps, depending on the size of the disk.
In AWS, the storage options (at the time of writing) are GP2 (General Purpose 2), GP3 and Provisioned IOPS SSD (io1 and io2). GP2 storage is a predecessor to GP3 and generally shouldn’t be considered for SQL Server. Its performance is tied to its size, which means you may end up needing to buy more larger disks than you require. GP3 also has a maximum throughput four times higher than GP2 and is less expensive.
GP3 has a baseline performance of 3000 IOPS and 125 MiB/s throughput. For an additional fee, however, performance can be increased to a maximum of 16,000 IOPS and 1000 MiB/s throughput. If additional IOPS are required, then you can move to a Provisioned IOPS volume. The io2 type is available at the same price point as io1 and has improved performance, so io1 generally should not be considered for SQL Server. Io2 volumes provide 500 IOPS per GiB, with a maximum of 64,000 IOPS.
In AWS, when considering storage performance, it is important to consider the size of the EC2 instance, as well as the performance of the disk itself. Different instance types and sizes have storage throttled at different levels. This means that you need to be careful that you do not buy fast, expensive storage, but then throttle that performance by using the wrong instance type. For example, a m4.xlarge instance has an EBS bandwidth of 750 Mbps (715 MiB/s). Therefore, if you have purchased a GP3 volume with 1000 MiB/s throughput, then you will only actually get 715 MiB/s.
Operating Systems Considerations
SQL Server has support for many operating systems, including many versions of Windows. It is unlikely, however, that you will want to allow SQL Server to be installed on any version of any operating system that is supported. For example, within your Windows estate, it is advisable to align a version of SQL Server with a specific version of Windows. This gives you two benefits.
First, it drastically reduces the amount of testing that you need to perform to sign off your build. For example, imagine that you decide you will only allow Enterprise edition within your environment. In theory, you would still need to gain operational sign-off on more than a dozen versions of Windows. In contrast, if you allow both SQL Server Enterprise and Standard editions of SQL Server, but you align both editions with Windows Server 2022 Standard edition, then you would only require sign-off once for each of your supported editions of SQL Server.
The second benefit is related to end of life cycle (EOL) for your platforms. If you allow SQL Server 2019 to be installed on Windows Server 2016, the end of mainstream support for Windows is January 2022, as opposed to January 2025 for SQL. At best, this will cause complexity and outage while you upgrade Windows, and at worst, it could lead to extended support costs that you could have avoided.
It is a good idea to run the latest version of Windows Server and SQL Server, as they provide the most benefit in terms of features, security, and performance. If your organization is risk adverse, however, and does not like to run the latest version of software, in case there are any early life stability issues, then I recommend trying to adhere to a current version – 1 policy, if possible.
Configuring the Operating System
Do your Windows administration team have a “gold build” for Windows Server 2022? Even if they do, is it optimized for SQL Server? Unless they have produced a separate build just for the purposes of hosting SQL Server, then the chances are that it will not be. The exact customizations that you will need to make are dependent on how the Windows build is configured, your environmental requirements, and the requirements of the data-tier application that your server will be hosting. The following sections highlight some of the changes that are often required.
A gold build is a predefined template for the operating system that can be easily installed on new servers to reduce deployment time and enforce consistency.
Setting the Power Plan
It is important that you set your server to use the High Performance power plan. This is because if the balanced power plan is used, then your CPU may be throttled back during a period of inactivity. When activity on the server kicks in again, you may experience a performance issue.
Set High Performance Power Plan with PowerShell
Optimizing for Background Services
It is good practice to ensure that your server is configured to prioritize background services over foreground applications. In practice, this means that Windows will adapt its context-switching algorithm to allow background services, including those used by SQL Server, to have more time on the processor than foreground applications have.
To ensure that Optimize for Background Service is turned on, enter the System console in Control Panel and choose Advanced System Settings. In the System Properties dialog box, select Settings within the Performance section.
Setting Optimize for Background Services with PowerShell
Assigning User Rights
Depending on the features of SQL Server that you wish to use, you may need to grant the service account that will be running the SQL Server service user rights assignments. These assignments allow security principles to perform tasks on a computer. In the case of the SQL Server service account, they provide the permissions for enabling some SQL Server functionality where that functionality interacts with the operating system. The three most common user rights assignments, which are not automatically granted to the service account during installation, are discussed in the following pages.
Initializing the Instant File
If you have not enabled Instant File Initialization during the installation of the SQL Server instance, then when you create or expand a file, the file is filled with 0s. This is a process known as “zeroing out” the file, and it overwrites any data that previously occupied the same disk space. The issue with this is that it can take some time, especially for large files.
It is possible to override this behavior, however, so that the files are not zeroed out. This introduces a very small security risk, in the respect that the data that previously existed within that disk location could still theoretically be discovered, but this risk is so small that it is generally thought to be far outweighed by the performance benefits.
In order to use instant file initialization, the Perform Volume Maintenance Tasks User Rights Assignment must be granted to the service account that is running the SQL Server Database Engine. Once this has been granted, SQL Server will automatically use instant file initialization. No other configuration is required.
Local Security Policy
Right-clicking the assignment and entering its properties will allow you to add your service account.
Instant File Initialization can also be configured during SQL Server installation. This will be discussed in Chapter 2.
Locking Pages in Memory
If Windows is experiencing memory pressure, it will attempt to page data from RAM into virtual memory on disk. This can cause an issue within SQL Server. In order to provide acceptable performance, SQL Server caches recently used data pages in the buffer cache, which is an area of memory reserved by the Database Engine. In fact, all data pages are read from the buffer cache, even if they need to be read from disk first. If Windows decides to move pages from the buffer cache out to disk, the performance of your instance will be severely impaired.
In order to avoid this occurrence, it is possible to lock the pages of the buffer cache in memory, as long as you are using the Enterprise, or Standard, edition of SQL Server 2022. To do this, you simply need to grant the service account that is running the Database Engine the Lock Pages In Memory assignment using the same method as for Perform Volume Maintenance Tasks.
If you are installing SQL Server on a virtual machine, depending on the configuration of your virtual platform, you may not be able to set Lock Pages In Memory, because it may interfere with the balloon driver. The balloon driver is used by the virtualization platform to reclaim memory from the guest operating system. You should discuss this with your virtual platform administrator.
SQL Audit to the Event Log
If you are planning to use SQL Audit to capture activity within your instance, you will have the option of saving the generated events to a file, to the security log, or to the application log. The security log will be the most appropriate location if your enterprise has high security requirements.
In order to allow generated events to be written to the security log, the service account that runs the Database Engine must be granted the Generate Security Audits User Rights Assignment. This can be achieved through the Local Security Policy console.
Audit Application Generated Properties
You need to ensure that your policies are not overridden by policies implemented at the GPO level. If this is the case, you should ask your AD (Active Directory) administrator to move your servers into a separate OU (organizational unit) with a less restrictive policy.
Selecting Features
When installing SQL Server, it may be tempting to install every feature in case you need it at some point. For the performance, manageability, and security of your environment, however, you should always adhere to the YAGNI (you aren't going to need it) principle. The YAGNI principle derives from extreme programming methodology, but it also holds true for the platform. The premise is that you do the simplest thing that will work. This will save you from issues related to complexity. Remember that additional features can be installed later. The following sections provide an overview of the main features you can select during an installation of SQL Server 2022 Enterprise edition.
The features are split into two categories, namely, instance features and shared features. Features within the instance features category can be installed multiple times on the server, associated to different instances. This is opposed to features within the shared features category, which can only be installed once on a server and are shared between all instances. The following sections will discuss the features available within each of these sections.
Instance Features
The following sections discuss each of the features that can be installed for each SQL Server instance on the server.
Database Engine Service
Database Engine architecture
It also contains replication components, in-database machine learning services and language extensions, full-text and semantic extractions for search, the PolyBase query service, and the DQS Server features, which can be selected optionally.
Replication is a set of tools that allows you to disperse data. In-database machine learning services provide Python and R integration, while sematic extractions allow you to use full text to search for the meaning of words rather than just keywords themselves. PolyBase query service allows you to run T-SQL against Hadoop data sources. DQS Server is a tool that allows you to easily find and cleanse inconsistent data. The machine learning services provide support for the R and Python languages, providing the ability to create both data science and machine learning solutions. These solutions can then import, explore, and analyze heterogeneous datasets. This book focuses primarily on core Database Engine functionality.
Analysis Services
Multidimensional and Data Mining
Tabular
PowerPivot for SharePoint
Multidimensional and Data Mining mode will provide the capability to host multidimensional cubes. Cubes offer the ability to store aggregated data, known as measures, that can be sliced and diced across multiple dimensions, and provide the basis of responsive, intuitive, and complex reports and pivot tables. Developers can query the cubes by using the multidimensional expressions (MDX) language.
Tabular mode gives users the ability to host data in Microsoft’s BI semantic model. This model uses xVelocity to provide in-memory analytics, offering integration between relational and nonrelational data sources and provides KPIs (key performance indicators), calculations, multilevel hierarchies, and calculations. Instead of using dimensions and measures, the tabular model uses tables, columns, and relationships.
PowerPivot is an extension for Excel, which like the tabular model uses xVelocity to perform in-memory analytics and can be used for datasets up to 2GB in size. The PowerPivot for SharePoint installation expands on this by running Analysis Services in SharePoint mode, and it offers both server-side processing and browser-based interaction with PowerPivot workbooks; it also supports Power View reports and Excel workbooks through SharePoint Excel Services.
Shared Features
The following sections describe the features that can only be installed once on a server and are shared between all SQL Server instances.
Data Quality Client
The Data Quality Server is installed as an optional component of the Database Engine, as mentioned earlier. The Data Quality Client, however, can be installed as a shared feature. A shared feature is installed only once on a server and is shared by all instances of SQL Server on that machine. The Client is a GUI that allows you to administer DQS, as well as perform data-matching and data-cleansing activities.
Integration Services
Integration Services is a very powerful, graphical ETL (extract, transform, and load) tool provided with SQL Server. For the last decade, Integration Services is incorporated into the Database Engine. Despite this, the Integration Services option still needs to be installed for the functionality to work correctly, because it includes binaries that the functionality relies on.
Integration Services packages comprise a control flow, which is responsible for management and flow operations, including bulk inserts, loops, and transactions. The control flow also contains zero or more data flows. A data flow is a set of data sources, transformations, and destinations, which provides a powerful framework for merging, dispersing, and transforming data.
Integration Services can be horizontally scaled out across multiple servers, with a master and n workers. Therefore, in recent versions of SQL Server, you have the option to install classic, stand-alone Integration Services or install a scale out master, or scale out worker, on the server.
SQL Client Connectivity SDK
The Client Connectivity SDK provides a SDK for SQL Native Client to support application development. It also provides other interfaces, such as support for stack tracing in client applications.
Master Data Services
A web console to provide administrative capability
A configuration tool to allow you to configure your MDM databases and the web console
A web service, which provides extensibility for developers
An Excel add-in, for creating new entities and attributes
SQL Server Extension for Azure
The SQL Server Extension for Azure provides out-of-the-box functionality to allow your on-premises SQL Server instances to be managed using Azure tooling, as if they were within your Azure environment. This is achieved by using an Azure Arc agent. This will be discussed in more detail in Chapter 21.
In SQL Server 2022, Distributed Replay Controller and Distributed Replay Client are no longer included with the SQL Server setup media. Instead, they need to be downloaded separately.
Additionally, the runtimes for Python, R, and Java are no longer installed during setup. Instead, the required runtime packages should be installed manually, as required.
Summary
Planning a deployment can be a complicated task that involves discussions with business and technical stakeholders to ensure that your platform will meet the applications requirements, and ultimately the business needs. There are many factors that you should take into account.
Make sure you consider which is the appropriate version of SQL Server to install and the associated licensing considerations for that version. You should consider the holistic supportability of the estate when making this decision and not just the needs of the specific application. You should also consider if an Azure hosting option may be right for your application, or potentially even a hybrid approach, involving both on-premises and cloud hosting.
When planning a deployment, make sure to carry out thorough capacity planning. Also think about the hardware requirements of the application. How much RAM and how many processor cores you will need are important considerations, but perhaps the main consideration is storage. SQL Server is often an I/O-bound application, so storage can often prove to be the bottleneck.
You should also consider requirements for the operating system. This should not be limited to the most appropriate version of Windows, but also to the configuration of the operating system. Just because there is a Windows gold build available, does this mean that it is configured optimally for your SQL Server installation?
Finally, consider which features you should select to install. Most applications require only a small subset of features, and by carefully selecting which features you require, you can reduce the security footprint of the installation and also reduce management overheads.