A database is a model where the data can be stored logically and can be retrieved for future use. If you are familiar with the Microsoft Excel, you are storing your important data in the tabular form already.

Database is nothing but a similar tabular form of storing data just like the spreadsheets. It contains columns and rows where the columns are composed of attributes and the rows are built by the records.

For example, if you are creating a database of names and phone numbers of your clients, you need the column names such as FirstName, LastName, and PhoneNumber. Then, the rows can be added under the columns as required according to the number of clients. The database can retrieve and update your data as and when required. It can carry out different complex calculations and cross check the records within various tables.

Types of Database

Database can be mainly divided into two different types – flat file and relational.

Flat File

If you are dealing with small amount of data, the flat file database system can be the ultimate solution for your needs. Though complex data can be stored by flat file, the process can be time-consuming and expensive compared to the relational management system. Thus, simple data where the data strings can be differentiated simply by a comma are under the flat file system.

Relational

The database for example, MySQL, Oracle or the MS SQL server can serve the purpose for large and complex data structure in a very logical manner. As the tables can be interlinked with each other, they are named as ‘relational database’.

Steps to Design Database With SQL Server

Step 1

Install The Software – At first you need to install the software from Microsoft. The Mac users can install from DbVisualizer or Squirrel programs which are open-source in nature.

Step 2

Introduce SQL Server Studio – You need to start the program by running the SQL server management studio. If you already have a running server, you need some permission to connect. But if you are creating a new one, then you need to select “Windows Authentication” and insert the database name as (.) You are now all set to connect when you click the ‘Connect’ button.

Step 3

Find the Database Folder – You are required to locate the folder of database. After connecting, the Object Explorer window will open. You may click on the ‘+’ icon to expand the option and then find the database folder easily.

Step 4

Create New – The remote DBA experts will now proceed to create a new database. You need to name the database and leave the rest of the functions to operate automatically. When you type the database names, two files namely; the Data and the Log file will be created by default. The data files store all your data while the log files can track the updates in your database. Now, you will have to click ‘OK’ and the new cylindrical database icon will appear in the folder.

Step 5

Create Table – Select the ‘Tables’ Folder by right clicking on the expanded database folder. From there ‘New Table’ can be chosen for creating and editing your new table.

Step 6

Create Primary Key – The first column in your database table needs to include the primary key. This key can be any item like the Id or the serial number. You may uncheck the ‘allow null’ button and type ‘int’ in the Data Type option. But if you want to allow null, your first entry will definitely be zero.

Step 7

Table Structure – The structure of the table needs to be arranged in the proper way. As the tables are created by the columns and fields, you need to represent the database entry by every column. For example, if you are creating the contact database of your customers, there must be columns like ‘FirstName’, ’LastName’, ‘Address’ and ‘PhoneNumber’.

Step 8

Create Different Columns – When your primary key is filled, you can focus your attention on creating different columns below the primary key. You are required to select the accurate data type for fitting all your information.

• int – This data type is basically used for the whole numbers and can be observed in the ID field.
• nchar (#) – It is mainly used for texts, such as names, surnames, and addresses. The parentheses should contain the maximum number of characters that can be permitted in each field. Phone numbers can also be saved in this format as it does not require any mathematical calculation.
• decimal( x,y) – The decimal form of numbers can be stored here. The parentheses signify the total number of digits in the figure and the number of digits just after the decimal point. For example decimal ( 4, 2) will save numbers as 00.00.

Step 9

Save The Data Table – After completion of the table creation task with different fields and columns, you are required to save the table by naming it. The name of the table must help you in recognizing the content in the database table. The toolbar contains the Save icon which you need to click for performing the action.

Step 10

Add Data In Table – As you are ready with the table, you can now add the data according to your requirement. You can expand your Tables folder from the window of Object Explorer and if the new table takes time to load, you just need to refresh the Table folder. For example if you have added 400 rows, just select ‘Edit Top 400 rows’ by right-clicking on the Table folder.

Step 11

Save Data And Execute Table – If you are ready after saving all your data, just click on the Execute SQL button from the toolbar. The SQL server will perform automatically and go through all your saved data. You can also execute the SQL by pressing Ctrl + R from your keyboard. The errors, if any, will be highlighted before the execution process takes place.

Step 12

Data Query – Now, after the designing process is over, you can easily query your data for all the analysis and reports. The data query reports can thus, be used for the administrative purposes of your enterprise.

Resolving Database Issues In SQL Server

Connectivity Issues

Connectivity can be a very common server issue of the database. When the new users start interacting with the server, the connection may get deployed sometimes. The server may run an excellent connection for many years, but can suddenly lose its sync between the applications and the end- users in its system.

For troubleshooting this problem, you need to focus on the duration of the error generation. The figure below explains the troubleshooting process properly.

If the error is generating within 25-30 seconds, you can be ensured that there are some connectivity problems occurring in the server. On the other hand, if the duration is just few seconds like 5-6 seconds, you can easily assume that problem is related to the configuration and settings or any permission related problems in the server. In the case of 30-second timeout, you are required to ping the host properly and the server can then fail to provide access to the new clients. It is always advisable that the server can be utilized with the specific named instances to avoid this particular problem.

If the connection still acts stubborn, you may carry out the Profiler with the Audit Login and then check the button of Failed Audit Login to identify all the failed events. If nothing occurs, you can be assured that the firewalls, named instance and port listeners are available by default. If any event occurs here, you require further in-depth details about your connection problem.

Architectural Issues

The architectural issues may include the blocks, deadlocks and over locking process. With the increase of the load and the number of end-users, these architectural issues may take a large form in your server. Here, the code is not properly representing the concurrent SQL server model due to the blocks in the consistency of different transactions. Though the problem remains hidden till the increased use of application, still it needs to be resolved for a proper architectural background. This problem is somewhat similar when a car tire is working fine in the smooth roads of the town, but can be accident-prone while travelling in the hilly roads. The DMV query can be the best solution in curing these dormant problems in your server. You can present the query command like sys.dm_os_waiting_tasks to get an idea of the number of queries that are under the waiting status. For example this particular DMV is checking queries that are blocked.

SELECT 
w.session_id
w.wait_duration_ms
w.wait_time
w.blocking_session_id
r.total_elapsed_time
FROM 
sys.dm_os_waiting_tasks
WHERE 
w.session_id>100

The DMV can also check the overall statistics of the server if any task is under waiting condition. Therefore, the blocking problems can be eliminated if the queries can be raised properly. The collision of data can be avoided if the index can work much faster throughout the operation. You can locate the deadlock and can deal with it in an effective way. The deadlock can be addressed by re-orchestrating the operation orders with all the issues in a sequence. The NOLOCK hints option can also be utilized but you must be aware of all the reverse effect of it in your environment of data consistency. You may require expertise skills for troubleshooting these problems of deadlocks. If you go wrong in a single step, the troubleshooting can appear highly expensive for you!

Disaster Recovery

Though data recovery is possible in various steps of troubleshooting SQL, it is always recommended that a proper back up of offsite should be taken for various disaster and contingencies occurred for them. You need to fulfill the Recovery Point Objective (RPO) and Recovery Time Objective (RTO) by regularly updating your existing plan of recovery. The regular updates and testing helps your server to cope with the environmental changes in the midst of the crisis situation.

Alerts And Errors

The regular alert set-up is one of the important tasks performed by the database server. The errors and the issues can easily pop up if the alerts are utilized in a proper configuration. The error message can appear with the technical details and can be very beneficial for resolving the issues. In few cases of exception where the error message is not providing the details, the automated operations are to be performed by various logging options provided by the SQL servers. The search engines can also be of great help if you insert the proper error code in their search boxes. You just have to identify the root cause before acting on the solution of any server issues. If you want to be helped by the social media, you can look out for the #sqlhelp on Twitter which will also offer you excellent options for solving the errors.

Performance Problems

The database performance remains without any complaints until the size is small. But when the size of the database increases, various performance issues can occur in the server. Thus the servers can gradually become unresponsive with some particular queries that run in the background and attracts various issues. Now to identify those queries, you can use sp_WhoIsActive process and find them out. This DMV proves to be a very useful tool during the troubleshooting issues and database performance tuning.

Like any other Windows application, the SQL server database can also attract various issues. Though troubleshooting the issues is not an impossible task, it requires hand-on skill of the database experts. The queries and the operations must run simultaneously with all the backup and antivirus software to secure the database performance. The proper execution of the host of the SQL server depends upon the overall performance and the default troubleshooting capacity. You may find it less expensive to outsource the troubleshooting activities to the concerned team of experts and focus on the right implementation of the database in your organization.