Monthly Archives: February 2013

Business Benefits of SQL 2012 Data Tools


Formerly code-named Juneau, SQL 2012 Data Tools (SSDT) is a tool set designed for use by Microsoft with SQL Server and SQL Azure to facilitate database design. Features include business intelligence (BI) tools, rapid data discovery, and data warehousing, among others. From a business perspective, SSDT combines features from several other programs into one integrated development environment, eliminating the need to manage several different applications to accomplish the same objective.

SSDT’s design allows it to perform large-scale data mining operations in conjunction with BI platforms like PowerView, PowerPivot, Excel, and SharePoint. It should be noted, however, that SSDT performance peaks out at 500 terabytes (TB), so companies with exceedingly large data sets will require additional platform support. SSDT also integrates cloud-based data in Windows Azure Marketplace Datamarket or via third-party providers.

As stated above, many of SSDT’s core features descend from features that were already available, albeit from a host of other programs. Former SQL Server incarnations required Visual Studio to create SQLCLR objects, but SSDT sidesteps the need for the additional software by permitting the user to build, run, and test SQLCLR objects. SSDT also abandons the need to run Business Intelligence Development Studio as it already contains BI development applications like Analysis Services, Reporting Services, and Integration Services.

Most large projects require database version control to avoid management headaches, so SSDT uses Schema Compare to create at-a-glance differences between multiple database versions. For offline development, SSDT can provide a template for easy SQL Server database construction and modification. Users can change this template as they see fit, and Schema Compare will readily make all differences transparent to the database developer. Once completed, users can copy the database schema directly into a project environment. Furthermore, SSDT can minimize micromanagement by automatically devising the scripts necessary to generate new database versions. The schema tool will also help users find differences between multiple database versions by using Transact-SQL (T-SQL) scripts to synchronize the discrepancies.

T-SQL functions as the standardized computer language responsible for all procedural programming, data processing, and mathematics within Microsoft SQL Server. SSDT incorporates T-SQL IntelliSense to provide rapid keyboard input. SSDT itself allows developers to test T-SQL code, allowing users to single-step through the code, set breakpoints, and debug stored procedures.

SSDT’s Table Designer gives users an easy way to create and alter tables and related objects, and it can modify tables on SQL Server instances as well as tables within an enclosed database project. The Table Designer also contains a script pane to permit users to directly edit the “create table” script, and users will also find it simple to add keys, triggers, and indexes to their tables, once created.

SSDT also features full retro-compatibility with SQL Server 2005 and later.

Although many of SSDT’s most alluring features are not entirely new to developers, never before have they been so readily available through one platform. In theory, this will minimize the need to juggle programs, windows, and divergent interfaces, thereby sharpening the efficiency of database design and maintenance.


Source link

Advantages and Disadvantages to Using MySQL Vs MS SQL


Two of the most popular database systems used by web developers today are MySQL and Microsoft’s MS SQL server. Both are similar in regards to being storage and retrieval systems. The two systems support primary keys, along with key indices which allow you to speed up queries and constrain input. Furthermore, both systems offer some form of support for XML.

Apart from price, which is the obvious difference, what distinguishes these two systems, and which one is right for you? We’ll overview both products, point out the major differences and explain the advantages and disadvantages of using them for your business.

Open-source vs. Proprietary

When it comes to these two databases, the differences begin with the open-source nature of MySQL vs. the closed, proprietary structure of the SQL Server. MySQL is an extensible, open storage database engine, offering multiple variations such as Berkeley DB, InnoDB, Heap and MyISAM. On the other hand, with the Microsoft product, you are limited to a Sybase-derived engine through both the good and bad times.

When considering how MySQL integrates seamlessly with a number of programming languages and other web-based technologies, it certainly has the advantage over MS SQL in the way of compatibility, as the SQL Server is known to work better with other Microsoft products.


Contrary to popular belief, the MySQL system isn’t always free. On the other hand, it is always more affordable. In regard to both products, licensing fees are based on a two-tiered scheme. With MS SQL, the best way to obtain a developer’s license is to buy a license for the Microsoft Developer or Microsoft Visual Studio suite. Both provide you with a free SQL Server license for development use. If you want to use the product in a commercial environment, you need to at least purchase the SQL Server Standard Edition – which could set you back over $1,000 for a few client connections.

Because MySQL is an open-source system under the GNU General Public License, developers can use it at no cost as long as the associated projects are also open-source. However, if you intend to sell your software as a proprietary product, you would need to purchase a commercial license, which costs about $400 for up to nine clients. Depending on the project and your funds, MySQL may have the advantage here.

Technical Differences

The open-source vs. proprietary battle alone is a leading cause why some users choose one system over the other. However, there are a few differences from a technical aspect as well.

For instance, MySQL doesn’t offer full support for foreign keys, meaning it doesn’t have all the relational features of MS SQL, which is considered a complete relational database. Some versions of MySQL also lack full support for stored procedures – the biggest disadvantage being the MyISAM system, which doesn’t support transactions.


In the way of performance, MySQL is the clear leader, mainly due to the format of its default table, MyISAM. MyISAM databases leave a small footprint using little disk space, memory and CPU. While the system runs on the Windows platform without flaw, it tends to perform better on Linux and other UNIX-like systems. Because of its stability, many internet powerhouses such as Yahoo! use MySQL as their back-end database.

When it comes to performance, MS SQL’s strength of being packed with more features than other systems is perhaps its biggest disadvantage. Although most of these features are designed for performance tuning, they tend to sacrifice other essential elements. The cost here is complexity and the hogging of resources in the way of storage and memory, which leads to poorer performance. If you lack the knowledge and sufficient hardware to support an SQL server, you would be better off with another database management system.


These two database systems are pretty much deadlocked in regards to security. Both come with adequate security mechanisms by default, bearing you follow the directions and keep the software updated with security patches. Both operate over known IP ports which unfortunately attracts a wealth of intruders, a downside that can be attributed to both products. The good thing is that MySQL and MS SQL allow you to change ports just in case the default becomes too vulnerable.


As far as recovery goes, the SQL Server has a definite advantage over MySQL, which tends to fall a little short with its MyISAM configuration. A UPS system is mandatory with MyISAM as it assumes uninterrupted operation. If a power outage should occur, it could result in the corruption and loss of critical data. With the SQL Server, data corruption is more unlikely. The data travels through various checkpoints while passing from your keyboard to the hard disk and through the monitor. Additionally, the SQL Server keeps track of the process, even if the system unexpectedly shuts down.

The Best Choice

As you can see, both systems have their advantages and disadvantages. From our perspective, any product that allows you to be efficient is a good database; anything other than that isn’t worthy of your time and frustration. When it comes to MySQL and MS SQL, the decision all boils down to the situation and most importantly, what you’re looking to accomplish.


Source link

How Will SQL 2012 Affect Oracle’s Market Share?


Microsoft aims to use SQL Server 2012 to tear off a slab of Oracle’s dominating market share on database management, but will they succeed? Their success or failure hinges on two factors: further refinement of SQL 2012’s integration with other Microsoft business intelligence (BI) products, and successful integration of Hadoop to address SQL Server’s problems with Big Data management.

Microsoft knows its strength-SQL 2012 “plays nice” with its host of BI programs such as SharePoint, Excel, PowerPivot, Analysis Services, Master Data Services, and others. Many businesses already have existing Microsoft BI infrastructure within their walls, thus creating familiarity, reducing the learning curve, and reducing the cost of additional licensing. Oracle does in fact sell a BI suite by the name of Oracle Business Intelligence Enterprise Edition 11g, and though it offers functionality comparable to Microsoft’s BI suite, some users perceive Oracle as more expensive and less pervasive.

In addition, Microsoft plans to fully integrate the Big Data management system Hadoop Distributed File System (HDFS) into its latest SQL iteration by mid-2012. Once applied, HDFS will nullify Oracle’s most significant advantage over SQL 2012: the ability to store, examine, and analyze daunting amounts of discordant data. Used by companies like EBay, Facebook, and Google, HDFS can tackle datasets on a petabyte scale. If SQL 2012 delivers Big Data to prospective companies, many of which already rely on Microsoft BI software, then those companies are presented with one more reason to dismiss Oracle.

Even with these considerations, it seems unlikely that Oracle will immediately flounder. SQL 2012 runs only on PCs running on the latest operating systems; Oracle will also run on Macs, Linux, Unix, and a few others. As it appears unlikely Microsoft will invest into the Mac, Oracle will likely remain significant on the Mac for some time.

Several other factors count against Oracle’s future market share. SQL Server is generally viewed as less time-consuming to install, with general estimates resting at 1.5 hours for SQL and about six hours for Oracle. SQL Server 2012 includes critical security features in its work group, standard, and enterprise editions; Oracle offers the premium security measures only in its enterprise edition.

With continued emphasis on reduced micromanagement, existing infrastructure, ease of use, Hadoop integration, and monolithic support, it seems logical to predict Microsoft will significantly reduce Oracle’s share of the ERP market in the approaching months. SQL 2012’s most significant obstacle may be its unavailability on dominant non-Windows operating systems. Like any market shift, the effect will be gradual, and Oracle’s longtime dominance in the database market will keep many Oracle users skeptical of Microsoft’s attempts to undermine their faith.


Source link