How to Reduce database management overhead to maximize functionality

I have introduced a lot about databases before, for example, using dynamic databases to access objects. Next I will explain how to reduce database management overhead.

Connect to the application

Each application needs to identify the database server to which it connects to for data retrieval. You can use a connection string to connect an application to a database server. The typical connection string is as follows:

Server = MyServerAddress;
Initial Catalog = MyDatabaseName;

Integrated Security = SSPI;

In this example, the database server can be identified by the machine name, IP address, obdc dsn name, or DNS server alias. It can be used as long as it can be resolved to the IP address name. Name resolution can be performed in many different ways.

If your SQL Server machine is in a domain, you can create a DNS domain name and register it in the domain. When the machine is registered with DNS, the client or application can use the Registration Name of the machine to connect to the machine. In addition, you can create a DNS alias using DNS registration. This is a logical name that represents your SQL Server machine. When the DNS alias is used in the connection string, when the database server is connected, the DNS will secretly resolve the name as an IP address. In this case, you only need to remember the logical name that seems meaningful and easy to remember when connecting, instead of the hard-to-remember IP address number string or machine name. When using a DNS alias in a connection string, you can create a connection policy to isolate applications from physical addresses or database server machine names.

Use DNS to identify Database Application Software locations

When using DNS to identify the location of database application software, you can use the machine domain name in the connection string, but this method is not flexible enough. Imagine what will happen when you want to change the name of the SQL Server physical machine. In this case, if you use the machine name, you must modify the connection string every time you change the machine name to reference the new machine name. If you only have one application to connect to a database server, the situation may not be so bad. However, if you have a lot of applications and a lot of databases on a machine, it means that once you rename your server, you need to modify many connection strings. Therefore, using the machine name in the connection string cannot flexibly cope with environment changes.

A better way is to use the DNS alias to resolve the database location. When you no longer use the machine name to identify the address of the database machine for all applications, you should consider creating a DNS alias with different meanings, this alias can be resolved to the IP address of the database server. For example, you can use a DNS alias similar to SQL2005PRO. The name defined in the DNS is the same as the IP address of the actual physical machine. You can use a DNS alias to give a name a certain meaning. SQL2005PROD indicates the SQL Server 2005 Server used for production. In this way, you can change the above connection string:

Server = SQL2005PROD;
Initial Catalog = MyDatabaseName;

Integrated Security = SSPI;

So what are the benefits of using a DNS domain name in a connection string? A descriptive name is undoubtedly one of the obvious advantages, but it is not the only one. Assume that your database server contains many different databases and supports 50 different applications. Assume that your SQL Server machine is named SSEDB01, and this machine now has an unknown hardware error. In addition, you have a backup machine named SSEDB02, And you have sent the backup of SSEDB01 to this SSEDB02 for security reasons, therefore, you can quickly restore all databases from SSEDB01 to support these 50 different applications. In addition, we assume that you know that it takes less time to restore all SSEDB01 databases on SSEDB02 than to solve hardware problems of SSEDB01 itself. Under the preceding conditions, if you use the machine name in the application connection string, you will have to modify all the connection strings one by one, change the machine name of SSEDB01 to SSEDB02, and point the 50 applications to the new backup server SSEDB02 to complete the recovery process. Modifying more than 50 connection strings may take a long time and is prone to errors. In this case, if the connection name you use in more than 50 connection strings is a logical name like SQL2005PROD, then you only need to make one modification to direct all applications to the new backup server SSEDB02, that is, the DNS modification, and change SQL2005PROD to the IP address of SSEDB02, instead of the IP address of SSEDB01. As long as you modify the DNS, each application automatically connects to SSEDB02 instead of SSEDB01, and does not need to spend time modifying any of the 50 connection strings. When designing a connection, you only need to make such a small application change and use the logical name to represent the SQL Server, instead of the physical Server name or IP address, in this case, the workload will be greatly reduced when all applications need to be redirected to the new SQL Server.

Use DNS for capacity management

Using DNS alias in the connection string helps you manage the capacity. Suppose there are many different SQL Server production servers in your environment. Each machine must support many applications. It is assumed that the databases of some applications basically grow linearly, but a considerable number of application databases do not show a predictable growth rate. The growth rate of these databases varies greatly in different time periods. Sometimes they do not grow at all, and sometimes they increase or decrease exponentially. Some of these databases have a very fluctuating growth rate. As a result, some servers have almost no available space, and sometimes the available space is used up, at the same time, some servers have a large amount of available space. So how can we use DNS to help you manage these disk spaces and solve capacity problems?

When the database is already full of hard disk space on the server, it is not always easy to look forward to more disk space on the database server. It may take several months to obtain additional hardware and design a time schedule for the disk to increase the disk space on the server. Therefore, if there is a problem with your disk space capacity, you need to find a way to make your database Plug and Play, in order to manage this capacity problem. "Plug-and-play" means that you need a method to quickly copy a database from one server to another, the IP address required by the application to obtain data can be modified with minimal effort. By using DNS, you can quickly point an application to a new database address. Of course, you must design your application connection policy to handle such database changes.

Assume that you have a database server that contains databases related to orders, accounting, personnel, and settlement systems. The following four databases are responsible for these applications: Order, REV, HR, and Billing. In this case, you should define different DNS domain names for different applications. You can use DNS domain names such as ORDER, REV, HR, and BILLING. Let the connection strings of each database application use the appropriate DNS domain name to ensure that the application points to the current physical server where the respective database is located. When you need to transfer a database from the current server to another Server due to capacity problems, you only need to direct the DNS domain name of the database to the new database server.

You can also use the logical DNS naming method to solve other problems. Assume that you have a development environment, a quality assurance environment, and a production environment for each of the above applications. In this case, you can add an environment suffix to all DNS domain names. For example, for BILLING, you can change the DNS domain name in the development environment to BILLINGDV, BILLINGQA in the Quality Assurance environment, and BILLINGDV in the production environment.

If you have a database server with a high CPU usage, you can quickly transfer one or more databases from this overwhelmed server to an unsaturated server by using DNS, then, point the DNS address to the new server. This provides you with a low-tech solution to balance the CPU usage of database servers.
 

The methods mentioned above are not a panacea, and they are not omnipotent. Therefore, you must use them flexibly based on your actual situation. We hope they can help you, rather than help you.