SQL Server Management Studio 2012 – What is it?

SQL Server Management Studio or SSMS is a product that is a part of Microsoft’s SQL Server 2012 product and has been available from the time the SQL Server 2005 came out. This is a component that helps manage all the parts of the SQL Server. SQL Server Management Studio 2012 can do the following for all the software modules in the server:

  • Configuring
  • Administering
  • Managing

SSMS is a crucial product and a critical piece in managing the database product; it is necessary to become familiar with the product first. Starting with the SQL Server Management Studio Express studio then seems to be a prudent approach. Getting the express version of the SQL Server, which includes the Management Studio could be downloaded from the Microsoft site. Installing the product should not create any problems. You should invest in a regular version after your team is comfortable with the SQL Server Management Studio 2012.

The SQL server Management Studio is like the control center for the product and helps you manage the features of the major modules of the product. MS has their products available in different configurations. The express version is usually a free product that helps users get familiar with the tool before plunging in into larger configurations that are priced. The SQL Management Studio 2012 product download module includes a complete tutorial. The first step for familiarization should be to study this guide. You are provided an access to the user community. This can help with any queries you may have regarding the product, its installation and configuration.

The SQL Management Studio provides the following features that will help manage the server:

  • A minimal database engine
  • An integrated environment to let you work with the product, and any scripting necessary
  • Data analysis services
  • Reporting services

The Microsoft SSMS 2012 provides an activity monitor. It is possible to schedule actions at different times and to have multiple tool open at the same time. The SQL Server Management Studio can monitor these activities. If you needed an uncluttered and reduced higher level view, it is possible to apply filters.

The SQL Management Studio has an object explorer that can provide access to all types of objects within the server environment. Access to the SQL server agent for administering them in a replicated environment, filtering of schema, name or date is available. The SSMS will let you filter parts of these objects too. Objects inside the database could be populated asynchronously. If necessary, the objects could be filtered with the metadata associated to the objects.

Scripting capabilities keep getting updated with the server version releases. This is supported with templates for quick development; script editing could be off-line, even when disconnected from the server and a source control tool for managing the script sources.

SQL Server Management Studio capabilities could be extended, given the way it is built. However, the Microsoft does not promise any support for extensions users may build. No official documents that may help in such developments are also published. This policy is so strict that when you seek customer support, you need to remove any such extensions if you have any installed.

SQL Server Management Studio – Related Links




SQL Browser – How does this Windows service help SQL Server?

In most practical installations in organizations, the database is usually arranged in a distributed manner. In this arrangement, several clients over the network communicate with the server hardware. There could, of course, be more than one physical server hardware too. In the simplest configuration, this will be a single instance of the SQL Server. However, in a general situation, there will be multiple instances of SQL server running.

SQL Server is assigned a port number 1433 on which to listen for connection request and transfer data back and forth. The communication with clients is established through a named pipe that connects to this particular port. Problem arises when there are several instances of SQL server is running simultaneously. You need a mechanism to make sure the communications with clients happen systematically, and data is passed back and forth between the requested instance of the server and the specific client, even though there is only one port that could be used even when multiple instances exist. SQL Browser is the tool that manages the set of communications exchanges that are needed between clients and an instance of the SQL SERVER. This SQL Browser mechanism was introduced with the release of the SQL server 2005. SQL Browser has been available in the following releases namely the SQL Server 2008, 2008 r2 and 2012 releases.

SQL Browser or SQL server browser (the official name) listens on the specified port and provides information about the instances that are in existence within a server. The SQL Browser works as a Window service and is present in the machine all the time. The service is able to browse the list of servers available with id and software version number of each instance. When the request comes the SQL server also connects the client to the right instance of the database. The SQL Browser service helps connect to dedicated administrator connection end point additionally. In the Express version of the databases in 2005 as well as later versions, SQL Browser is not enabled. The configuration manager of the SQL server will help set up the SQL Browser in the server.

The SQL 2008 Browser works the same way across the versions.

  • The default server instance uses the port 1433, and a named pipe named as “\sql\query”. Server administrator can change the defaults through the server configuration manager, of course.
  • Ports are assigned dynamically to the server instances.
  • When the server instance stops, for some reason, and is restarted, this assignment can change.
  • Thus, client would not know which port number to use for communicating.
  • Usually the SQL Server Browser uses port 1434. Initial request from a client is sent to this port. SQL server, on request, passes on the port number and the named pipe that has been assigned to the particular instance when it was started.
  • The client then uses these specific resources to communicate with the right server instance. The SQL Browser service can be started and stopped by the database administrator as needed.

The browser is a necessary tool for real-life situations using multiple database servers with clients distributed on the enterprise network.

If you would like to learn more, check out our page on SQL Server 2012 Training.


Overview of Transact SQL

IBM is credited with introducing the first version of SQL (Structured Query Language). The initial form of the query language from IBM was named SEQUEL and was standardized as the query language for databases. SQL (sometimes pronounced S Q L) is now a standard of both ANSI and ISO organizations.

Transact SQL or T-SQL is the version of SQL that is modified and extended by Microsoft for their product, SQL Server. It was intended to make transactions on SQL Server versions including SQL 2012 easier. Transact SQL introduces extensions in several areas of the standard SQL language. Significant changes introduced help creating procedural programs like stored procedures. SQL by itself is unable to do that. If one were able to write procedural pieces, these extensions will help writing procedural code blocks.

The changes required cover conditional program control statements (if..then blocks), introduction of local variables, string operations and other support functions. These include math functions, data-related functions, etc. Some changes made to the “Update” and “Delete” commands are also parts of these extensions in Transact SQL. TSQL also introduces a bulk insert command (similar to BCP) that helps insert large amounts of data. The modifications made in SQL Server 2012 T-SQL for program control introduces program structures that provide the equivalent of if-then-else like structure, loop control structure like while loop. A “continue” construct helps continue a loop. Other controls include return, wait for a time function, begin and end of a code block, etc. Useful operations like setting initial values to local variables, etc. are available too in transact SQL.

There are also updates to the “delete” and “update” commands in Server 2012 T-SQL is similar. Both have a variation that helps utilize a table “join” functions. A “from” clause can be used with both the commands, and tables joined prior to doing a delete or an update. The “Bulk Insert” helps speed up situations where insertion of date has to be done on many rows. Rather than issuing an “insert “command for every row, you could utilize this bulk form of insert provided in transact SQL to read off data from a file and insert date in consecutive rows.

Detailed discussions on Transact SQL can be found at TechNet site provided by Microsoft. Transact SQL is the means of communicating with Microsoft’s DBMS SQL Server. Applications will need to issue the Transact SQL statements to the database server to interact with the underlying data. There is a whole range of applications that may need to interact with databases through this Server 2012 T-SQL language. When interacting with the database through an application, one needs to take into account a range of issues. For details, you would need to look up documentation available from Microsoft. But, briefly, following are the issues to be taken into account when utilizing transact SQL with your applications. These are:

  • Transact SQL syntax details
  • Query tools and APIs
  • Process of modifying data, locking of records and managing transactions
  • How to handle distributed queries
  • Tutorial on Transact SQL scripts



Using SQL Server 2012 Agent to create a Database backup SQL job

If you are a SQL server DBA at your company, scheduling jobs and automating Admin tasks is a common practice in your daily life. Of course as the number of SQL servers and the underlying databases grow, this vital role for a SQL DBA can quickly get out of hand. This is where SQL Server 2012 Agent can come to your rescue.
SQL Agent is a separate service in SQL server 2012 DBMS. It has the ability to setup and schedule automated jobs that can run on a predefined schedule. This can include DBA things like backing up your databases on a regular intervals, running import and export jobs, executing a predefined Transact SQL scripts and even deploying PowerShell commands.

Let’s go ahead and dig deeper into some of these components

A visual representation of the MS SQL Agent is as follows.



Create a backup job for Northwind database

We are going to create a backup job for Northwind database using SQL Agent. First make sure the Agent service is running, if not right click on SQL Server Agent and choose Start. Next do this:

Jobs > New Job

You will get a new dialog box. Enter the following info for job:

Name: Backup Northwind
Category: Database Maintenance

Next go to Steps in the left pane. Then click on New in the middle pane. Basically you are creating a job step that will run the backup command for your database.

For Step name type “Run SQL”
Next go ahead and enter the following SQL code:

 TO DISK = N'C:\SQLBackups\Nwind.bak' WITH NOFORMAT, INIT, NAME = N'Northwind-Full Database Backup',

You always want to parse the sql code and test it. When we ran out SQL command, we got the following output which means that it WORKS!

11 percent processed.
 21 percent processed.
 31 percent processed.
 41 percent processed.
 51 percent processed.
 61 percent processed.
 71 percent processed.
 81 percent processed.
 91 percent processed.
 Processed 560 pages for database 'Northwind', file 'Northwind' on file 1.
 100 percent processed.
 Processed 2 pages for database 'Northwind', file 'Northwind_log' on file 1.
 BACKUP DATABASE successfully processed 562 pages in 0.410 seconds (10.690 MB/sec).

Here is what the new job step looks like in our SQL Server 2012 Agent instance.



Generally you should enable logging which means track execution of your SQL jobs.

Click on Advanced in the left pane

Go ahead Check Log to table and make sure to select Append output to existing entry in table

This basically will provide forensics (big word for SQL geeks) on job executions as time goes on. Btw you can also write this to a file on a shared drive. I actually prefer that option over the Logging table. Click Ok. We can certainly add alerts and a recurring schedule to this. For now lets go ahead and click OK to finally create the sql job in SQL Server 2012 Agent.

Now we are ready for the million dollar question, is this going to work??

Let’s find out by manually executing this job. Under Jobs folder find Backup Northwind. Right click on it and select

Start job at Step

Since we have only one step, the job should start doing its magic. If you did everything correctly, you should get this nice dialog box.

Of course you never assuming anything as a SQL Server DBA and always verify the results. We are going to browse to the folder location and look for the backup file. Here we go:




Yep its there, BINGO!

Related topics to SQL Server 2012 Agent

Other important things SQL Server 2012 Agent can do are the following:

Operators: An operator in SQL server is someone who can be notified or alerted when something goes wrong in SQL server. And it can also be used as FYI information when an important SQL job completes.

Alerts: Alerts are notifications. These are customized SQL alerts based on error messages or Severity levels. Think off an alert as a heads-up which can then in turn execute a job or notify an operator.

Proxies: SQL Server 2012 Agent proxies are specific accounts with particular permission so that you can have fine-tune control on your SQL server. This will minimize any related security risks.

Error logs: This is the placeholder for any execution related info that is tied to a SQL server job or its underlying job steps. Basically this keeps a chronological order of all the executions with different messages that get generated as the job is executing. Def helpful for troubleshooting purposes


Follow us on: