Database Fundamentals # 3: What's in a database?

Database Fundamentals # 3: What's in a database?

It is worth mentioning that many people will not need to create their own database. You may never be able to create your own tables or other data structures. You may be installing an application to create a database for you, and you are just doing some backup repair work and dealing with system security issues. Of course this is fully understandable, after all, it is very much in line with the needs of many enterprises and many less important database application systems. However, it's a good idea to understand a subset of SQL servers, such as what it is and how it works.

The database is actually a file.

You need to save the information you want to help you find later. The orderly storage of information is essential. If you do a word-processing program, you store different documents in different files. You don't put all your documents in the same big file. This approach is very similar to SQL Server functionality. When you have a server, you cannot simply store all kinds of information necessary for your business in a large file and put it into the server. Instead, you have to organize that information. The initial organizational mechanism for SQL Server is the database. Databases allow information sets to be saved in different storage areas. In addition, it allows you to isolate these different sets of information to ensure security, while also enabling you to control who can view or modify the data.

There is something called a table in the database. Table defines the storage relationship. The information you store in the database will be added to these tables. You can also add data to the database or delete it from these tables. You can also retrieve information from these tables in the database.

In addition to the tables in the database, there are a number of different structures that will help you manage the information stored in the database. This includes a number of 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.

A database is actually made up of files stored somewhere in the Windows file system. The format of these files is the binary form of the special, except through the SQL Server, other software can not read directly. Writing data to these files is one of the most valuable and time-consuming operations in the SQL Server (although in most cases the time involved in the server is measured in microseconds). Because of this contribution, it is very important to understand that the database is defined by the file and the file is stored in the database.

Two types of files

You can store data in a text file or spreadsheet like many people do. But when you need more people to access at the same time to update some data, delete, insert new information, other storage mechanism becomes very problematic. That's why you want to use the database. Because SQL Server has to do a lot of different work on the stored data, it produces a number of different mechanisms to perform these operations. The structure of a database is to store different types of information in two different types of files, which you can refer to when you examine these types.

Note: There are actually several other file types that can be added to the database, but now we're talking about the fundamentals, so now we'll keep it simple.

Data files

The first class of files required to define a database is a data file. Data files are easy to understand and interpret. The information you write to the database is stored in this file. Any given database can consist of multiple data files. The data file can be placed on more than one hard disk. If you have more advanced storage mechanisms, such as storage area networks (Sans), you may have other structures instead of simple hard drives on the server, but they will be mapped as drives to the Windows operating system, which SQL Server can also use to store data files.

Log file

The second type of file that is required to create a database is the log file. The log file is slightly more complex and difficult to understand than the data file. Log files record every transaction in the database. Transactions occur when the data is manipulated in some way in the system. These operations can update existing data by inserting data into the table to add new data or delete data. These actions cause the information to be written to the log file, and many other functions are also related to the log file. These files are usually much smaller than data files because entries in the log are only saved until the data is successfully written to the data file. Because writing to the data file is limited by the different types of failures, the log is deleted once the data is written to the data file. The process of cleaning up the log files will be discussed in detail in another post. When you initially create the files, you can resize them. After you create a file, you can resize it, including up and down adjustments. Each file can also be set to grow automatically, but it's complicated, so it's worth spending more time understanding what it means.

Auto-Grow settings

If you have a large number of databases, then managing the files in your database can be a lot of work. You need to check the available space and increase the size of the file when the space is sufficient. One way to do all of this manually is to use the auto-grow settings for your database.

Warning: To be careful with this setting, you can fill a drive and keep your server offline. Set the database to autogrow, which means that the database will automatically resize the file when it starts to run out of space. Many people use this setting, and many applications are installed to set it to on. To avoid this problem, you should set a higher growth limit in the file. You can set these files to a database or a fixed size percentage. For smaller databases, a percentage increase is possible, but as the scale of the data expands, the percentage-growth process becomes more and more lengthy. The best practice is to set growth to a specific value instead of a percentage. When the database is created, detailed information about how to set these will be overwritten.

Where to place the file

When you install SQL Server, you have the option to define where your database files are placed. You can also use the Server Properties window to adjust. When you create a database, in order to make sure that there is enough space in the run, finally there is the location where you know where you placed the file. To see where the files are placed, connect to your server and have an overview of the relevant Essentials on Database Foundation 2. Once connected, in the Object Explorer window, right-click the server name itself. This will result in 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 in the default "General" tab. Select the Database Settings tab, and you'll see something very similar to this:

Viewing the interesting area here is the bottom of the window titled "Database Default Location" section. You'll see three different directories, one for data, logs, and backups. By clicking the ellipsis to the right of the entry, you can bring up the default file browser window to change the default location of the log or data file. You can also modify the input directly in the text box as shown in any physical path, using a Universal Naming convention (UNC) path.

The goal of placing files should be to detach the functionality 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, further, at a minimum, you should consider storing the data and log files on a completely separate drive. This allows the maximum throughput of data to be made on files on the drive.

Once you have decided where the storage location is appropriate, if you have made a change, click the Cancel button to save these changes on the stand-alone button, if you have not made any changes, or if you do not want to save the changes you have made. Any of these clicks will close the Server Properties window.

Both types of files have several properties, except that when you create a database, a part of the physical location is a file name and an extension. These can be valid Windows operating system name and extension, by default, the extension is usually MDF data file and IDF log file, you can change these if you want. But it can lead to a series of puzzles because most people use the default as the best practice. There is a logical file name that allows you to refer to the file in the database for operation without returning the full file location, including the drive. Logical names do not have to match physical names at all, but they usually do.

Conclusion

The above describes the nature of database file storage, the next chapter we will actually create a database on the server, it will be simpler than this chapter.

* Articles from:

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

* Original quote: Inside SQL Server T-SQL Querying–itzik Ben Gan

SQL Server, Transact-SQL Recipes–joseph Sack

Database Fundamentals # 3: What's in a database?