Queries, Cursors, Views  «Prev  Next»
Lesson 3 Queries, Cursors, and Views Requirements
Objective Learn the requirements to take this course.

Client/server Software and Hardware Requirements to run SQL-Server 2019

To deploy and run SQL Server 2019 effectively, adherence to specific client/server software and hardware requirements is paramount. Ensuring compatibility and performance requires a thorough understanding of these prerequisites.

Server Hardware Requirements

  1. Processor:
    • Minimum: x64 Processor: 1.4 GHz
    • Recommended: 2.0 GHz or faster
    • SQL Server 2019 is built exclusively for x64 architectures.
  2. Memory:
    • Minimum: 512 MB
    • Recommended: 4 GB or more
    • For SQL Server 2019, the memory requirements heavily depend on the database workloads. More complex operations demand higher memory.
  3. Hard Disk:
    • Minimum: 6 GB of available space
    • The disk space requirement depends on the components you install. A SQL Server instance with extensive databases will require significantly more space.
  4. Disk I/O subsystem: This is critical for SQL Server performance. RAID configurations are often recommended for production systems, especially RAID 1 for log files and RAID 10 for data files.

Server Software Requirements

  1. Operating System:
    • Windows Server 2019, 2016, or 2012 R2
    • SQL Server 2019 is also supported on Linux: Red Hat Enterprise Linux (RHEL), SUSE Linux Enterprise Server (SLES), and Ubuntu.
    • Windows 10 is supported for development purposes.
  2. .NET Framework:
    • .NET Framework 4.6 or later is required.
    • Some SQL Server features like SQL Server Reporting Services (SSRS) require additional .NET components.
  3. Windows PowerShell:
    • Version 2.0 or later is required for automation and management tasks.

Client Hardware and Software Requirements

  1. Hardware: Client machines should have a modern processor (1 GHz or faster), at least 512 MB of RAM, and enough disk space for the tools and applications they intend to use.
  2. 2. Software:
    • The client machines require network software to communicate with the server.
    • Windows 10, 8.1, or Windows 7 SP1 for a client operating system.
    • For SQL Server Management Studio (SSMS) or other SQL tools, the corresponding software version compatibility is essential.
  3. Browser Support for SSRS:
    • SQL Server Reporting Services require a modern web browser like Microsoft Edge, Internet Explorer 11, Google Chrome, Mozilla Firefox, or Safari.

Critical Considerations

  • Scalability: Hardware should be chosen not just for current needs but also considering future scalability.
  • High Availability: In production environments, high availability features like Always On Availability Groups or Failover Cluster Instances demand specific hardware and software configurations.
  • Security: Ensure the operating system and all software are regularly updated to maintain security.
  • Virtualization: SQL Server 2019 supports virtualization, but this requires careful planning regarding resource allocation and management.
  • Testing: Always test your configuration in a non-production environment to ensure compatibility and performance.

In conclusion, meeting these hardware and software requirements is crucial for the successful implementation and smooth operation of SQL Server 2019. Organizations must evaluate their specific needs and plan accordingly, considering both current requirements and future growth.

Making your view look like a Table with VIEW_METADATA

This option has the effect of making your view look very much like an actual table to DB-LIB, ODBC, and OLE DB clients. Without this option, the metadata passed back to the client API is that of the base table(s) that your view relies on. Providing this metadata information is required to allow for any client-side cursors (cursors your client application manages) to be updatable. Note that, if you want to support such cursors, you are also going to need to use an INSTEAD OF trigger. 
alter view dbo.sales(vi)
with view_metadata
as
   select o.[SalesOrderID] 
   from [Sales].[SalesOrderHeader] o 

   select * from dbo.sales

INDEXED (MATERIALIZED) VIEWS

When a view is referred to, the logic in the query that makes up the view is essentially incorporated into the calling query.
Unfortunately, this means that the calling query just gets that much more complex. The additional overhead of determining the impact of the view (and what data it represents) on the fly can actually become very high. What is more, you are often including additional joins into your query in the form of the tables that are joined in the view. Indexed views give you a way of taking care of some of this impact before the query is run. An indexed view is essentially a view that has a set of unique values materialized into the form of a clustered index. The advantage of this is that it provides a very quick lookup in terms of pulling the information behind a view together. After the first index (which must be a clustered index against a unique set of values), SQL Server can also build additional indexes on the view using the cluster key from the first index as a reference point. That said, nothing comes for free and there are some restrictions about when you can and cannot build indexes on views.