With the advent of SQL Server 2014 Express edition, IT students and professionals are beginning to have a simple and efficient solution for the creation and management of SQL databases. In this post, let’s see how it is easy to install such an Express distribution and then use it to perform common database tasks. To start, let’s have a short intro to the SSMS (SQL Server Management Studio) 2014 as below.
A Short Introduction to SSMS 2014
By definition, Microsoft SQL Server 2014 Express is a free and powerful data management system that allows you to have a rich data store for those lightweight desktop applications and websites. In another words, this SSMS 2014 is bound for providing an integrated environment for maintaining control over SQL databases.
As all Visual Studio tools have the power to manipulate the SQL server from its IDEs, the above-mentioned SQL server tool won’t rely on the front-end to manage the database objects. Besides, its user interface is so comprehensive that you can create, delete and restore MSSQL databases within the blink of an eye.
How to Install Your SSMS 2014?
Firstly, we’ll explain how to install SQL Sever Management Studio 2014. To get this SQL Server tool installation files, visit this download center. Following are the simple steps to set up SSMS 2014.
Step 1: To execute the installer package, just double-click it. Then, choose the option saying “New SQL Server stand-alone installation or add features to an existing installation” to continue.
Step 2: SSMS 2014 Express would check serial setup rules and then transfer you to another “SQL Server 2014 Setup” page. Be sure to read those listed license terms and check the box next to “I accept the license terms”. Just follow the give instructions or refer to this post to complete this installation task.
Step 3: Remember to check all selected installation options during this setup process from the bottom “Ready to Install” tab. If done, perform a full reboot.
How to Start Using this SQL Server Management Studio?
In this section, we will focus on how to carry out common database engine tasks within SQL Server Management Studio 2014. To start, connect to one instance of SQL Server “Database Engine” from the left “Object Explore” window. This requires user “Login” and “Password”.
Create a New Database
In case for any unexpected disaster, you’d better make a prior backup of the master database. More than that, a maximum of 32,767 databases would be created on one SQL Server. Keep in mind that the “Create Database” statement can only run under the default transaction management mode. Follow the listed steps to get the job done.
- Right-click the “Database” in order to choose the “New Database” option. In the pop-up database creation dialog, enter the database name. Also, accept all those default values and click the bottom “OK” to continue. The “Owner” name can be changed via this bracketed “Ellipsis” button.
- The below “Options” section makes it possible to re-define the database “Collocation”, “Recovery Mode”, “Compatibility Level” and “Containment Type”.
- To create a new “Filegroup”, just click on this “Filegroups” option and press the “Add” button to configure the required values, such as “Name”, “Read-Only” and “Default”. Finally, hit the “OK” button to end the database creation process.
Delete an Existing Database
This deletion task only applies to user-defined database and existing database snapshots. As shown, this will show a tree view of all available databases. Simply right-click the needed one to “Delete”.
If done, press the “OK” button to take effect. If this database is involved in the log shipping, then delete the log shipping; and if the database is subscribed or published to merge application, then remove the replication. Again, the system database cannot be deleted.
Add or Delete Log or Data Files
As shown, right-click the database and choose “Properties” from the pop-up list. Enter the “Files” tab and click to “Add” a data or log file.
From the “Database Files” section, create a logical name that will be unique on your database. The file data, type and log are also required. Note that, the file shall be included in the “Filegroup”. When setting the “Initial Size” of a file, try making the data file as large as you can.
The next “Autogrowth/Maxsize” column is where to modify how the file will grow. Click the button with three dots and select from those options:
- Select the checkbox of “Enable Autogrowth” to allow those chosen files to grow when more disk space is needed.
- Select “In Megabytes” to allow the files to grow by specified increments, while “In Percent” option will let the files grow by a percentage of the current size. The default value is 10.
- Select “Restricted File Growth (MB)” to set the maximum file size, while “Unrestricted File Growth” will allow the file to grow without restrictions.
Note that, the amount of disk space can determine the maximum size of your database, and the version of SQL Server determines the licensing limits. The last step is to modify the file path that has existed before adding this file. Usually, the transaction and data logs are put on the same path and drive in order to accommodate the single-disk system.
From the same “Files” page, you can also delete the unwanted file by clicking this “Remove” button. Pay attention that, a file cannot be moved when a BACKUP statement is still running.
Shrink a Database
Shrinking a database means moving pages of data to the unoccupied space, which helps recover space. Once there is enough free space at the end of the file, your data pages will be deallocated. A shrink operation will increase more or less fragmentation, and hence do not enable the “AUTO_SHRINK” feature if possible.
To begin, click the database and go to “Tasks” > “Shrink” > “Database” as below.
Inside this “General” page, you can modify:
- Database – the name of the chosen database.
- Currently Allocated Space – the total unused and used space for the chosen database.
- Available Free Space – the amount of free space in the data and log files.
- Reorganize Files before Releasing Unused Space – the action to execute DBCC SHRINKDATABASE with a target percent option specified. Checking this option will affect performance.
- Maximum File Space in Files – the maximum percentage of free space that will be left after the shrink operation. The values range from 0 to 99.
Do check the “OK” button at last.