Database Base #: Data in a database?

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

Reprinted from book– "execution plans" CODE AZURE DATA PLATFORM Instructors

It is important to note that many people never need to create their own databases. You may never create your own tables or other data structures. You can only 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 a good idea 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. It is necessary for you to organize this information. If you are using a word processor, 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 that are required to run the business in a large heap 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. The table is how the relational store is defined. information that you store in a given database for your organization is added to these tables. you will be able to 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 different constructs that will help you manage the information that is also stored in the database 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 stored by the A file in a location on the Windows file system. These files are in a proprietary binary format, unless they are 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 they are important in the place where they are stored because of your understanding of the database.

Two types of files

you can simply store the data in a text file or spreadsheet and have a large number of people to use. However, when you need more than one person to access it at the same time, update some dates, delete certain dates, insert new information, and 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. The database consists of two different types of files that store different types of information. We can refer to this number by these two types.

Note : There are several other file types that can actually be added 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 that 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 map to the Windows operating system as drives, and SQL Server can use the storage 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 files because entries in the log files need to be preserved until all data has been 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 adjust the size of the top and bottom after you create the file. 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 a lot of databases, you may have a lot of administrative files on the database. You must check the free space and then set the file to a larger size when enough. One way to do all of this manually is to use the Autogrow property setting on the database.
a little 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. More information about how to set all of these will be overwritten when the database is created.

Where to place the file

installation 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 this 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 by typing directly into the text box using the physical path above or by using Universal Naming Convention (UNC) paths.
the goal of placing files should be to try to detach as many functions as possible. If you have this ability, you'd better separate the operating system files from the data and log files. This means that if possible, the data and log files are placed outside of the C: \ Drive. In addition, you should at least consider storing the data and log files on a completely separate drive. This allows the maximum data throughput on the files on the drive.
after you determine if the storage location is appropriate, if you make a change, click the OK button to save the changes. If you have not made any changes, or if you do not want to save your changes, click the Cancel button. One of these clicks will close the Server Properties window.
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 it can cause confusion because most people use default values as best practices. Has a logical file name that 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 will describe the contents of the database's file store. Next article we will actually create a database on the server, which is a much easier feeling than this post.

Database Base #: Data in a database?