The performance of Microsoft SQL servers has vastly improved in the last few years to emerge as one of the leading contenders for database administration activities. SQL servers act as the core fundamentals for almost all online applications. And with the occurrence of numerous cyber-attacks, organizations must follow best industry practices in running their Microsoft SQL servers.
Default configurations add more value to the usability, functionality, and communications while security aspects are often ignored. And these loops can hamper the system’s stability to sustain long-term effectiveness.
At E2E Networks, we held an online webinar for understanding the best practices of running the Microsoft SQL Server. The host was Mr. Jasmeet Bajaj, a Cloud expert from Mieux technologies,who gave the presentation and answered queries along with Mr. Nikhil as the speaker.
Here are the main insights from this webinar for running Microsoft SQL servers that you should follow in fetching higher performance and productivity.
Although there are no specific hardware requirements, they are likely to vary from client to client. Here are the factors that should help you to choose the hardware considerations accordingly:
- CPU and Memory Setup (Use of Ram Cache pages)
- Use of 64-bit hardware for both OS as well as SQL Server
- Memory and Log pages separately
- Dynamic memory should be more than 2008
- The number of users in the present system; after 50 users, you should add 1 or 2 GB RAM for every 5 users for better practicing.
- Execution packages – doubling the RAM can reduce the execution time from 4 hours to 2 hours.
- Rate of Growth
- Size of Databases
- OS Requirements – reserve 2GB Ram for OS default
- One additional for the number of users at the concurrent level
It is recommended to have dedicated drivers, for instance,
- Drive 1 to have database files,
- Drive 2 to have tempDB files, and
- Drive 3 to include the OS, program files, and other basic programs.
On the Network aspect, these are recommendations:
- I/O subsystem
- Planning requirements
- Designing for redundancy
- Partition alignment
Installation and Configurations
Set up storage with database performance, proper allocation, tests, and then enable Instant File Initialization. SQL servers should have a default instance or named instance only for the effective use of SQL server feature selection.
Domain accounts should be part of the SQL service installation and not the default account. There are two authentication modes – Window authentication mode and mixed-mode – that offer enhanced login options for the users.
Treat data files and log files separately for storing as well. Do not go for the same drive as it is considered a bad practice. TempDB’s precise location is also a crucial factor.
Although SQL servers are built on several security practices, still, using default settings can lead to a lot of loopholes in the system. There is an essential need for strengthening the Windows server. Some tips include:
- Securing the operating system.
- Installing critical fixes and service packs.
- Configuring the firewall.
- Lowering the number of employees with access to the Windows Administrator on the SQL server.
- Securing and encrypting remote access.
- Implementing activity logging.
- Restricting all unnecessary services.
You also need to know all the required SQL database components and install these only. System admins must follow the principle of least privileges for the service accounts.
SQL Browser Service
The default purpose of the SQL Browser Service is to offer instance and port information for all the incoming connection requests. Here are some tips for effective implementation of SQL Browser service:
- Effective usage of SQL server instances.
- May be stopped in case the default instance is installed.
- Running securing configuration.
- Administration and management must consider the positive and negative for using browser service for respective use in the premises.
Database Administrators must use highly strong and effective passwords to avoid any misuse and thwart attacks. SQL servers need to be updated regularly and have the latest critical patches to avoid any online attacks.
Implement the use of multiple authentication options for logging into the Windows SQL server options. Never use the same account and disable it immediately as it is the first place for attackers via the brute-force attack.
Have a strong database backup strategy and follow the industry standards with –
- Online transaction processing
- Frequency of schema changes
- Data loading patterns
- Nature of the Data
- Restoring and more
- Have protection against all the SQL injection attacks
Use the precise encryption on a specific location with Transparent Data Encryption (TDE), Always Encryption, and Column-Level Encryption.
Maintaining documentation can have several benefits. System admins must have thorough documentation for newbie and even expert professionals to board the system effectively.
Essentials steps for documentation include:
- Parameters for installation and configuration.
- Current Application Configuration.
- Troubleshooting tasks.
- Database Recovery plans.
- All changes made to the current database.
Also, admins need to make sure that documentation is easily available within an organization for users who need it the most.
These practices define the way organizations must run Microsoft SQL servers on their premises. Here, consistency is the key to following these practices regularly and applying all of them step-by-step. Admins need to make sure that each step has its importance, and following them with the best industry standards will make the data secure for their organization.