Microsoft SQL Server is one of the market leaders for database technology. It’s a relational database management system that supports a number of applications, including business intelligence, transaction processing and analytics. Microsoft SQL Server is built on SQL, which is a programming language used to manage databases and query data.
SQL Server follows a table structure based on rows, allowing connection of data and functions while maintaining the data’s security and consistency. Checks in the relational model of the server work to ensure that database transactions are processed consistently.
Microsoft SQL Server also allows for simple installation and automatic updates, customization to meet your business needs and simple maintenance of your database. Below, you can get a quick overview of how a SQL Server manages data, and how data is retrieved and modified.
SQL Server Data Management
A SQL Database is comprised of one or more data files (.mdf/.ndf) and one transaction log file (.ldf). Data files contain schema and data, and the Log file contains recent changes or adds. Data is organized by pages (like a book), each page is 8KB.
A SQL Server manages this data in three ways:
- Reads
- Writes
- Modifies (Delete, replace, etc.)
Data Retrieval with SQL
A SQL Server accesses data by pulling down the entire 8KB page from disk into memory. Pages temporarily stay in memory until they are no longer needed. Often, the same page will be modified or frequently read as SQL works with the same data set.
Data Modification with SQL
SQL changes data via delete or modify, or by writing new data. All modifications are written to the transaction log (which sits on disk where it is safe) in case the SQL server loses power before it writes data back to disk.
The 8KB page is written back to disk after it has not been used for a certain time period. Once a transaction is written to disk (.mdf/.ndf file), it is marked as written in the transaction log. In case of a power outage, SQL can retrieve completed transactions that were not written and add them to the database files (.mdf and .ndf) once back in operation.
Taking Steps to Implement a Microsoft SQL Server
Today’s SQL dependent applications have different performance and high availability requirements, meaning there are many factors to consider for implementation. Thinking about implementing a Microsoft SQL Server, or want to make sure that yours is properly meeting your needs? INAP’s solutions architects can help with this process, and your SQL Servers can be hosted and managed on Bare Metal or Private Cloud.
INAP’s latest managed cloud solution, Intelligent Monitoring, supports SQL Servers, monitoring for core application metrics. Get transparency and control over your servers with the support of INAP’s experts.
