The Microsoft RDBMS product SQL Server is available in several editions. The most common ones are the Express, Standard and the Enterprise editions. Standard versions of each release will have more features and capabilities than the Express version. SQL Server Enterprise edition, in turn, has more capability than the standard editions of each release. Depending on the release version of the product, there may be additional versions.
For example, the 2012 release has a business intelligence edition. We shall concentrate on this latest product here, the SQL Server 2012 Enterprise version. While the Express edition is for familiarization, the SQL Server Enterprise is for serous applications like implementing high capability data centers with extensive database and business intelligence solutions.
Some of the notable features of the SQL Server Enterprise edition 2012 include the following:
The huge amounts of data that gets generated today needs to be managed and stored. One never knows what kind of insight is available from the big data collection. Besides the efficient storage and management implemented in SQL Server Enterprise edition, it ensures the ability to view the data to discern any valuable patterns. An additional tool of Power Pivot in the Power View feature of the SQL Enterprise version helps users to access and combine data from other sources to derive meaning to data visualizations being made. Support of cleansing of data helps the IT department to ensure BI solutions that are dependable. There will be no need to drill down to low-level data to ensure they are good data.
Whenever a new version of a product arrives on the scene, a question that arises without fail is whether existing users should upgrade. ROI percentage and the time it takes to recoup the investment are good metrics to help you decide. According to a Forrester study commissioned by Microsoft for an upgrade to SQL Server Enterprise 2012 has indicated that ROI percentage is 189%, and the payback time is just one year. In order to learn more, check out SQL Server 2012 Training.
Microsoft released their product SQL Server 2008 r2 in 2010. As it is usual with their products, MS SQL Server 2008 R2 also has an Express edition that is available for evaluation and use in small systems. Microsoft SQL Server 2008 r2 is available in Standard and Enterprise editions. Having tried out the SQL Server 2008 r2 Express, you would eventually graduate to either the standard or the Enterprise editions depending on your actual needs. Two other special editions also were released with the MS SQL Server 2008 R2. These are the SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse. These two special editions were introduced to meet increasing demands for datacenters and parallel data warehouse applications (Business Intelligence applications).
As with other versions, Microsoft SQL Server 2008 R2 Express edition is supplied free by Microsoft. This facilitates your team becoming familiar with the database product, get used to developing web based and desktop applications, etc. You will need the help of Microsoft Visual Studio and Visual Web Developer Express.
Some features added over the MS SQL Server 2008 R2 include the following:
For getting started with the 2008 R2 you need to download the Express edition first. It is fairly simple to download and install. It is free to use, but you will need to get the product registered with Microsoft. Your team can start getting familiar with it right away. When the team gets into applications development activities, there is another issue you need to take into account. All such database applications need to have the database as part of the application and then the question is if you can re-distribute the database to a third party (your customer). This too is free for the Express edition, but you will have to apply and get the right approved by Microsoft.
The so-called core editions of Enterprise and Standard are the most popular ones of the SQL Server 2008 R2 product. The SQL Server 2008 R2 Standard is positioned as the database tool for data management as well as business intelligence for departments and also the small and medium business segment. This configuration is supposed to take up minimum of IT resources. Some new features in the Standard edition are:
The SQL Server 2008 R2 Enterprise is capable of delivering a data platform but provides the user a secure, highly available and scalable product. Business intelligence features are supported. Some new features in the Enterprise edition include:
The Microsoft SQL 2008 R2 also offers a free Compact edition for applications on mobile devices besides desktop and the web.
In simple terms, database management software is a set of software tools that help manage databases. To understand other functions of database management software, one needs to look at what databases are and what they do? Databases are repositories of data. Business and other applications access this data, manipulate/modify it and store it back or may even delete the data records. How the data is organized internally affects how effectively and efficiently data could be found and manipulated. This structure or the arrangement of data inside a database has evolved over time. From hierarchical and networked models, most database today are relational. Data in these relational databases are organized in a tabular fashion. Database management software will make sure such structures are maintained properly. This organization is much like the spreadsheet kind of arrangement like Microsoft Excel. Each row has information or data items that are related to the information contained in the row or record. Most of us are familiar with phone books and they are typically organized in a table. One row of the table is dedicated to details of contact about a particular person. The items in the columns or fields are attributes or information/ data elements related to that person. Names (may be split as first and last names) and phone number would be the minimum amount of details. Work phone, home phone, mobile phone and any number of other details (birthday, and other anniversaries and so on) could be held in such a tabular arrangement.
Some examples of info in a database are:
Why do we need database management software then? Well it is apparent from the prior discussion, you may need to create different kinds of tables, modify its structure sometimes or remove the table entirely. With the relational model it is possible to have multiple tables in a database where another table may have related data. Any organization will need many types of data to be organized in unique databases. Database management software helps with creation of all kinds of database you need.
A DBMS or database management system is a collection of software that helps creation of these databases. The database management software system also implements a query language. The query language lets the user search for relevant data specified through the query language. With relational databases this query language is the SQL or structured query language and is supported by all the relational database management software systems (RDBMSs). Though supposedly standard, each manufacturer has a little twist to the language support through their DB management systems. Essential these are some proprietary extensions to the core functions of the operations defined in the SQL standard. When these data searching and manipulations are carried, one needs records of what operations and changes were done. Thus report generation is an important function of the database management software systems. Often the reporting functions are supported by graphical charting functions to help add meaning to the reported information easily. On-screen viewing and printing support are other functions provided. One important set of features help configure the database management software system and tweak parameters to fine-tune performance. Some examples of RDBMS are Oracle, SQL Server, DB2 and MySQL. A lot of housekeeping work including backup, restore and disaster recovery, etc. are often included in database management systems.
What is SQL or Structured Query Language becomes easier to understand if we look at database systems first. A database is a repository of data vital to the activities that need large amounts of data to be stored. Businesses are examples of such activities. Usually such data is used again and again. That requires the data to be organized and easily accessible. Retrieval or update of such data is required to be accurate. Relational databases allow users to set up flexible search criteria to accurately locate a specific data item. Relational databases organize data as tables of rows and columns. RDBMS lets users create flexible queries and locate data accurately. Query language offers standardized syntax to specify such queries. When found, operations possible on the data using Structured Query Language are:
RDBMS or the relational database management systems use SQL programming language. SQL is a standardized form of proprietary query languages used by the product companies. Structured Query Language is a standard of both ANSI and ISO organizations. Standardization makes migrating easier. Substantial changes to the SQL statements used in an application should not be required when moved to another database.
Many of the database products use SQL. Major, well-known database products available are Oracle, IBM DB2, Microsoft SQL Server, etc. Open source MySQL is a very popular product. Structured Query Language available with each of these products offer the core functions. Unfortunately, these companies implement additional functions. The core functions offer select, insert, create, update, delete and drop, etc. These are really sufficient to implement most operations one needs. SQL of these products coupled with the extensions are known differently. T-SQL is offered by Microsoft in SQL Server product while the Oracle version is called PL/SQL.
Distributed database applications, typically, work with a central database server known as the backend. Client applications communicate with this back-end to retrieve data and manipulate them. These clients issue queries for data with Structured Query Language statements. The database server is then updated with the changed data.
SQL is able to change/modify data in the RDBMS. Besides, it is able to change the structure of the database tables. Structured Query Language statements will let a user create a table, modify it or even entirely delete it. What SQL statements can do with data is insert data into fields in the table. What SQL lets you do is to “select” a specific table for modifications. A SQL “update” command changes the value of a piece (field) of data.
If you need to work with RDBMS applications, the core functions that constitute what is SQL would be easy to learn. There are only a few commands that are used. Learning the syntax of how the command word and associated parameters are used is quite simple too. Even when your application needs to migrate to any other RDBMS, it should not be difficult to manage. Optimization can be implemented through the core SQL first, the product specific extensions can be done as a next step.
ODBC is the abbreviation of “Open Database Connectivity”. It is a standard that specifies how an application using data from database will interact with a database. ODBC is a standard adopted by the IT industry. The standard specifies the commands that can be issued irrespective of the actual database product. How database products (both RDBMS and non relational ones) respond to requests for finding data, modifying or editing them as also how the requests are to be made vary from product to product. Open Database Connectivity strives to make the requests from applications the same irrespective of which database product serves the data. Databases, on their part, interpret these requests and process data according to their own internal processes (requests and command syntax) to respond the same way irrespective of its make.
With this kind of standardization, it is possible for any application to write front end that issues the standard requests to databases. This is the front end part of the ODBC standardization/ specification. This makes it possible for any application to issue data requests that will be recognized any ODBC compliant database. In fact, the application with this kind of Open Database Connectivity compliant front end would be transparent to what database is going to supply the data requested by it. The database could be any ODBC compliant product, working on any computer on the network and on any operating environment. Like the application front end needs special code to transform the requests from the application to standardized requests, the database needs a driver that will recognize the Open Database Connectivity requests and transform them to commands in specific syntax and sequence required by the given database. These drivers are, obviously, specific to a database product of a particular company. This driver is installed with the client application, the database could be anywhere on the network. The chain of modules for getting ODBC compliant work done is as follows:
Database serves up data requested and changed made by application up the same chain. After this ODBC standards have been complied with creating an application with a database, removing the parts and replacing them with another is a simple affair. As long as the standard connections through the front end and the Open Database Connectivity driver are maintained, substituting the database with another or an application with another is as simple as changing the tire in a car. It does not matter if the tire is from Bridgestone, Michelin or something else as long as the dimensions (specs of database) of the tire are similar. One could write monolithic application that has the database as a part of it. The application communicates with the database in its native protocol. This will be alright if there was no need for any change during the lifetime of the application. If there is a change needed, you will need to re-write the application all over again. The flexibility arising from ODBC standardization will be lost entirely.
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:
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:
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.
Visual Studio 2008 is a development system by Microsoft. It is an integrated development environment or IDE. An IDE usually has features that help write code, manage the source code, create the object code, run it and help debug it. Visual Studio 2008 is an IDE that supports several software tools that help is each of these departments. While Visual Studio has many editions, it is from Visual Studio 2008 version that has integration with SQL Server editions, starting with the SQL 2005. There have been several versions after Visual Studio 2008 too. Because of this, connection mentioned, it made sense to study Microsoft Visual Studio 2008. Visual Studio makes available multiple tools for each of the developments, testing, deployment, integration and management of a software system for and given application. Not only, there is a comprehensive set of tool but development across devise type is also supported. Your target device could be the web, a PC, a server or some mobile device.
Visual Studio 2008 integrates tools, servers as well as services. It offers these features to make development and solving related problems relatively easy. So much so that not only experienced developers benefit from it but even beginners are able to get used to the tools and services quite easily. Productivity in development is helped by code editors that are quite efficient. Intellisense feature and wizards help developers manage their development functions. Intellisense is like auto-completion so that code statement’s completion is helped by the system automatically prompting the required parameters. That would avoid mistakes and make code development faster in Microsoft Visual Studio 2008 environment.
Life cycle management support through tools like Visual Studio Team System is available. MS Visual Studio Team system is a team collaboration product. This tool can help developers with work-item tracking and provide a team portal. By tracking the work items, it can manage version control too. Build management, business intelligence and process guidance are other features available through this component of Visual Studio 2008.
Visual Studio 2008 offers the team system that is a combination of products. These are:
Visual Studio 2008 is available in two major editions; the standard edition and the professional edition. Microsoft Visual Studio 2008 standard edition provides an integrated environment as described. It is a complete development environment for applications for the web or the Windows.
Visual Studio 2008 Professional edition provides additional features beyond the standard edition. Developing ASP.NET or .NET AJAX based supplications for the web or for the different Windows versions, this tool will let you work with SQL Server databases as also mobile devices. This Visual Studio 2008 Pro version also has unit testing built in to get software modules tested early on. This tool helps integrate Microsoft’s productivity suite easily too.
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 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.
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:
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.
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:
BACKUP DATABASE [Northwind] TO DISK = N'C:\SQLBackups\Nwind.bak' WITH NOFORMAT, INIT, NAME = N'Northwind-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO
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!
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