Top 7 Hidden Secrets Of SQL Server For A Developer

SQL Server has a massive audience. With the use of it, you may get record-breaking performance on Windows and Linux based operating systems. The major features of SQL Server are: –

Views, Constraints, Keys, Indexes, Triggers, Stored Procedures and Functions, Defaults, Users, User-defined types, and much more. BI Semantic Model supports BI experiences in SQL Server. SQL Server is also used to tackle Big Data.

Considering all the amazing features and some of its drawbacks, here, in this blog, we will discuss the top 7 hidden secrets of SQL Server which will be helpful for the developer.

Safeguards your system

SQL Slammer is kind of a worm which is also known as Sapphire (F-Secure), w32.SQLexp. Worm (Symantec), and Helkern (Kaspersky) which exploits the vulnerabilities of  SQL Server 2000. The impact of this worm is limited to home or desktop systems, and it leaves no impact on Linux, Mac, or Unix based systems. The worm does aggressive scanning, which is overloaded by many networks, and thus, it slows down internet traffic. This worm can be removed by rebooting an infected system. To safeguard your system from this worm, you need to block the following ports of SQL Server at their gateway or firewall-

ms-sql-s 1433/tcp #Microsoft-SQL-Server
ms-sql-s 1433/udp #Microsoft-SQL-Server
ms-sql-m 1434/tcp #Microsoft-SQL-Monitor
ms-sql-m 1434/udp #Microsoft-SQL-Monitor

Also, please do not forget to patch up your system when you discover vulnerabilities.

Data Recovery

Every organization ensures the protection of their data and infrastructure. But no one can predict fires, accidental loss of data, corruption, power outages, or any kind of disaster. In such cases, you may prioritize recovery strategies for the database of an organization by implementing various technical methods of SQL, such as- Page Checksums, SQL Agent Alerts, Consistency Checks, System Center Operations Manager alerts, etc.

Implementing these data recovery methods, you may protect data with backups, replication, log shipping, database mirroring, and potential failover to a redundant system concerning mirroring or failover clustering.

Take and Inspect frequent backups

Even if you have high disaster recovery planning, still you cannot avoid taking frequent backups of the databases. Data which is securely backed up can be restored if in case your data got corrupted. But if you do not have any backups for your firm, you could suffer major repercussions. Taking only backups is not sufficient, you need a frequent practice to reimpose them so that you may have updated data.

Create your client connection use Shared Memory Protocol

To make the client connection using Shared Memory Protocol, the initial step should be permitted on the client. You may check the status of the permitted protocols using SQL Server Configuration Manager. Proceeding, use local as the server name in your connection String to impel the client connection to use the Shared Memory Protocol. For this, you may use localhost or a period operator (.). See the example below-

Server=(local);Database=TestSuccessful;Trusted_Connection=True;

The following query will show which protocol your client connection is using.

SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;

Code Security

The Database is the collection of valuable information, and because of this reason, databases became the primary target of the attack.

“Kaspersky Lab, a Moscow-based security company, admitted that a database containing customer information had been exposed for almost 11 days and that it only learned of the breach when Romanian hackers told the firm about it in 2009.”

Let’s see how vulnerability looks-

txtUserName.setText(“andy01′ OR 1=1”);

query = “SELECT username, password FROM users WHERE username = ‘” + txtUserName.getText() + “‘;”;

// Final statement

query = “SELECT username, password FROM users WHERE username = andy01 OR 1=1;”

You might have found the vulnerability in the above code. The query will end up selecting all records of username and password from the table because one is always equal to 1.

Here, a developer should know that authentication methods in SQL server are less secure. Thus, as a developer, you should know that you have to secure the code by creating secure queries and also when you found such vulnerabilities in the database, use SQL Server authentication methods.

Normalization of Data

Normalization of data is a technique to organize the contents of the database. In the absence of Normalization, the database system will be slow, inefficient, and inaccurate. The “levels” of normalization is known as a “form,” and there is a total of five normal forms. 1NF (First Normal Form) is the highest and 5NF is the lowest form level of normalization.

Normalization is a required concept in the database because when you have a small database of, let’s say, ten tables and 200 rows. Such a small database is easy to manage. But in case of a large amount of data, you will remember Normalization. Let me tell you that the most important level is 3NF; you might skip the initial levels. The 4NF and 5NF are more luxurious in database development.

Tutsplus has explained the learning strategy to learn the first three normal forms. “The key, the whole key, and nothing but the key.”

Joins

The SQL statements for a single table are easy to write. Depending upon the size of business, more complex queries can be written. SQL Server has the facility of Joins that is used to combine two rows or two or more tables, based on the related column between them.

• There are mainly two types of joins: Inner Join and Outer Join.
• The inner join is used to fetch matching records.
• The outer join is used to fetch unmatched rows from two tables. Outer join has three sub-categories:
• Left outer join (It will return all matched and unmatched records from the left table)
• Right outer join (It will return all matched and unmatched records from the right table)
• Full outer join (It will return all matched and unmatched records)

There is another join named Self Join which joins the table to itself.

• For example,
• STUDENT TABLE
• -StudentName
• -RollNo

Conclusion

The organizations that are looking to position themselves to become a leader as per the availability and in the progressing area of Big Data or any other database solutions, SQL Server is really helpful, all thanks to a step forward taken by Microsoft. Using SQL Server your systems will run smoother, you’ll get organized data, and as a DBA the technology will be fruitful for you. Happy Learning!