SQL Server is a relational database management system software that runs on Windows or Linux on a physical or virtual hardware platform to support applications and users. SQL Server would be considered the highest level in the overall hierarchy, supporting databases, data, security, high availability, reporting, and business intelligence.
Some people refer to SQL Server as the hardware platform, others as software or an application. And still others refer to SQL Server as the database engine.
Current versions include SQL Server 2019, SQL Server 2017, SQL Server 2016, 2014, etc. for Enterprise Edition, Standard Edition, Developer Edition, Express Edition, and Web Edition.
SQL Server Instance
A SQL Server instance is an installation of the SQL Server relational database platform that can be considered the highest level in the software hierarchy. The SQL Server instance encapsulates all of the database concepts (database, tables, data, stored procedures, security, etc.) covered in this article.
A single server (i.e., hardware platform) can have numerous SQL Server instances installed, each of which can be completely autonomous, but share the underlying CPU, memory, network, and storage resources. This configuration can be used for Production, Quality Assurance \ Testing \ User Acceptance Testing,g and\or Development. This is a separate configuration from virtualization.
Additional Information:
- How and why would I use multiple instances of SQL Server
SQL Server Service
The SQL Server Service is the main Windows service that controls whether the SQL Server instance is started or stopped. Each SQL Server instance has a corresponding Windows service. This Windows service can be configured to run with a domain account to access resources on the network or a local system account.
Additional Information:
- Windows Services for SQL Server
- How to stop and start SQL Server services
SQL Server Agent
SQL Server Agent is responsible for running Jobs on a predefined schedule. Jobs can include T-SQL, PowerShell, SSIS, Python, R, DOS, MDX, etc. commands. Operators can be configured and notified via email or text when a Job completes, fails or succeeds. SQL Server Agent also includes Alerts that can be configured for when performance or operational thresholds are met.
Additional Information:
- SQL Server Agent Job Management
- Getting Started with SQL Server Agent – Part 1
- Accessing SQL Server Agent Data
Virtual Machine
A Virtual Machine is an independent operating system and SQL Server installation with management similar to a SQL Server. The difference is that there is a virtualization layer between the physical hardware and the virtual machine. A single Host will support numerous Guests (virtual machines).
Virtualization is used to consolidate numerous independent machines onto a single hardware platform to reduce power and resource costs. Each server remains a separate entity that still requires management and maintenance. Windows and SQL Server licensing is still required for each virtual machine.
Additional Information:
- SQL Server Virtualization Overview (Part 1 of 5)
- Virtual Server Technologies and SQL Server
Host
A Host is the hardware platform (i.e., server) with the virtualization software to support one or many Guest virtual machines.
Guest
A Guest is an independent virtual machine with an operating system and SQL Server installed.
VMWare
At the time of writing, VMWare is the most used virtualization platform to support virtualized environments.
Relational Database Management System
SQL Server is one of many Relational Database Management Systems on the market, such as Oracle, PostgreSQL, Azure, Azure SQL Database, MySQL, etc. The Relational Database Management System manages all of the databases, data, transactional integrity, security, user access, etc. This could be for data warehouse projects, business intelligence, and more.
SQL Server Database
A database is a logical container for data, structures, and code with a specific name. A single database can support one or more applications and users. Permissions can be granted at the database level, but permissions are generally better suited to be granted to an application that a user needs at the object level, such as a stored procedure, table, or view.
In SQL Server, there are both system and user-defined databases. At the most basic level, a database consists of two physical files. First is for the database objects and data. The second file is for the transaction log, which is used to maintain transaction integrity as numerous users and applications are adding, removing, and modifying data within the database.
Additional Information:
- Create SQL Server Database using SQL Server Management Studio
SQL Server System Database
The SQL Server system databases are responsible for particular functions of the underlying relational database management system, which include:
- Master – All system objects to run the active relational database management system
- Model – Template database for new user-defined databases and Tempdb when SQL Server starts
- TempDB – Stores all temporary objects such as #temp tables, ##temp tables, hash and sort records, etc.
- MSDB – Stores all SQL Server Agent-related tables and stored procedures
- ResourceDB – Hidden and read-only database that includes all system objects
Additional Information:
- SQL Server System Databases
SQL Server User Database
A SQL Server user-defined database is created to support a specific application, such as CRM, eCommerce, Inventory, Reporting, etc. Each user-defined database on a SQL Server instance has a unique name and has a database owner who is generally the login who created the database. The User Defined database has one or more schemas, and each schema has tables, indexes, stored procedures, views, functions, and more.
MDF File in SQL Server
The SQL Server MDF file is one of two core files to support a physical SQL Server database. The MDF file is responsible for storing the system objects, user-defined object definitions (i.e., tables, views, stored procedures, etc.), data, and indexes. Each SQL Server database must have an MDF file, and only one MDF file can exist per database. The MDF file generally is the largest file supporting the database since the size of the data should be much larger than the space needed to support the transaction log.
NDF File in SQL Server
A SQL Server NDF file is a secondary data file to support a physical SQL Server database. Although it is not mandatory for a SQL Server database, an NDF file can support tables and indexes. NDF files are created for SQL Server databases to store a database across numerous disk drives to improve performance or support the database growth.
LDF File in SQL Server
The SQL Server LDF file is the second of two core files to support a physical SQL Server database. The LDF file supports the database transaction log, which stores before and after versions of records to enable a transaction to rollback. The LDF file is generally a fraction of the database file size, perhaps five to twenty-five percent of the database. There are circumstances with large transactions where the transaction log file may need to be sized larger.
SQL Server Database Transaction Log File
The SQL Server Transaction Log File corresponds to the physical LDF file supporting the database. The SQL Server Transaction Log File is responsible for storing before and after records of data involved in UPDATE or DELETE transactions in order to roll back the transactions to maintain transactional integrity with numerous users in the database.
SQL Server Schema
A schema is a logical container within a database to grant permissions to particular objects. By default, each SQL Server database has a dbo and sys schema. Although user-defined objects can be created in the dbo schema, it is generally recommended to create a separate schema for each logical application, such as sales, inventory, accounting, etc.
SQL Server Table
A table is a logical structure to store data related to a single entity, such as a customer, product, order, etc. Each table has one or more columns. Each column should be configured with the appropriate data type and null setting.
Row in a SQL Server Table
A single record in a table that consists of multiple columns.
SQL Server Data Types
Data types are defined for columns in tables and parameters for stored procedures, functions, and queries. The data type determines the acceptable data for the column or parameters. It is prudent to select the appropriate data type for your columns and parameters to minimize the amount of storage that is needed at a table level and avoid implicit or explicit conversion issues which could be a performance problem in the long term. Common data types are INT, DATETIME, VARCHAR(), CHAR(), etc.
NULL in SQL Server
Null is an unknown value. A column in a table can be configured to allow a null value or not
Referential Integrity in SQL Server
Referential integrity maintains consistent data from a primary key on one table to a foreign key on a related table. For example, with an Orders table the primary key could be OrderID. A related table could be OrderDetails, which should include the OrderID column from the Orders table. On the OrderDetails table, the OrderID column would be the foreign key related back to the OrderID column from the Orders table.
SQL Server Primary Key
A primary key is generally a single column, but could be two or more columns, that uniquely identifies a row in a table. A table should have a primary key to uniquely identify each row.
SQL Server Foreign Key
A foreign key is generally a single column, but could be two or more columns, that relates to another table to ensure data is consistent.
SQL Server Indexes
SQL Server indexes are physical structures that are built to improve data access performance. Without an index, a table scan will occur, which means the SQL Server Query Processor is scanning the table from the first to last row to complete the query. This is an expensive operation with a significant amount of data and a significant user base.
SQL Server indexes should be chosen wisely based on the data access patterns. Having too many or duplicate indexes is counterproductive and will impact the performance of INSERT, UPDATE and DELETE operations as well as excessive storage. Indexes also need to be maintained (rebuilt or reorganized) on a regular basis, either weekly, monthly or quarterly.
SQL Server ships with numerous types of SQL Server indexes that all provide specific value based on the need. The SQL Server indexes include:
- Clustered Index
- Non Clustered Index
- Covering Index
- Included Columns
- Filtered Index
- ColumnStore Index
- Full Text Index
- XML Index
SQL Server Clustered Index
A SQL Server Clustered Index brings physical and logical order to a table. There is a single SQL Server clustered index created per table. Typical columns are a unique ID, such as the Primary Key for the table or date column, such as OrderDate. It is a best practice to create a clustered index for most tables to improve data access performance when querying the table.
============================================================
"This Content Sponsored by SBO Digital Marketing.
Mobile-Based Part-Time Job Opportunity by SBO!
Earn money online by doing simple content publishing and sharing tasks. Here's how:
- Job Type: Mobile-based part-time work
- Work Involves:
- Content publishing
- Content sharing on social media
- Time Required: As little as 1 hour a day
- Earnings: ₹300 or more daily
- Requirements:
- Active Facebook and Instagram account
- Basic knowledge of using mobile and social media
For more details:
WhatsApp your Name and Qualification to 7338068894
a.Online Part Time Jobs from Home
b.Work from Home Jobs Without Investment
c.Freelance Jobs Online for Students
d. Mobile-Based Online Jobs
e.Daily Payment Online Jobs
Keyword & Tag: #OnlinePartTimeJob #WorkFromHome #EarnMoneyOnline #PartTimeJob #jobs #jobalerts #withoutinvestmentjob"
====================================================================
No comments:
Post a Comment