Database Fundamentals # #: What's in the database

Source: https://www.scarydba.com/2017/06/20/database-fundamentals-3-whats-database/

Grant Fritchey

Translation: Shesheni Liu Jongmao Xu Yali Lai Huifang

President

It is important to note that many people do not need to create their own databases. You may also not create your own tables or other data structures. You may also need to run backups, re-store and secure the operating system so that the application installs as you create the database. This is fully understandable and fully conforms to the needs of many businesses and many database administrators. However, it is a good idea to understand what these things are and how it can be understood as part of SQL Server.

The database is actually a file.

If you need to store information that you can retrieve later, you must first organize the information. If you use a word-processing program, you can store different documents in different files instead of putting all the documents in a large file. The functionality of SQL Server is very similar to this one. Although you have a server, you will not simply store the various types of information needed to run your business on a large scale within the server. Instead, you want to organize this information. The initial organizational mechanism for SQL Server is the database. A database allows you to save a set of information in a separate storage area. In addition, it allows you to isolate the security of these different sets of information so that you can control who can view or modify the data.

In a database, these things are called tables. A table is how a relational store is defined. Information that you organize to store in a given database is added to these tables. You will be able to add or remove data from these tables to the database, and you can retrieve information from those tables from the database.

In addition to the tables in the database, there are many different constructs that can help you manage the information stored in the database. These include many different types of objects that can help you manipulate data such as views, stored procedures, and functions. You can also get a complete set of securable objects, such as roles and users.

The database is actually made up of files that are stored on the Windows file system. These files are proprietary in binary format and cannot be read directly except through SQL Server. Writing data to these files is one of the most expensive and time-consuming operations in SQL Server (although in most cases the time involved is measured in microseconds). Because of these costs, it is important to understand that the database is defined by the file and that it is stored in the database as part of it.

Two types of files

You can simply store the data in a text file or spreadsheet, which many people can do. But when multiple people access it at the same time, updating some dates, deleting some information and inserting new information, the storage mechanism becomes very problematic. This is why you need to use a database. Because SQL Server does a lot of different work on the data it stores, it has a different mechanism to perform these operations. Databases are built around two different types of files that store different types of information. In both of these types, you can refer to this diagram.

Data files

The first file that is required to define a database is a data file. Data files are easy to understand and interpret. This type of file is where the information written to the database will be stored. Any given database can consist of multiple data files. Data files can be placed on multiple hard disks of your system. If you have more advanced storage mechanisms, such as a storage Area network (SAN), it may not be a simple hard drive, it has other constructs on the server, but they will be mapped to the Windows operating system, because drives and SQL Server can also use them to store data files.

Log file

The second file that is required to create the database is the log file. The log file is slightly more complex than the data file. Log files record every transaction that occurs in the database. Transactions occur when the data is manipulated in some way in the system. These actions can update existing data by inserting new data into the table or by deleting the data from the table to add new data. All of these operations write information to the log file. There are many other features that are also related to log files. These files are usually much smaller than data files because entries in the log file are only saved until all data is successfully written to the data file. Because writing to the data file will have different types of failures, save the log file to help handle these failures. Once the information is written to the data file, the log entry can be deleted. The process of cleaning up the log files is discussed in detail in another post

When you start creating files, you can resize them. After you create a file, you can resize it up or down. Each file can be set to grow automatically, which is complicated, so it's worth taking more time to understand what it means.

autogrowth Set

If you have many databases, managing files on the database can be a lot of work. You must check the available space and then set the file to a larger size when enough. One way to work around these manual tasks is to use the Autogrow property setting in the database.

Warning

Use this setting with great care to populate the drive and take your server offline.

Setting the database to autogrow means that the database will automatically adjust the file size when it starts running. Many people use this setting, and many applications set it up at installation time. To avoid this problem, you should set the file's upper growth limit. You can set a file by a database or a fixed size percentage. For smaller databases, it is possible to grow by a percentage, but as the size of the database expands, the percentage-growth process becomes more and more lengthy. The best practice is to set growth to a specific value, not a percentage. Details on how to set all of these will be overwritten when the database is created.

Where do you put the files?

When you install SQL Server, you can choose where to place the database files. You can also adjust through the Server Properties window. When you create a database, it's best to know where to place the files to make sure there is enough space on the drive. To view this location, connect to the server as described in Database Foundation # 2. Once connected, in the Object Explorer window, right-click the server name itself. This will bring up a context menu. Select the Properties menu option at the bottom of the menu. This will open the Server Properties window and you will be on the default "General" tab. Select the Database Settings tab and you'll see something very similar to this:

This interesting area is at the bottom of a portion of the window named the default location of the database. You will see the three different directories listed for data, logs, and backups, respectively. By clicking the ellipsis to the right of the entry, you can open a default file browser window to change the default location of your log or data file. You can also modify them by entering the text box directly, such as the physical path shown above, or by using a UNC path.

The goal of placing files should be to try to detach as much as possible. If you have the ability, it is best to separate the operating system files from the data and log files. This means that if possible, try to place the data and log files on something other than the C: \ Drive. In addition, you should at least consider storing the data and log files on a completely separate drive. This allows maximum throughput for data transfer to files on the drive.

Once you have determined where the storage location is appropriate, if you have made changes, click the OK button to save the changes. If you do not make any changes, or if you do not want to save your changes, click the Cancel button. These clicks close the Server Properties window.

It is important to have several attributes per two file types in addition to the physical location when creating the database. A part of the physical location will be the file name and extension. These can be any valid Windows operating system name and extension. By default, the extension of the data file is mdf,ldf as an extension of the log file. If you prefer, you can change these, but it can cause confusion because most people use default values as best practices. Has a logical file name that allows you to reference a file in the database without having to return to the full file location, including the drive. The logical name does not need to match the physical name, but this is usually the case.

Summarize

This describes the core content of the database file store. Next article we will create a database on the server, which is much simpler than this post.

Database Fundamentals # #: What's in the database