Microsoft SQL Server
SQL Server is a relational database management system that supports users and applications by running on a physical or virtualized hardware platform on Windows or Linux. Within the overall hierarchy of support for databases, data, security, high availability, reporting, and business intelligence, the SQL Server would be regarded as the top level.
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 and\or Development. This is a separate configuration from virtualization.
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.
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.
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.
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.
SQL Server System Database
The SQL Server system databases are responsible for particular functions of the underlying relational database management system which includes:
· 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
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 which 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 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 Tables
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(Length), CHAR(Length), 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.
SQL Server Non Clustered Index
One or more SQL Server Non Clustered Indexes can be created per table. Non Clustered Indexes are generally used for range queries, Foreign Keys or on columns used in WHERE, ORDER BY and GROUP BY clauses to fulfill specific queries. The more selective the data is in the column, the greater benefit the index will provide to the query performance.
SQL Server Stored Procedure
A stored procedure is an object created within a database and schema which includes business logic to perform a discrete operation such as retrieve, add, modify or delete data. Stored procedures are written with T-SQL code and permissions are granted to execute the code. Stored procedures are given a logical name. Zero, one or many parameters can be passed into a stored procedure to make them modular and one or more result sets can be returned by a single stored procedure. Once a stored procedure is created, it can be modified with an ALTER command or deleted with a DROP command.
SQL Server Query
A query is written with T-SQL code and is not stored within a database, but rather in an external file. The user executing the query needs to have permissions to the tables and views that are accessed with the query. The query can be used to retrieve, add, modify or delete data. A query can have one or more parameters as well as one or more result sets.
SQL Server Function
There are two types of SQL Server Functions – System and User Defined. System functions are created by Microsoft and ship with SQL Server. System Functions perform string manipulations, date calculations, statistical calculations and more. These functions are fairly generic and used across numerous applications. User Defined Functions are created by Developers and DBAs at specific organizations to meet a particular business need. The User Defined Functions can be scalar valued (return a single value), inline table valued (returns multiple columns) or multi statement table valued (returns multiple columns and rows).
SQL Server View
A SQL Server View is a virtual table. The code that comprises the View is a SELECT statement that accesses one or more tables via a JOIN statement. A View can be used to simplify a complex query or to simplify reporting for power users. Permissions for users or groups should be granted to Views.
SQL Server Management Studio
SQL Server Management Studio (SSMS) has been the long-standing tool available from Microsoft to administer and develop database objects with T-SQL code. SQL Server Management Studio enables you to connect to instances of the database engine, Integration Services, Reporting Services and Analysis Services that are on-premises, at a co-location facility and in some circumstances, in the cloud. SSMS originally shipped with the database engine, but has subsequently become a separate download directly from Microsoft.
From an administration perspective, SSMS enables DBAs to write T-SQL code to perform tasks such as backups, restores, configure databases, tune performance, schedule jobs and more. SSMS also includes user interfaces and wizards to perform many of these same tasks. On many of these interfaces, you can also get a copy of the T-SQL code that is generated to automate the process across numerous instances or to include in your source control system as the change progresses from development to test and production systems.
From a development perspective, SSMS enables Developers and Business Intelligence Professionals to build tables, stored procedures, views, functions, etc. There are also user interfaces to visually build database diagrams and tables to better understand relationships among objects. SSMS also includes visual query plan analysis to understand the interworking's of your T-SQL code in order to optimize it.
SQL Server Profiler and Server Side Trace
SQL Server Profiler has been a long-standing client side tool run by DBAs or Developers to capture each and every line of code, in the form of events, being executed against a SQL Server instance to tune performance. Profiler can generate a tremendous amount of data and potentially impact the performance of the monitored system if not configured correctly on an already taxed SQL Server instance. Profiler can be fine-tuned to only capture event data for specific users, databases, time periods, etc. Profiler data can be written to a table for further analysis or to a text file.
A related process is the Server Side Trace which is a set of T-SQL code which can be executed against a SQL Server instance either via SQL Server Management Studio or a SQL Server Agent Job to capture the same events and write them to a file. The file is generally loaded to a table to perform analysis. The Server Side Trace generally has less of a performance impact on the monitored SQL Server instance as compared to Profiler.
In general, Profiler has been replaced by Extended Events due to the performance implications, but some SQL Server Professionals still prefer Profiler and Server Side Trace for ad-hoc or specific performance tuning cases.
SQL Server Extended Events
In many respects, SQL Server Extended Events have grown out of the original SQL Server Profiler and Server Side Trace functionality. Many of the same events and configurations are available in Extended Events in the SQL Server Management Studio Wizard or T-SQL code. Extended Events can be run to perform data collection for performance tuning or monitor for operational items such as a database that goes offline. SQL Server also ships with some default Extended Event sessions and templates which can be used as a starting point to collect data. Keep in mind this data can become large very quickly, so it is not a configure it and forget it situation. It is necessary to only capture the needed events and associated data then monitor this process as a portion of your daily tasks.
Performance Monitor
Performance Monitor (or sometimes referred to as Perfmon or System Monitor) is a free tool that ships with Windows to capture real time (or via a predefined schedule) macro level performance statistics related to the Windows operating system and SQL Server. The Performance Monitor counters provide either a usage percentage or a count of a particular event for a specific resource or application. Performance Monitor counters do not drill into a specific line of code. At a high level, the Windows counters are related to memory, CPU, disks, etc. and from a SQL Server perspective are related to scan methods, recompiles, buffer cache hit ratio, memory grants, etc.
Using both Performance Monitor and Profiler was a long time performance monitoring trick to understand the macro level statistics such as a CPU spike with Performance Monitor and then see the corresponding code executed during that time frame with Profiler.
SQLCMD
SQLCMD is a command line tool that is run at the Windows command prompt to connect to the SQL Server relational engine and execute T-SQL code. SQLCMD can be used to automate execution of particular script files or for troubleshooting purposes. Everything that can be performed in SQL Server Management Studio can also be run with SQLCMD with the corresponding T-SQL commands.
BCP
BCP is a command line tool to import or export data between a text file and a SQL Server table or view. BCP can accept a number of parameters to format the data as needed. BCP in many respects has been replaced by SQL Server Integration Services and Azure Data Factory due to the graphical nature of the newer tools and additional functionality. However, BCP remains a consistent tool that is easy to integrate for scripted deployments.
DBCC
In SQL Server, DBCC is the acronym for Database Console Command. These commands are intended to retrieve low-level status data for databases as well as correct specific settings or data within a database. They may not be commands you use on a daily basis, but are very useful when needed.
· DBCC CHECKALLOC
· DBCC CHECKCATALOG
· DBCC CHECKDB
· DBCC CHECKPRIMARYFILE
· DBCC CHECKTABLE
· DBCC CLONEDATABASE
· DBCC DROPCLEANBUFFERS
· DBCC FREEPROCCACHE
· DBCC INPUTBUFFER
· DBCC LOGINFO
· DBCC OPENTRAN
· DBCC OPTIMIZER_WHATIF
· DBCC PAGE
· DBCC SHOW_STATISTICS
· DBCC SHOWCONTIG
· DBCC SHRINKFILE
· DBCC SQLPERF(logspace)
· DBCC UPDATEUSAGE
· DBCC WRITEPAGE
DBCC CHECKDB
DBCC CHECKDB checks the consistency of a SQL Server database to ensure there is no corruption at the data or index page level. There are parameters that can be passed to this command to correct corruption including correcting the corruption at the price of losing data.
DBCC CHECKTABLE
DBCC CHECKTABLE checks the consistency of a specific SQL Server user defined table in a user defined database for corruption. DBCC CHECKTABLE can be run on larger tables, if there is not sufficient time to run DBCC CHECKDB or after correcting corruption on a specific table. However, it is prudent to run DBCC CHECKDB to ensure there is not corruption on every object after corrective measures have been taken to correct database corruption.
DBCC CHECKALLOC
DBCC CHECKALLOC checks the internal allocation structures for a user defined SQL Server database. DBCC CHECKALLOC is included as a check when DBCC CHECKDB is run.
DBCC CHECKCATALOG
DBCC CHECKCATALOG checks the consistency of the system catalog (i.e. objects). DBCC CATALOG is included as a check when DBCC CHECKDB is run.
DBCC PAGE
DBCC PAGE is used to review the contents of SQL Server data and index pages. This is generally used when troubleshooting corruption.
DBCC WRITEPAGE
DBCC WRITEPAGE is a command to test and correct SQL Server database corruption. This command can cause corruption and should only be used in development or test environments.
DBCC CHECKPRIMARYFILE
DBCC CHECKPRIMARYFILE reads the meta data from the primary database data file on disk such as the database name, logical file name, physical file name, etc. One reason to do so is to use the output from this command to attach databases.
DBCC SHRINKFILE
DBCC SHRINKFILE is used on a SQL Server database to shrink an existing database or transaction log file with the ability to free up disk space on the drive.
DBCC CLONEDATABASE
DBCC CLONEDATABASE creates a copy of an existing SQL Server database with only the objects and statistics. No data is included so the cloned database is generally a fraction of the size of the original version. DBCC CLONEDATABASE is helpful to use when trying to perform query tuning or move the database structure to a development or test environment without the data.
DBCC DROPCLEANBUFFERS
DBCC DROPCLEANBUFFERS is a means to clear cache for testing purposes in a SQL Server development or test environment. This should not be run in a production environment.
DBCC FREEPROCCACHE
DBCC FREEPROCCACHE is a means to clear the procedure cache for testing purposes in a SQL Server development or test environment. This should not be run in a production environment.
DBCC INPUTBUFFER
DBCC INPUTBUFFER with the System Process ID passed to it displays the last run command for that specific System Process ID in SQL Server. Depending on the size of the code issued, this command may not return all code run.
DBCC OPTIMIZER_WHATIF
DBCC OPTIMIZER_WHATIF in SQL Server provides the ability to perform query plan simulation for differing hardware configurations between production and development as an example.
DBCC UPDATEUSAGE
DBCC UPDATEUSAGE in SQL Server updates the internal page counts that sometimes become inaccurate with a large data load or deletion. DBCC UPDATEUSAGE can help the SQL Server optimizer obtain accurate data for query plans.
DBCC SHOW_STATISTICS
DBCC SHOW_STATISTICS provides statistics for a particular SQL Server table including the most recent UPDATE STATISTICS, row count, histogram steps, etc.
DBCC SQLPERF(logspace)
DBCC SQLPERF(logspace) reports the amount of the SQL Server transaction log is in use.
DBCC LOGINFO
DBCC LOGINFO reports on the virtual logs in the SQL Server transaction log.
DBCC OPENTRAN
DBCC OPENTRAN reports the open transactions in a SQL Server database.
DBCC SHOWCONTIG
DBCC SHOWCONTIG reports on SQL Server table fragmentation.
SQL Server Maintenance Plan
SQL Server Maintenance Plans are included in SQL Server Management Studio as a point and click option to perform SQL Server database backups, rebuild indexes in SQL Server, reorganize indexes in SQL Server, SQL Server DBCC CHECKDB and more. The SQL Server Maintenance Plans are actually SQL Server Integration Services Packages that are created and saved to the instance. The maintenance plans can be configured for all databases and scheduled to run on a regular basis. Depending on the workload for the SQL Server environment, maintenance can be scheduled at night, over the weekends, on a monthly basis, etc. The benefit of performing the SQL Server database maintenance is to ensure the databases are free of corruption and able to achieve high performance.
SQL Server Maintenance
Beyond the SQL Server Maintenance Plans included with SQL Server Management Studio, SQL Server database maintenance can be performed with DBCC Commands, index rebuilds, index reorganization, database backups, database restores, UPDATE STATISTICS, etc. These are T-SQL commands that can be run in SQL Server Management Studio or can be scheduled via SQL Server Agent. The benefit of using the T-SQL commands is that additional parameters can be included and code can be built dynamically to include or exclude particular databases without manual intervention.
SQL Server Backup
SQL Server Backups are a critical process which generate point in time snapshots of a database or transaction log. SQL Server Backups include: Full, Differential, File, Transaction Log, Copy_Only and Snapshot. SQL Server backups can be created in SQL Server Management Studio or via Transact-SQL code. SQL Server Backups are generally scheduled on a regular basis to provide point in time recovery in case an issue occurs. Depending on the organizational or application requirements, Full backups are executed daily and transaction log backups are issued throughout the business day, perhaps everyone to fifteen minutes. Keep in mind that SQL Server Backups are a point in time version of your online database, so these files need to be protected and secured to ensure your data is not compromised. Often times, SQL Server Backups are the last resort to get a database operational and continue business operations. It is prudent to verify backups run accurate on a predefined basis, to issue backups prior to any major changes to a database and to retain a backup prior to retirement for long term retention.
Restore Database in SQL Server
A SQL Server Restore is the process of using a previously generated SQL Server Backup to create a new database or overwrite the existing database. The SQL Server Restore options include Full, Differential, File, Transaction Log and Snapshot. A SQL Server database may need to be restored if there is an issue with the online database, data needs to be recovered, testing needs to be performed or a new development environment needs to be setup. Keep in mind all of the production data will be restored, so it is imperative to properly secure the database once restored and either obfuscate or delete sensitive data, if it is not appropriate for the new environment. Also, it is prudent to test backups by restoring them on a regular basis to ensure the restore process is understood and can be executed under pressure.
0 Comments