What is a Database – Just an Overview

What a database is simply a repository of data. Data are pieces of information that is important for some purpose; business or otherwise. Typically, you would need to add to the repository, pieces of data may need to be updated/ modified. When a piece of data has outlived its purpose, users would like to delete the same. A software system that lets you manage data and meet your needs as described is a Database. Examples of databases we are familiar with would be,

  • Phone directory
  • Phone book on your cellular phone
  • Information about all your employees
  • Hospital records

RDMS or relational databases are in popular use for storing such data in an enterprise. These use an organization known as tables. These tables themselves are organized in a manner familiar to all of us as rows of information that has columns to store related pieces of data for that row. Tabular organization helps searching flexibly. Relational databases allow relating multiple tables together for the data finding and manipulation purposes. This is something we do at personal levels too. Our telephone book is a prime example of this organization. A name with first name and second name, telephone number, possibly the address goes into such a phone book. Often important dates like birthday, wedding anniversary and other information are included. One such entry will be a record in a particular database row. Each of the items will go into a column that is common to all the records. Some well-known and popular databases are Oracle, SQL Server and MySQL.

Sometimes the database may be organized in a distributed manner. The data are placed at different physical locations and parts of the database may have copies at different spots on the network.

An associated concept to a database is a database management system. These database products let you store multiple databases and have very large capacities to do so. In a typical business, you may need to store data about customers, sales made, details of your inventory and so on. There is a need for a management process to let the diverse databases work properly. The DBMS or the database management system lets read or write access in a disciplined manner. Authorization of users is managed by the system. Analysis of usage and performance are also provided. Then, of course necessary types of reports are usually provided by these database management systems.

Database and database managers are available on the full spectrum of systems; from mainframes to personal computers and everything in between. Major difference is in the capability of the amount of data that could be handled by the different versions of each product. In all these incarnations of the database products, it is the relational database that is currently used predominantly. All the products use a form of a query language that has become an ANSI as well as ISO standard. This is known as Structured Query language or SQL. This language lets the users specify queries to find data, manipulate or update them. It has commands for changing the structure of the database too.


SQL Server Enterprise – The most powerful edition of SQL Server

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:

  • Data quality services that help profile, clean up and match data ensuring the quality of the data going into the database is dependable
  • Power View or a means for data visualization and exploration  by users conveniently
  • Distributed replay features helps testing applications, configurations and upgrades. This can be very handy in SQL 2012  Enterprise adoption in the organization. A range of free tools are available so that users can be sure of a hassle-free  transition to the new version
  • Enhanced audit facility helps flexibility in auditing, and making audit possible in all versions of the product. The audit trail thus created in SQL Server Enterprise helps ensure all activity by authorized people can be tracked
  • SQL Server Enterprise has an AlwaysOn feature that manages higher uptime, quickest possible fail-over and utilization of hardware. The SQL Server AlwaysOn feature has been newly introduced and ensures higher availability and business continuity in case of disasters
  • xVelocity features in the SQL Server 2012 Enterprise help in a significant rise in performance in the intended applications of business intelligence and warehousing. xVelocity supports in-memory data processing technologies and can boost performance by a factor of 10 to 100 times.

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.


Database Management Software – An Overview

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:

  • Employee information
  • Flight data information
  • Movie ticket booking
  • On-line shopping support

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 ODBC – An Introduction and overview

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:

  • Application issues requests to the front-end
  • Front-end (part of ODBC requirements) issues standardized requests
  • Open Database Connectivity driver specific to a database product, issues commands as needed by the product

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.

Related links to ODBC

  1. Open Database Connectivity – MSDN
  2. ODBC – A Technical overview
  3. SQL Server 2012 training
  4. What is an ODBC compliant database



Visual Studio 2008 – An Overview

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:

  • The architecture edition is helpful in designing a distributed system that is well-designed and can be validated
  • The database edition provides a tool for managing the database and changes made to it
  • Insecure, inefficient or poor-quality code development ca be prevented through the development edition. It provides the means of unit testing
  • Test edition and test load agent modules helps with managing all the testing you need to do.

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.

Microsoft Visual Studio 2008 – Additional Reading




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


SQL Server 2012 Analysis Services – An Overview

Microsoft SQL 2012 Analysis Services or SSAS has proved to be a major step for Microsoft for the company has been able to project itself as a leader in the field of voluminous data. It promises new, unique and customized solutions for the clients. Microsoft has released a ODBC driver for MS SQL Server that will be capable of running in a Linux environment, allowing the new version to deal with large amounts of data. PowerView with SSRS allows the users to create and consolidate BI reports from all through the Enterprise.  The new version is also loaded with distributed relay that allows capturing of workload on the production server and playing the same on a different machine. This allows testing of the schema forming the basis, the support packs and hardware under specific conditions.


SQL Analysis Services

SQL Analysis Services are a layer of meta data or a semantic model that resides on the top of a data warehouse in a relational database. This is like an extra layer that stores information about how the fact and the dimension tables in SQL Analysis Services are to be joined and how measures are to be aggregated. It can also be said to be a cache that results in speedy reporting. Before we discuss the Analysis Services features available in SQL Server 2012 in detail let us briefly look at what SQL Server 2012 SP1 has to offer.


SQL Server 2012 SP1

It supports Power Pivot MS Excel 2013 and SharePoint and it comes with a brand new architecture based on utilization of Excel utilities for querying, refreshing, loading and saving of the data, which are supported outside the SharePoint 2013 farm. The experience of the user is further enhanced through spPowerpivot.msi which is nothing but an installation package for the windows environment.

This is the result of additional features like the PowerPivot Gallery (SSAS) and the management dashboard. It also incorporates latest Analysis Services in the Tabular mode which includes KPI storage and measures; categorization of data; character extensions and hierarchy annotation. SP1 allows specifying the level of compatibility at the time of creating new Tabular model projects.

SQL Server 2012 SSAS in addition to the earlier features is loaded with new features that help in decision making and data analysis. A prominent feature is introduction of enhanced PowerShell Support and replacement of the Analysis Services Unified Dimensional Model with a hybrid BISM model (BI Sematic Model) permitting BI to be experienced in the SQL 2012 Server environment. The article discusses new features of Analysis Services in detail. For achieving better understanding the new features have been clubbed under various headings.

The new features available with SQL Server 2012 SSAS can be clubbed under seven heads:

  1. SQL Server 2012 Instance/Monitoring
  2. Analysis Services Tabular Modeling
  3. SSAS Multidimensional Modeling
  4. PowerPivot for Excel
  5. PowerPivot with SharePoint
  6. SQL Server SSAS 2012 Programmability
  7. Design Tools

Discussing each of the heads and the features that can clubbed under them in detail:

SQL Server 2012 Instance and Server Monitoring

When it comes to new features in Server Instance and Server Monitoring, one of the features is – Availability of 3 analysis services modes. Three server modes Multidimensional and Data Mining which is the default mode, PowerPivot for SharePoint, and Tabular are available for analysis services. The type of solution that is created and deployed depends on the type of server mode. The snapshot illustrated below shows the three deployment modes.

Figure 1: Snapshot illustrating the three deployment modes

In addition to this the xVelocity In-Memory Analytics Engine is available for Tabular Modeling model DBs. Using the in-memory storage it does calculations for collecting the data and manipulating it the moment it is requested.

The next feature that has been added is making available new schema rowsets and a DMV query interface to support the xVelocity engine for data warehousing and latest architecture of SQL Server 2012 PowerPivot service pack 1.

The feature to trace events is yet another important aspect of this version which makes it supportive of the SQL Server Extended Events Framework.

This can be enabled with the help of an XMLA create object script as shown in figure 2 below. For stopping tracing objects, the object has to be deleted using the XMLA delete object script as shown in figure 4 below.


Figure 2: XMLA create object script for enabling SQL 2012 Sever Extended Events Framework.


Figure 3: XMLA delete object script for stopping SQL Sever Extended Events Framework.

Analysis Services Tabular Modeling

The second head of features are clubbed under Analysis Services Tabular Modeling. The features that are available under this head are – Integration of the Tabular Designer Model with  SQL Server 2012 Data Tools, availability of administrative support for tabular model databases that can be independently on an Analysis Services server. This version also introduces the SSAS Tabular Model Designer Diagram View which displays inter related tables using a graph and allows filtration of columns, hierarchies and measures. Another feature that can be discussed under this head is partitioning of tables into logically partitioned objects allowing processing of each partition independently. There are some excellent features related to security. While allowing definition of the security roles by defining the permissions of various members included in a tabular model it also allows row level security restricting the access to data.

KPIs can be used for analyzing the performance of a variable by using base and target values. Hierarchies that exist in the form of metadata can be used for determining the relationship between columns. These can be made to appear separately from the rest of the columns. This particular version has no restriction in terms of number rows that a table can contain.

Two important features in this version are – The DirectQuery Mode and the DAX. The DirectQuery Mode that is available in this version provides with an option to deploy in tabular projects. This allows the users and the reporting clients to retrieve the data right from a MS SQL 2012 Server. DAX can be used with both PowerPivot Multidimensional Modeling and Tabular Modeling allowing use of statistical functions, search functions and table functions.

SSAS Multidimensional Modeling

The next head under which features of this SSAS release can be clubbed are multidimensional modeling. The release carries a new option where it is possible to remove a limitation on file storing strings allowing the files to grow as big as they can. In the earlier versions the maximum size of this file could be 4 GB at the most. Any file beyond this size would face an error. It does not mean though that there is no limit on the file now but fortunately it is not regarding the size any more but only limited by the count of strings that could be place in this file.

Other important features available in the release is collection of metrics related to resource usage right at the cmd level while processing a query & is known as Resource Usage Reporting for SSAS Multidimensional Databases.

Two more enhancements over the earlier versions that can be clubbed under this head are – the ability to trace events for the purpose of troubleshooting lock related query or problems related to processing and using DistinctCount in order to help in running the process much faster for the operation gets over to the relations DB engine. Queries can run faster in the DistinctCount process if the criterion that has been laid down is fulfilled. The performance improves as a result of the fact that the operations are offloaded and placed on the relational database engine. The optimization process is not available as a default action. Optimization requires that the following conditions be fulfilled – Presence of one partition for every measure group; absence of random shapes in queries and the relational DB engine should be on SQL Server 2005 or a later version.

PowerPivot for Excel

SQL Server 2012 SSAS introduces PowerPivot for Excel. Authors can be added and workbooks published using Excel 2010 in SQL Server 2012 and as mentioned above this release also introduces the DAX functions that allows introduction of new statistical functions.

The next two screen shots display more info about SQL PowerPivot



PowerPivot with SharePoint

For the release version 2012, a group of features can be clubbed under the heading PowerPivot SharePoint. This version provides an excellent tool for the purpose of installing PowerPivot SharePoint. The first step that the tool undertakes is to perform a scan and determine if PowerPoint configuration is there so that if not, the necessary steps are provided for deployment of operational server. The release also introduces PowerShell Cmdletss for configuration of PowerPivot SharePoint. By using a combination of SharePoint Cmdletss with the newly introduced PowerShell Cmdletss, a PowerPivot SharePoint can be configured. Another feature under this heading is known as a BI Semantic Connection which provides a HTTP endpoint to Analysis Services DBs deployed on a server in the standalone mode. This release provides an improvement in the administration of PowerPivot SharePoint deployment by addition of extra settings. These help detection of problems in advance.

Another feature that is available is the ability to configure automatic upgrade in PowerPivot SharePoint which ensures that the workbooks in SQL 2008 R2 are always up-to-date in tune with the most recent version. This allows data to be refreshed for SQL Server 2012 PowerPivot SharePoint Server workbooks.

Here is the architecture of PowerPivot SharePoint  with relation to SQL 2012.


SQL Server 2012 SSAS Programmability

As discussed above the release version 2012 provides DAX functions in both tabular projects and PowerPivot workbooks that are built in the Microsoft SQL Server Data Tools. The release has introduced more than thirty new functions in various categories like statistical, table and search along with security at the level of rows.

Another distinctive and important feature of this version is introduction of PowerShell Cmdlets for AMO enabling navigation, the ability to connect via cmd line, and addition of Analysis Services DBs/databases running on a server in either mode namely Multidimensional Modeling or Tabular Modeling.

This release version also introduces PowerShell Cmdlets for the purpose of configuration of Power Pivot for SharePoint. This can be done by using both SharePoint PowerShell Cmdlets and new ones. A PowerPivot SharePoint installation can be installed through the PowerShell script.

Version 2012 also provides extensions for Automation Management Objects as well as XML for Analysis which are made available in order to provide support for Tabular Modeling and both these types of models use the united BI semantic modeling schema. It helps to utilize different kinds of Application Programming Interfaces. Servers running in either mode can be connected using this feature with the help of Object Linked Database or ADO Multidimensional Modeling.NET.

Last but not least yet another feature which falls in this category of programmability is the CSDL or Conceptual schema definition language which is always useful for representing the tabular model as a response to a query initiated by a client. The nature of the query is such that it can use the schema and the representation for creating visualizations like the Power View. An additional feature to Conceptual schema definition language is the bi: extension which includes various entities, data types and relationships for the tabular models.

Design Tools

In addition to the above, the SQL Server 2012 provides updated provisions SQL Data Tools that can be run in the Visual Studio shell. Tabular and Multidimensional projects are created in the SQL Server 2012 Data Tools and the Visual Studio shell incorporates many IDE enhancements like better readability and maintenance of more than one monitors.

Related links to SQL Server 2012 SSAS



Overview of SQL Server 2012 Licensing

SQL Server 2012 is package loaded with features and promises to deliver phenomenal results in terms of improvements in performance. Reading or acquiring information about the licensing policy and procedure may be the last thing that a SQL professional would like to do. But for availing the best features of a product and to get maximum benefit from the same it is an important act to be done.

A specific feature and objective of SQL Server 2012 is that it presents information in an altogether intelligent manner to the organization and assists the organization to analyze the data and the information. Some of the features helping SQL 2012 Server deliver the above mentioned objective are AlwaysOn availability, Contained Databases, Uptime enhancement and ColumnStore Index a fast query tool.

While on the other side, the new licensing policy at Microsoft results in increased expenses for the customers. It is also important to bear in mind that to take full benefit of the enhanced features of SQL 2012 means that enhanced network bandwidth is required which is again an additional burden on the Information Technology Department.

SQL Server 2012 is available in 3 versions – Standard, Business Intelligence and Enterprise. The maximum number of new features are available in the SQL 2012 Enterprise Version. Microsoft has also proceeded to replace the licensing model based on per CPU basis to per core.

For the earlier versions of SQL, only one license was required to be bought for every physical processor irrespective of the number of CPU cores in it. Smart choices made at the time of buying server hardware allowed buying up to eight CPU processors for the price of a single SQL Server license. This would allow saving of licensing fee. For procuring the license for SQL Server 2012 for the same server, one would need 8 core licenses. While the new core license fee is reduced than the earlier per CPU fees, but in case someone has an aptitude to do some serious mathematics, it is easy to see the SQL 2012 Server’s prices have increased considerably.


Figure 1: Comparative Features of SQL 2012 Editions

SQL Server 2012 Licensing Tips

Microsoft released a licensing report for all its customers thinking of associating themselves with this upgraded, brand new technology. Reports by independent agencies like Kirkland brief the SQL 2012 licensing requirements and also offers advice on what issues to take into consideration before buying SQL Server 2012. The important licensing tips are:

  • Understand the Need: In order to make the right decisions regarding licensing, it is imperative to look at and understand the IT requirements of the organization and have complete information about the emerging technologies and the licensing requirements.
  • Understand the Costs: Organizations are required to pay the Server and Client Access License (CAL) costs for procuring a license.
  • Know what is being offered: The process of licensing with respect to SQL Server 2012 has been greatly simplified by Microsoft. Licenses are offered for two SQL editions – Datacenter and Standard. Largely the capabilities of the two editions are identical with one major difference that the Datacenter edition offers unlimited virtualization rights. Allocation of server licenses for Windows Server 2012 is done following a 1: 2 ratio, where one license offers coverage to two processors.

Licensing in case of up gradation from Windows Server 2008

Organizations may also like to procure up gradation rights from Windows Server 2008 R2. This is only possible if their license is covered under Software Assurance. As different editions are offered under Windows Server 2008 R2 licensing, up gradation to Windows Server 2012 requires that costs be calculated. To put it in simple words, it would prove less costly in case an organization resorts to licensing Windows Server 2012 for organizations that have “modest virtualization workloads”.
Before proceeding ahead with our discussion, it is important to understand Virtualization Rights.

Virtualization Rights

Microsoft advertises that the Datacenter edition of Windows Server 2012 offers “unlimited” virtualization rights. Even though an organization may have unlimited virtualization rights there is an upper ceiling limit with reference to the number of virtual machines that can run without resulting in any loss of performance. Experts have expressed their an opinion meaning that stacking of licenses (putting two Windows Server 2012 Standard Licenses on one single physical server in order to avail rights to four VMs).

It is also important to determine the number of servers available and how the VMs would be moved around the organization. There are also restrictions on how many times the licenses can be moved between the servers. This is important for volume license cannot be reassigned from one server to another within a period of 90 days.

There is also a possibility that an organization may be required to downgrade from Windows Server 2012 to 2008, in order to run an organization that may not be ready for Windows Server 2012. Down gradation is permitted in case of Datacenter editions.

Another thing that requires the notice of organizations is using the step up license purchases for this is available to Software Assurance customers only. A step up license purchase lets organizations swap a license to a lower edition with a license to a higher edition after paying the price difference.


Overview of SQL Server Management Studio

SQL Server Management Studio aka SSMS can be defined as an environment that is integrated for managing the SQL Server Infrastructure. It is a combination of tools that can be used for configuring, monitoring and administering the SQL Server.  SQL Server Management Studio also makes available tools for deployment, monitoring and up-gradation of the data tier components like data warehouses and databases that are brought into use by applications and construct queries and corresponding scripts.

Benefits of SQL Server Management

There are a number of benefits of SQL Server Management Studio, the most important being that it is a comprehensive utility that manages SQL Server objects. It combines a graphical interface and capabilities related to scripting. It can also be used for managing Database Engine, Analysis Services, Integration Services, and Reporting Services

Features of SQL Server Management Studio

SQL Server Management Studio supports a number of highly developed features. The general features of the SQL Server are that it is able to support most of the administrative functions and an environment that is integrated and single allows effective authoring and management. There are dialogs that for managing various aspects like objects, Analysis and Reporting Services. Here is a screen capture of what SSMS looks like for SQL Server 2012.


It also supports new Scripting Capabilities. This is supported by the integrated script editors that are contained in the SQL Server Management Studio. The script editors allow authoring Transact-SQL, MDX, DMX, and XML/A. The main features that SQL Server Management Studio supports under this head are a dynamic environment allowing access to information without any loss of time, various ready templates and the ability to customize them, renders support for functions like editing and writing queries and scripts while providing a new interface that allows XML results to be viewed. The Object Explorer part of the SQL Server Management Studio filtration using the entire name, date or schema or a part of it and the asynchronous members are filtered using the metadata. SQL Server Management Studio supports plug-ins and add-ins (extensibility) for it s built upon the Visual Studio Isolated Shell. This allows usage of extensions that have been developed by third parties.

Tool Windows in SQL Server Management Studio

For all stages of development and administration, the SQL Server Management Studio provides a number of powerful tool windows. Some of these tools are capable of being used on any SQL Server part while others are limited to only specific components. The tools that can be used for all the components of SQL server and their purpose are enumerated in the table below:




Can be Viewed

Object Explorer This is used for browsing servers, creating and locating objects. It is also used for managing data sources and viewing logs. This tool can be accessed from the View menu.
Solution Explorer This tool is used for storing and organizing scripts and connection information. SQL Server Scripts can be stored as Solutions and use the source control for managing scripts as their evolvement takes place. This tool can be accessed from the View menu.
Template Explorer This is used for creating queries that are based on the templates that exist. Custom queries can also be created and alter the templates that are existing. This tool can be accessed from the View menu.
Dynamic Help This is used for displaying a list of Help topics that are related. For viewing a particular topic, one needs to click on the name of the specific component.

The tools in the SQL Server Management Studio work with each other. To explain it with an example, a server can be registered with the Object Explorer and for opening a SQL Editor window that is connected to a specific database.

We provide SQL Server 2012 Tutorial videos including topics on Administration, Development, Programming and Business Intelligence areas. Feel free to check sample training at this page: http://sqlserver2012tutorial.com/free-video-training/





Follow us on: