Top 3 features in SQL Server 2008 R2

SQL Server 2008 R2 has quite a few new features even though it was an intermediate release. Here are the top 3 features in our opinion.

Master Data Services

With Master Data Services (MDS) in SQL Server 2008 R2, you have a single source of corporate data. Large organizations face the problem of multiple data sources where the same data is defined and used in various locations. SQL Server 2008 R2’s new MDS feature provides a central portal or hub from which administrators can create and update attributes and hierarchies, with the goal of ensuring data consistency across the enterprise.

You also have the ability to do version control of data for auditing or corporate restructuring purposes.  This means that every change made to data and can be reversed back to its original form if needed be. With Master Data Services in SQL Server 2008, you can deliver faster and accurate results across the enterprise. You can direct stewardship to ensure data quality. Enforce business rules and manage supporting domains in a central stewardship portal to create, edit, and update master data, including members and hierarchies. More information on MDS is available here:
http://msdn.microsoft.com/en-us/library/ee633763.aspx

PowerPivot with Excel

PowerPivot for Office 2010 allows you to work with large amounts of data in Excel by interacting directly with a SQL Server 2008 database. With PowerPivot your Excel workbook (.xslx) files can contain large, related datasets that you can use with PivotTables and PivotCharts in a worksheet. The window is provided through the PowerPivot for Excel add-in that you download from the web. Here is the Add-in link for PowerPivot that you can use with SQL Server 2008.

http://office.microsoft.com/en-us/excel-help/powerpivot-add-in-HA101811050.aspx

You use PowerPivot to import large or small amounts of data from a wide range of external data sources, including relational or multidimensional databases, public data feeds, SQL Server 2008 Reporting Services reports, or Office documents. After you import the data, you can build relationships between different data so that you can work with the data as a whole. In addition to the separate work area, the add-in also provides a local data processor that performs rapid calculations on large amounts of compressed data. Datasets that you import into the PowerPivot window can exceed the one million row limit in Excel worksheets.

Related to this you also have PowerPivot for SharePoint. With this configuration, you can provide the capabilities of PowerPivot workbooks with SQL Server 2008 and then deploy this to Microsoft SharePoint. As such you will be able to share the data and finding across the whole enterprise.

Report Builder 3.0

Report Builder 3.0 is a report authoring tool in SQL Server 2008 that makes creating reports easier just like Microsoft Office environment. It has features like sparklines, databars and visual data indicators. With SQL Server 2008 Report Builder 3.0, you can accelerate report creation, collaboration, and consistency by enabling users to create reusable reports. Shared components make it quick and easy to assemble comprehensive business reports in a professional-looking format. On the best feature is the Report Part feature where you can take an existing report and designate report items and data regions to save and reuse in other reports.  This can amount to a huge time savings for developing new reports based on an existing report.  Other customers say that they like the improved SharePoint integration and the performance improvements in SharePoint.  Further information on Report Builder 3.0 in SQL Server 2008 can be found here:

http://technet.microsoft.com/en-us/library/dd220460%28v=sql.105%29.aspx

For Hands-on SQL Server 2008 Tutorials and advance TSQL scripts, visit this site:
http://sqlserver2008tutorial.com/

 

What is Microsoft SQL Server?

SQL Server is a Database Management System (RDBMS) from Microsoft. It is also known as MS SQL. SQL Server is an Enterprise level database application just like Oracle, DB2 or MySQL. There are several different versions of MS SQL, the most common ones are SQL Server 2008 and SQL Server 2012. If you want to try a free SQL Server version, please visit this Microsoft site.

http://msdn.microsoft.com/en-us/sqlserver/bb671149.aspx

Historically speaking, SQL Server was developed as collaboration between Microsoft and Sybase. SQL Server version 4.2 was available in 1993. The first version of MS SQL developed completely by Microsoft was SQL 6.0 and then SQL 6.5. In 1998, Microsoft came up with SQL Server 7 followed by SQL 2000. Microsoft pretty much built the next version MS SQL 2005 from the ground up. Next version of Microsoft SQL Server 2008 was available for production in 2008. The latest product SQL Server 2012 link is on the market now.

SQL Server is a massive product with lots of moving parts and pieces. In is post, we are going to look at a handful of the important ones in the order of their importance. If you are interested in learning MS SQL, please visit our partner sites at SQL Server 2008 Tutorial, SQL Azure Tutorial and SQL Server 2012 Tutorial.

What is SQL Server Management Studio (SSMS)?

SQL Server Management Studio is by far the most important tool in SQL Server. It is used for administration in addition to development of objects like MS SQL databases, tables, stored procedures, logins and SQL jobs. In order to launch SSMS you can follow this path:

Programs > Microsoft SQL Server 2008 > SQL Server Management Studio

Here is a screen shot of What SQL Server Management Studio looks like on our computer, by the way we are using MS SQL 2008 R2 version:

sql-server-management-studio1

If you are a visual person like me, you can watch FREE SQL Server tutorial along with sample videos on SQL Server at this location.
http://www.learningsqlserver2008.com/faq.htm

Important components within SSMS are:

Databases: Using this icon/folder, you can create objects like MS SQL databases, tables, views, stored procedures, functions, triggers and constraints. You will be spending most of your time in MS SQL right here, guaranteed!

Security: Within this folder, you have the ability to create new logins (for access to MSSQL Server). You can also create server level roles (similar to Windows groups), and setup security audits.

Server Objects: Here you can create backup devices, endpoints (point of entry to a SQL Server), Linked Servers (non SQL providers that can interact with MS SQL).

Replication: This is the ability of SQL to synchronize data between a primary and a secondary server(s). With replication, you can configure items like publishers, subscribers, publications, articles, etc.

Management: You can perform SQL Server management tasks like data-tier applications, policy management, setup maintenance plans; look at SQL Server logs, setup Database mail and configure Full Text Search.

SQL Server Agent: Vital component in SQL 2008 component for database administration. You can manage SQL instance, create jobs, alerts, operators, proxy accounts and also review SQL Server agent logs.

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

what-is-MSSQL-Agent2

SSQL Server Configuration Manager (SSCM)

This is another vital management tool for SQL Server. You can control SQL Server services, Server protocols; limit Client level protocols, client aliases and other surface area configuration pieces. You can get o SSCM by doing the following:

Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configure Manager

I have included a screen shot of SSCM from our MSSQL server:

sql-server-configuration-manager3

Here are the available options under SSCM:

SQL Server Services: With this, you can control items like MSSQL Server service (database engine), SQL Agent service, SQL Server Integrations service (SSIS) and SQL Server Reporting service (SSRS).

SQL Server Network Configuration: Here you can configure protocols (network communication standards) for connection to Microsoft SQL including TCP/IP, Named Pipes and Shared Memory

SQL Native Client Configuration: Just like on the server side, you can configure protocols at the client side. In addition you can setup alias to connect to the SQL Server.

 

Import and Export data:

Using the SQL Server import and export wizard (SSIEW), you can move data to and from SQL Server. Microsoft SQL supports a number of different providers like Oracle, IBM DB2, flat file (.txt), Office applications, OleDB and ODBC compliant drivers. SSIEW walks you through all the necessary steps and at the end will create an Integration Services (SSIS) package. We will cover Integration Services under Business Intelligence Development Studio.

What are SQL Server Performance tools?

It’s just a fact of life, sooner or later your SQL Server database will grow and grow and run into performance issues. It may be a combination of higher volume of data, poor database design or inefficient Transact SQL queries. In order to solve the problem, SQL Server provides you the following two tools right out of the box:

SQL Profiler:

SQL Profiler can capture every single SQL Command, stored procedure and database process that you are running on SQL Server. In a sense, you create a trace which stores a chronological list of every event on SQL Server. You can then open the trace and review it later for performance tuning and query analysis.

Database engine tuning advisor (DETA)

This next performance tuning tool is also an excellent utility that can help you find not only missing indexes but show you how to improve existing indexes in SQL Server. First you have to either create a SQL Profiler trace or a TSQL script, next you need to be loaded it up into database engine tuning advisor which will then make its recommendations.

What is SQL Server Business Intelligence Development Studio (BIDS)?

When you install SQL Server on your box, you will also be given Business Intelligence Development Studio. This is a cut down version of Visual Studio and allows you to create projects on Integration Services (SSIS), Reporting services (SSRS) and Analysis Services (SSAS). You can launch BIDS by doing this:

Programs > Microsoft SQL Server 2008 > SQL Server Business Intelligence Development Studio

Like we mentioned in the Import and Export data section, SQL Server Integration Services is an ETL (Extract, Transform and Load) Tool that can move data back and for between MSSQL and other data providers. Our trainers cover a basic way to create a SQL Server at Integration package in this sample training video.
http://www.youtube.com/watch?v=Jl0Vze2_6_o

Here is what a SSIS package looks like in BIDS. We are loading a flat file (.txt) into SQL Server.

what-is-ssis-integration-services5

SQL Server Reporting Service (SSRS) is a full blown reporting solution from Microsoft. SSRS uses RDL (Report Definition Language) and XML to create and manage reports. You can use BIDS to create a report. You can deploy it to the Reporting Server which will process reports and handle end-user transaction. It has the ability to allow users to not only pull SQL reports but also push reports on demand at a fixed schedule.

Here is video on SSRS from YouTube:

http://www.youtube.com/watch?v=xhXaxV2-iUQ

This is what an SSRS report looks like in BIDS.

how-to-create-sql-server-reporting-service6

SQL Server Analysis Services (SSAS) is part of the business Intelligence set of tools from Microsoft. SSAS will let you perform Online Analytical Processing (OLAP) which lets you create multi-dimensional cubes. SSAS also enables its users to do Data Mining which lets you analyze massive data by different attributes. This will uncover patterns and useful information that may not be readily available from an online transactional Processing (OLTP) system.

Miscellaneous topics on MS SQL:

What else is there in MSSQL? you ask. Well a lot! SQL Server has many more components and features that we have not even mentioned. Here are a few important ones:

SQL Server Failover Clustering: This provides High Availability support for the entire SQL Server instance. First you create Windows Server Failover cluster and then add one or more nodes to it.

Database Mirroring: This is a High availability solution in SQL Server at the database level. In case of a database failure, the database is switched from the Principal (active) to the Mirror Server. In this manner, there is minimal interruption from the data consumer point of view. You can take a look at this Database Mirroring video on YouTube.

SQL Replication: This is the ability to synchronize data between different MSSQL databases. This is a popular option for mobile Sales professionals who are on the road and can sync with the master database on as needed basis. You can setup on of these types: Snapshot, Merge and Transactional replication.

Transact SQL (TSQL): This is the flavor of Structured Query Language (SQL) for Microsoft. You can manipulate your data using commands like SELECT * FROM TABLE. Our partner has more than 60 TSQL scripts to help you be a better SQL DBA or SQL Developer!

Sqlcmd Utility: Sqlcmd is a command line utility in SQL Server. You can use it to execute TSQL statements, stored procedures and other script files from the command prompt. This is useful when you use batch files or run automated processes like setting up a new server.

Services: Using Windows Services manager, you have ability to Start and Stop MS SQL Services. You can get to this by either typing in services.msc in the Start menu or by following this path

Control Panel > Administrative Tools > Services

SQL Server Related Links:

-SQL Server Home Page
-SQL Server 2012 Training
-SQL Server Central – Excellent resource on MSSQL topis
-How to implement Security in MS SQL 2008?