Foreign language translation: What is in the database?

What's in the database?

It's worth noting that many people never need to create their own databases. You may never create your own tables or other data structures. You only need to run backup and restore and manipulate security on the system and have the application install the database for you. This is fully understandable and fully conforms to the needs of many businesses and many unexpected DBAs. However, it is good for you to understand what these things are and how to understand them as part of SQL Server.

The database is actually a file

You need to store information that you can retrieve later. Therefore, it is necessary for you to organize the information. If you are using a word-processing program, you can store different documents in different files. You really don't put all the documents in a single large file. The functionality of SQL Server is very similar. When you have a server, you do not simply store the various types of information you need in a large heap of business running on that server. Instead, you will organize this information. The initial organizational mechanism for SQL Server is the database. The database allows you to preserve the 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 is viewing or modifying the data.

There are some things called tables in the database. A table is a definition of a storage relationship. After you store your organization's information in a database, it is added to these tables. You can add or remove data from the database through 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 constructs that will 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.

A database is actually made up of files stored in a location in the Windows file system. These files are proprietary, binary format data that can only be read directly by 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 in microseconds). Because of this cost, it is important to understand that your database is defined by files and that it is clear where they are stored.

Two types of files

You can simply store the data in a text file or spreadsheet and be used by a large number of people. However, when you need more than one person to access it at the same time, updating certain dates, deleting certain dates, inserting new information, and so on, these other storage mechanisms become very problematic. That's why you need to use the database. Because SQL Server has to do a lot of different work with the stored data, there are different mechanisms to perform these operations. A database is built around two different types of files that store different types of information. We use this graph to understand the two types.

Note: You can actually add several other file types to the database, but we're talking about fundamentals here, so now we'll keep it intuitive and simple.

Data files

The first file type required to define a database is a data file. Data files are easy to understand and interpret. This type of file is where you want to store the information you write to the database. Any given database can consist of multiple data files. The data file can be placed on multiple hard drives with your system. If you have more advanced storage mechanisms, such as storage area networks (Sans), you might have other constructs instead of simple hard drives on the server, but they will all map to the Windows operating system as drives, and SQL Server can use these to store data files.

Log file

The second file type required to create the database is the log file. Log files are a bit more complicated than data files. Log files record every transaction that occurs in the database. Transactions occur when data is manipulated in some way in the system. These actions can be updates to existing data, add new data by inserting new data into a table, or delete data from a table. All of these operations write information to the log file. There are also other features associated with log files. These files are usually much smaller than the data file, because entries in the log file only need to be retained until all data is successfully written to the data file. Because write data files have different kinds of failures, log files are retained to help handle these failures. Once the information is written to the data file, you can delete the log entry. The process of cleaning up the log files is discussed in detail in another article.

When you initially create a file, you can resize it. You can also resize a file after it is created. Each file can also be set automatically, which is very complex, so it's worth spending more time understanding what it means.

Auto-Grow settings

If you have many databases, managing files on the database can be a lot of work. You must check the free space and then set the file to a larger size when enough space is available. One way to do this is to use the auto-growing property set on the database.

Be cautious: Use this setting very carefully. You can fill in the drive to take your server offline.

Setting the database to autogrow means that the database automatically adjusts the file size when it starts to run. Many people use this setting, and many applications set it to on when they are installed. You should set a higher growth limit for the file to avoid the problem. You can set the file as a percentage of the growth database or a fixed size. For smaller databases, growth by percentage can work, but as the size of the database increases, the percentage growth will grow longer. The best practice is to set the growth rate to a specific value rather than a percentage. All details about how to set these will be overwritten when the database is created.

Where to place the file

When you install SQL Server, you have the option to define where to place the database files. You can also make adjustments through the Server Properties window. When you create a database, it is best to know where you want to place the file to ensure that there is enough space on the drive. To view that location, connect to your server as described in "Database Fundamentals 2". Once connected, in the Object Explorer window, right-click the server name itself. This will display a context menu. Select the Properties menu option at the bottom of the menu. This opens the Server Properties window, which you will be on the default General tab. Select the Database Settings tab, and you'll see something similar to this:

The interesting area to view is the "Database default location" section at the bottom of the window. You will see three different directories listed, one for data, logs, and backups. By clicking the ellipsis to the right of the entry, you can start the default File browser window to change the default location of the log or data file. You can also modify them or use Universal Naming Convention (UNC) paths by typing directly into the text box using the above physical path.

In addition to the physical location, each of the two file types has several properties that are important 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 is usually the. mdf of the data file, the. ldf of the log file. You can change these if you want, but this can lead to confusion because most people use default values as best practices. The logical file name, which allows you to reference operations in a database in a file without having to refer to the full file location including the drive. Logical names do not have to match physical names at all, but they are usually the same.

Conclusion

This post describes the contents of the database's file store. Next article we will actually create a database on the server and feel that it is easier to understand than this post.

(This post is translated by Grant FRITCHEY2017 June 20 posted on intimidating Databases and code: Database Basics #: What's in the database?

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

Foreign language translation: What is in the database?