Microsoft Training Blog

New Certifications Affecting SQL Server Certifications?

Fri, 25 May 2012 17:28:00 GMT

You may have heard that Microsoft is getting rid of the MCTS and MCITP certifications, and returning to the MCSA and MCSE. Actually, that’s a deceptive statement - the new certifications are the Microsoft Certified Solutions Associate and the Microsoft Certified Solutions Expert (not Engineer).

If you are aiming for the MCSE - Business Intelligence, your first target is the MCSA SQL Server 2012. This is very different from the MCITP BI path, which had just the MCTS - BI exam, and then the MCITP - BI exam. Now, if you don’t already have a certification you will need to take three exams:

Exam 70-461 - Querying SQL Server 2012
Exam 70-462 - Administering a SQL Server 2012 Database
Exam 70-463 - Implementing a SQL Server 2012 Database

To get ready for the 70-461 you should take the MS-10774 course - Querying SQL Server 2012 and you can also review your knowledge with the MS-Press exam Training Kit written by Itzik Ben-Gan, who also wrote the course. The best place to start is Microsoft Exam page on it’s requirements, and resources. Pay particular attention to the Skills Measured section.

To get ready for the 70-462 exam, you should take the MS-10775 course - Administering a Microsoft SQL Server 2012 Database. This exam maps directly to the MS-10775 course.

And since Microsoft decided to map the exams to classes the third exam maps directly to the MS-10776 course: Developing a Microsoft SQL Server 2012 Database. Neither the 70-462 nor the 463 have study materials out yet, other than the courses, although Amazon does show both the Training kit for the 70-462, and the Training kit for the 70-463.

The exams are due out in June 2012, and you should see more study guides and prep materials show up over the summer.

But, you say that you already have the MCTS for SQL Server 2008. What about an upgrade path? For you, regardless of which MCTS SQL Server you have, you can just take two exams to upgrade to the MCSA - Business Intelligence. Those upgrade exams won’t be available until August 2012, and are 70-457 and 70-458. By now, you realize that you start by finding Microsoft’s page on the exam to see the requirements. The courses covered are the ones listed above- the MS-10774, MS-10775, and MS-10776. These two exams are being called Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 -Part 1and Part 2.

If you have the MCITP - Business Intelligence, you have to achieve the MCSA certification, and then pass the 70-460 - Transition Your MCITP: Business Intelligence Developer 2008 to MCSE: Business Intelligence. The courses focusing on Business Intelligence are the MS-10777 and the MS-10778.

If you do not have the MCITP- BI for SQL Server 2008, then after achieving the MCSA, you will need to pass the 70-466 - Implementing Data Models and Reports with Microsoft SQL Server 2012, and the 70-467 - Designing Business Intelligence Solutions with Microsoft SQL Server 2012. The 70-466 exam maps to the MS-10778 course, while the 70-467 exam does not currently have a course which can be taken.

There is also a new SQL Server Data Platform MCSE.

The interesting thing about the new certifications is that Microsoft started by defining the certification requirements, and then designed the courses to support them. That doesn’t mean that you learn everything you need to know to pass the exam in that course. It just means that the topics on the exam are mostly covered in the course.

Microsoft has stated that it wants to make the certifications more valuable. To that end, they have stated that they are making the exams harder, and testing beyond book learning to ensure that those passing the exam have hands on experience, and years of usage. The courses are out, and the exams will be out by the end of summer whether you are upgrading or not. Good luck with your preparations!

Thank you!

Saskia Schott, Systems Engineer, QuickStart Intelligence

SharePoint Designer Governance

Wed, 16 May 2012 23:09:00 GMT

I hear these and many similar comments and questions all the time in my SharePoint Site Administrator/Power User classes:

“Oh, I can’t do that because my company won’t let us use SharePoint Designer.”
“I’d like to create a workflow, but no one is allowed to use SharePoint Designer.”
“How do I get my company to let me use SharePoint Designer?”

These questions and comments all relate to SharePoint governance. SharePoint governance is a very important consideration for any SharePoint deployment, one that from my experience a lot of organizations cut corners (or don't even realize what they should be doing), to the detriment of their SharePoint deployment’s success. SharePoint governance is a big subject; more than one blog post can cover. Of all the subjects and classes we teach here at QuickStart (from Citrix, Cisco, VMWare, to Microsoft SQL Server, Windows Server, Exchange, etc.), there is only one product we have a dedicated class for: SharePoint. I present this as evidence for the need for SharePoint governance, without which has led some people to call SharePoint a “virus”. (Go Google or Bing “SharePoint” and “Virus” if you don’t believe me).

In this blog post, I’m going to discuss a specific SharePoint governance issue, that of SharePoint Designer. What is SharePoint Designer, why do I care, who should have and use and for what, and what can and should be in a governance plan to make best use of SharePoint Designer will be the questions answered in this post.

What is SharePoint Designer?

SharePoint Designer is a tool that developed out of Microsoft FrontPage (back when it was still and active product) that Microsoft eventually made free to anyone using SharePoint. Due to the way that SharePoint stores files in a SQL Server database, most web development tools cannot work with SharePoint content so a SharePoint aware design tool is necessary. Over time, Microsoft has added more and more functions into SharePoint Designer, so it is not just a web customization and design tool. SharePoint Designer 2010 allows Site administrators to, among other things:

Customize SharePoint pages, including applying cascading style sheets, add web part zones, remove or change formatting
Customize and apply custom master pages to change page layouts
Backup and restore sites (limited, should not be used as a true backup solution)
Add DataView web parts to SharePoint pages (used to display external data content)
Design SharePoint Workflows
Create sites, lists, libraries and change site structure
Configure security settings
Create External Content Types using Business Connectivity Services

Many of the new features added to SharePoint Designer increase the potential audience for the tool. It is a very useful tool that can make administration of sites (security, creating and managing lists and libraries) faster and easier.

Why do I care about SharePoint Designer governance?

SharePoint Designer is, however, a tool that can be dangerous if users do not know what they are doing. The experience of SharePoint 2007 and SharePoint Designer 2007 showed that users can make sites difficult to use, waste time changing colors and look and feel, create inconsistent navigation, or even completely breaking pages and sites so that they cannot even load. This resulted in a lot trouble for other users, IT, help desks, and contributed to resistance and negative opinions of SharePoint some users gained. Many organizations have since completely banned or restricted SharePoint Designer use to IT only. The risk to this approach is losing the benefits that SharePoint Designer can provide to their organization.

Who should use SharePoint Designer and what should they be using it for?

Users who administer sites (create list and libraries, customize web parts on pages, set site security) or business users who already work with data or set business process rules could be beneficial users of SharePoint Designer. The biggest risks with SharePoint Designer are those things related to customizing pages, not site administration, incorporating external data, creating and using lists and libraries. Not allowing users to do these things with SharePoint Designer risks having SharePoint be treated as a web-based file server and project tracker that can take a lot of work in the Web UI to make useful. By letter users build simple team or department workflows, display and incorporate data from external business systems, administer security and customize web parts quickly, they can build integrated solutions that users want to use, rather than SharePoint sites that are too “SharePointy”, as I’ve heard them described. SharePoint sites that are too restricted to just storing list items and documents.

How can I gain the benefits and yet governing SharePoint Designer usage to minimize risk?

The discussion invariably leads to how to govern SharePoint Designer but still reap the benefits of using it. Some organizations create teams of SharePoint Designer experts who handle requests from users for changes and features. Some deploy SharePoint Designers to their site designers and site administrators in the company. Banning SharePoint Designer is easy and safe and some administrators have been burned for allowing it.

Fortunately, Microsoft saw the problems (and likely experienced some of them first hand in their own deployments), and provided some governance tools for us: At the site collection or web application levels, administrators can restrict what is allowed with SharePoint Designer. You can prevent users from customizing pages from the site definition (but still allow web part customization), prevent master pages customization, and prevent users from changing site structure (but still allow creation, modification, and deletion of lists and libraries). This allows an organization to deploy SharePoint Designer to approved users, but limit those users to the most useful and less dangerous capabilities (Administration of security and managing list and libraries, creating workflows, BCS External Content Types, advanced formatting of lists, etc).

No governance can work with just computer based tools. Governance starts and ends with good, logical, and well-designed governance policies in place and communicated clearly to those people they govern. A good governance policy for SharePoint Designer is one that identifies who can benefit from SharePoint Designer, establishes rules and guidelines for using it, and incorporates a training aspect to the policies. You wouldn’t let an employee drive a corporate vehicle without some proof they can operate it successfully, so don’t give users SharePoint Designer without some procedure to validate they know your policies and the software. I’ve seen organization build a computer based training program with videos and examples, then use SharePoint surveys to quiz the users, with a final test of sample site with tasks for the users to perform. An approver then reviews the site and approves (possibly through a SharePoint workflow) the user to have SharePoint Designer installed for them. Third party training certifications have also been used in the past to meet this requirement. Don’t forget that governance policies will vary from site to site. A site like an employee HR site used by everyone in the company, you may not let anyone outside IT use SharePoint Designer with it, but a project team site it may be highly appropriate for a project manager to use SharePoint Designer.

If you have need of more information about SharePoint Governance or SharePoint Designer, or want to train your users to use SharePoint Designer, QuickStart had extensive 2 day classes for both of the subjects.

Thank you!

Steven Randell Allen, Systems Engineer, QuickStart Intelligence

Auditing Tools in SQL Server 2008 and SQL Server 2008 R2

Fri, 23 Mar 2012 22:07:00 GMT

This is the third article in a series discussing database auditing. The first article discussed the need to audit from various governmental mandates and best practices of auditing just the data you need to meet requirements. The second article discussed the auditing log data best practices, both what event data to include in your audit logs and the need to protect your audit log systems and data from tampering. This article will look at the various tools that we can use for auditing and the pros and cons of various tools that SQL Server 2008 provides to us administrators and developers to enable auditing compliance.

SQL Server contains many tools that can be used for various types and levels of auditing:

Auditing Feature	Version Introduced	Advantages	Disadvantages
DML Triggers	<= SQL 2000	Available in all editions and versions of SQL Server Allow for fine-grained auditing Can use SQL Server Reporting Services to easily report High overhead, auditing is part of transaction Implementation cost is high, need to implement separate trigger for each table Can only be used for INSERTs, UPDATEs, DELETEs Low security: can be disabled by db_owner to bypass
SQL Server Profiler	<= SQL 2000	Available in Standard Edition Allow for fine-grained auditing Can audit all types of events Low-medium performance overhead with server processed events (recommended for auditing) Need to run a tool outside of SQL Server Risk of database activity without auditing running Difficulty reporting depending on audit log location (SQL table vs. trace file)
SQL Server Server-Side Tracing	<= SQL 2000	Available in Standard Edition Allow for fine-grained auditing Can audit all types of events Much more difficult to disable than DML triggers or SQL Server Profiler and disabling can be auditing Low-medium performance overhead Implementation cost is high to setup, configure, and make sure it is running all the time Difficulty reporting depending on audit log location (SQL table vs. trace file)
C2 Audit Mode	SQL 2000	Easy to configure Meets federal C2-level security audit specification Lots of detail in the audit log If auditing cannot occur, will stop server Can log reads as well as changes All or nothing, logs every activity on the server Can easily become too much data Extra work required to report from data Many extend stored procedures do not work with C2 auditing enabled Includes more than just user access data
DDL Triggers	SQL 2005	Can audit for schema changes Fairly easy to implement, especially with event groups Disabling is audited	High overhead, auditing is part of transaction Can only used for DDL statements Can be disabled without to much trouble
Event Notifications	SQL 2005 (SP1 for a security fix)	Allows for fine-grained auditing Low overhead, auditing is asynchronous, but: Highly reliable, using the SQL Server Service Broker Most secure auditing mechanism possible if configured correctly and disabling is logged Supports all editions of SQL 2005 and SQL 2008/R2, only requiring one Standard or Enterprise edition for audit log server. Fully customizable reporting possible using SSRS Can be difficult to setup, especially in the more secure scenarios
SQL Server Audit	SQL 2008	Easy to configure Very secure Choice of log locations (file – easily queried and/or placed in SQL table, or either Windows Application log or the Security Log Can integrate almost seamlessly with other auditing measurement and reporting systems that collect from the Windows Security Log Low overhead Possibly not as secure as some Event Notification setups Requires SQL Server 2008/R2 Enterprise edition (or greater). Won’t work with Standard or less or with SQL Server 2005.

I hope you found this article looking at the available auditing tools in SQL Server 2008 interesting and useful. In the next articles in this series, we will look at the two most powerful, secure, and flexible auditing methods in SQL Server 2008, SQL Server Audit and Event Notifications. Following those we will look any changes to auditing that SQL 2012 provides us.

Has this helped you and what do you think?

Steven Allen, Systems Engineer, QuickStart Intelligence

SQL: Auditing Event Data While Protecting Auditing Systems and Data

Thu, 22 Dec 2011 23:54:00 GMT

This is the second article in a series discussing database auditing. The first article discussed the need to audit from various governmental mandates and best practices of auditing just the data you need to meet requirements- refresh your memory on SQL Server Database Auditing. This article will continue discussing auditing best practices, including what data needs to exist in your audit logs and the need to protect your audit log data and auditing systems from being disabled or removed.

Auditing Event Data

First of all I need to say that auditing compliance is more than just having systems in place to track what users are doing with data. It includes many other aspects besides just the monitoring systems. All the security mechanisms are important but even with the best, state-of-the-art auditing system, if someone can walk in and walk out with your server or backups, you will be out of compliance with several of the regulations. I could go on and talk about all the other security systems that need to be in place, but suffice it to say auditing of data access is just one part of auditing compliance and let us agree that we will all implement those systems.

Returning to auditing (aka monitoring systems), a key part of any such system is a measuring and reporting mechanism so that the auditing data can efficiently be used. While those are mainly outside the scope of our discussion of generating the auditing data, I will mention the ease (or difficulty!) of implementing those systems when we look at the various tools SQL Server 2008/R2 provides.

Any good auditing system will include, at the very least, the following information:

Who accessed the data?
What type of access was it?
New data or change, delete, or read of data – could include systems change like permissions change or new users/logins
What application accessed the data?
Where was the access from? (What network and/or what computer?)
What was the statement or command that accessed the data?
Was it successful and what data accessed (if successful)?

Protecting Audit Event Data and Auditing Systems

Protecting your auditing data systems and audit data is a very important aspect of a compliant auditing solution. You might otherwise have a very good auditing system, but if it can be disabled or removed easily, then in total, the system is weak. Various techniques are used by system hackers (both external or internal) to cover their tracks. If they can disable auditing systems in some way, then they can then do what they want without auditing occurring.

Methods include:

Straight out disabling, and if they can do so without us knowing so much the better,
Filling up the auditing log location
Disabling certain network traffic to prevent sending auditing records to remote servers
Clearing the log at the end
Generating large amounts of data to “push” their records out of rolling window auditing solutions.

A good audit system will try to account for these types of attacks by:

Providing ample storage for auditing events
Having in place notifications for unusually high volumes of activity
Using tools and methods that are difficult to disable
Shutting down the server, if unable to log to the event location. (When government regulations are involved, denial-of-service can be better than insufficient logging).

As a last resort, your auditing solution should include logging of disable events or clearing of event logs.

In our next article we will look at various tools that SQL Server 2008/R2 provides to us for auditing, including what methods are more secure with the above listed best practices in mind.

SQL Server training at QuickStart is available!

Thanks!

Steven Allen, QuickStart Intelligence, Systems Engineer

QuickStart adds HTML5, Opalis, Denali and more!

Thu, 10 Nov 2011 22:02:00 GMT

QuickStart Intelligence just added 5 new classes to the schedule.

50507 - Designing IT Process Automation with Opalis Integration Server is 3 days and this course is intended for Enterprise Systems Engineers, Administrators and System Integrators who need to implement a Dynamic Datacenter using Opalis Integration Server.

Next, there are 2 new HTML classes. The first one, HTML5/JavaScript, is 2 days and the class is explained: HTML5 already figures prominently in developing interactive web sites for the iPhone, iPad, and Android mobile devices. Your instructor brings years of web development experience to the classroom, as well as a thorough understanding of the evolving technologies which comprise HTML5, so that you will be able to apply your new knowledge faster.

The second one is also 2 days and is called, HTML5/CSS3. HTML and CSS are the two languages that shape most websites on the internet. HTML dictates the content of a website (text, images, and media plugins) and CSS defines the style (colors, fonts, and layouts). Although HTML4 and CSS2 have been foundational in the development of a functional and stylistic internet, HTML5 and CSS3 represent the next step in web technologies.

The last two classes are SQL Server.

MS-10777 - 70-463: Implementing a Data Warehouse with Microsoft® SQL Server® Code Name “Denali” is actually free. The class is 5-days and an instructor-led course which describes how to implement a BI platform to support information worker analytics. Students will learn how to create a data warehouse with SQL Server “Denali”, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services.

MS-10778 - 70-466: Implementing Data Models and Reports with Microsoft® SQL Server® Code Name “Denali” is a free 5-day instructor-led course that describes how to empower information workers through self-service analytics and reporting. Students will learn how to implement PowerPivot and tabular data models, create and deliver rich data visualizations with Project Crescent and SQL Server Reporting Services, and discover business insights by using data mining.

Learn more information about the free SQL Server classes.

***Due to an overwhelming response, the two beta courses above are full and require pre-requisite skills. Please check back in March, QuickStart will be offering 5 Day SQL Server 2012 courses.

Please feel free to comment below if there are any questions or email at info@quickstart.com

Thank you!

QuickStart Intelligence

SQL Server Database Auditing

Fri, 23 Sep 2011 22:44:00 GMT

Auditing Event Data While Protecting Auditing Systems and Data

This is the second article in a series discussing database auditing training. The first article discussed the need to audit from various governmental mandates and best practices of auditing just the data you need to meet requirements. This article will continue discussing SQL Server 2008 auditing best practices, including what data needs to exist in your audit logs and the need to protect your audit log data and auditing systems from being disabled or removed.

Auditing Event Data

First of all I need to say that auditing compliance is more than just having systems in place to track what users are doing with data. In includes many other aspects that just the monitoring systems. All other security mechanisms are important. You can have the best, state-of-the-art auditing system, but if someone can walk in and walk out with your server or backups, you will be out of compliance with several of the regulations. I could go on and talk about all the other security systems that need to be in place, but let’s just suffice to say that auditing of data access is just one part of auditing compliance and agree that we’ll all implement those systems.

Returning to SQL Server 2008 auditing (aka monitoring systems), a key part of any such system is a measuring and reporting mechanism so that the auditing data can efficiently be used. While those are mainly outside the scope of our discussion of generating the auditing data, I will mention the ease (or difficulty!) of implementing those systems when we look at the various tools SQL Server 2008/R2 provides.

Any good auditing system will include, at the very least, the following information:

Who accessed the data?
What type of access was it?
- New data or change, delete, or read of data – could include systems change like permissions change or new users/logins
- What application accessed the data?
- Where was the access from? (what network and/or what computer)
- What was the statement or command that accessed the data?
- Was it successful and what was the data accessed (if successful)?

Protecting Audit Event Data and Auditing Systems

Protecting your SQL Server 2008 auditing data systems and audit data is a very important aspect of a compliant auditing solution. You might otherwise have a very good auditing system, but if it can be disabled or removed easily, then in total, the system is weak. Various techniques are used by system hackers (both external or internal) to cover their tracks. If they can disable auditing systems in some way, then they can then do what they want without auditing occurring. Methods range from straight out disabling, and if they can do so without us knowing so much the better, to filling up the auditing log location, disabling certain network traffic to prevent sending auditing records to remote servers, to clearing the log at the end or generating large amounts of data to “push” their records out of rolling window auditing solutions. A good audit system will try to account for these types of attacks by providing ample storage for auditing events, having in place notifications for unusually volumes of activity, using tools and methods that are difficult to disable, shutting down the server if unable to log to the event location (denial-of-service can be better than insufficient logging when government regulations are involved). As a last resort, your auditing solution should include logging of disable events or clearing of event logs.

I would love to know your thoughts on this. Please share your comments below.

Steven Allen, Systems Engineer, QuickStart Intelligence

Policy Based Management in SQL Server 2008

Wed, 07 Sep 2011 17:01:00 GMT

SQL Server 2008 has the ability to manage some aspects of your SQL Server via policies. There are several benefits to doing your management through Policy Based Management (PBM). First is the lessening of DBA burden to constantly check for standards adherence. Far too much of a DBA’s time is spent ensuring that objects are created based on the organizations agreed upon standards. Second, policies ensure consistency. Whether the issue is a naming convention, indexing, data file placement or options choices, it is important for the sake of efficiency that everyone can know what to expect. Third, policies give pervasive insight into server and database level objects and settings for the purposes of planning, troubleshooting and documentation.

Policy Based Management consists of four subjects.

Facets. These are the objects themselves (Tables, Databases, and Logins etc.) and what can be managed about the object. What can be managed will vary based on the type of object. e.g. Table Facets expose the ability to manage indexes associated with tables, columns in the table, etc.
Conditions. Conditions define a value against which the facet is checked, in other words the standard you desire. e.g. Tables must have indexes or Stored Procedure names must begin with usp_.
Policies. One or more Conditions make up a Policy. Policies define the Conditions to check and the group of objects against which to check them. e.g. All Tables in the Customers database, or specified Logins on the Production instance.
Evaluation Modes. There are four Evaluation Modes. These determine how to evaluate your Conditions against you extant environment. The four modes are:

On Demand. This allows you to create and view a report showing the condition of your measured objects in light of the Conditions you have defined. e.g. Which Tables have indexes and which do not. On Demand only evaluates the objects and display the results, it does not change anything.
On Schedule. Same as On Demand but rather than running manually as the result of SQL
On Change: Log. This mode evaluates the creation of new and/or the modification of extant objects. When a creation or modification event occurs that falls under the auspice of the Policy, the Policy is evaluated and the results are sent to a report.
On Change: Prevent. Like On Change: Log, the Policy is evaluated at the creation or modification of an object correlating to a Facet in the Policy. If the creation or modification will violate the Condition, the change is rejected by SQL Server and the transaction is rolled back.

Of course, this article does not explain how to implement Policy Based Management, but it introduces some of the capabilities of the technology. PBM should create savings due to decreased management costs. With extra money to spend from those savings and the increased free time your DBA will have, you can send them to a SQL Server 2008 class at QuickStart to learn how to implement PBM as well as many other improving technologies.

Interested in learning about SQL Server 2008 training and other QuickStart course? Please contact us today.

Please also share your SQL Server 2008 questions and comments below.

Thank you!

Jeff Rathjen, Systems Engineer, QuickStart Intelligence

Getting Started with PowerShell

Tue, 23 Aug 2011 16:44:00 GMT

At QuickStart and most companies, PowerShell is becoming more important to all Microsoft product administrators. Microsoft is making this the go-to command-line tool, and scripting tool for all its products. So, how do you get started? How hard is it to get started?

First of all, PowerShell v2.0 is installed by default on all Windows 7 and Server 2008 R2 machines. Version 1.0 is installed on Vista machines and Server 2008. You should download version 2.0 and install it, since there are a number of features you get with version 2.0 that provide core functionality not included in version 1: the ability to run jobs in the background, the ability to manage remote machines and run your PowerShell commands on the remote server or servers using sessions, and the new debugging features. PowerShell v2.0 also has added an Integrated Scripting Environment (ISE), the new Microsoft GUI for running commands, and creating scripts. This is not installed by default. On Windows 7, you add this as a Windows component. In Server 2008, you add this in Server Manager as an additional Feature. So, now that you’ve installed it, how do you get started?

It’s not hard at all. There are only a few things you need to know to be able to run commands at the command line. All commands (called ‘commandlets’ and written cmdlets) have a verb-noun structure. So, to find all the cmdlets, you type: get-command. If you want commands that use a specific verb, like ‘get’ , you use the “-verb” parameter to specify which verbs to display. Or, alternatively, you can use the “-noun” switch to find cmdlets with a specific noun. So, “ Get-command -verb get”, will retrieve all the cmdlets that use get as a verb. If you want to find all the cmdlets that you can use to manage aliases, you can say: “Get-command -noun *alias*” . Note that you can use wildcards, which is handy if you aren’t sure what the noun might be. This last command will then show you the following cmdlets:

Export-alias, get-alias, import-alias, new-alias, and set-alias.

So, now that you’ve found the cmdlet you want to use, what are your syntax options? For this, you can use the “get-help” cmdlet. “Get-help get-alias”. This will give you the basic help file, without detailed information on parameters, and without examples. The help file that is displayed suggests (at the bottom) that you can also use one of the following parameters: “-full”, “-detailed”, or “-examples”. I particularly like the examples parameter, since I may know what the command is, but not be able to remember the common usage.

Now, how can you manipulate the output? You have used “get-process” to see all the processes, but you don’t like the output. Before I introduce you to the formatting options, you need to understand that PowerShell cmdlets emit objects, that can be passed on to the next cmdlet through something called a pipeline. Each cmdlet can pass the objects on, after performing its task, whether that is sorting, or filtering, or getting related information. The symbol for the pipeline is the vertical bar: |. So, if you want to change the default output from get-process, you can pass it to a formatting cmdlet.

You can choose your output with the “format” verb. To do this, you send the output from “get-process” through the pipeline to another cmdlet that specifies the format. You already found format-list by using: “get-command -verb *format*. (If you didn’t guess that the verb was format, you could always just use get-command, which will display all cmdlets.)

You find that your choices are format-table, format-list, format-wide, and format-custom. So you specify: “get-process | format-table”. This is the same output you got with just “get-process”. But if you send get-process to format-list, you get a shorter list of properties. To get all the properties you can use: “get-process | format-list *”. (Be sure you leave a space between list and the asterisk. ) Note that format-list is now being told to show all the properties, by using the asterisk.

Interested in learning about Powershell training and other QuickStart course? Please visit our course catalog or contact us today.

Please also share your SQL Server 2008 questions and comments below.

Also, to continue reading the rest of this PowerShell blog series, check out the seccond one: PowerShell Output- Tips and Traps.

Thank you!

Saskia Schott, Systems Engineer, QuickStart Intelligence

Leveraging SQL Server 2008 R2 to Protect Important Documents

Fri, 05 Aug 2011 17:35:00 GMT

At QuickStart, I have spent a fair amount of time trying to purge Excel from our environment. The idea of users keeping mission critical data on an Excel spreadsheet on their local machine makes me cringe. However, with the popularity of Power Pivot and Excel services, more and more end users are demanding that they be allowed to use a product that is intuitive, familiar, and is already installed on every machine in the environment. I give up. If they really want to use Excel to do ad hoc analysis instead of more robust tools like Report Builder 3.0, then fine. So be it. It is important however to save it in a secure space, enter SQL Server 2008 R2.

The issue now, is how to expose the stored procedures that I have created for them on the database. I’m not really into letting them just write their own ad hoc queries, nor are they likely to have the skill set to do so. I needed an easy way to allow them to connect with SQL Server 2008 R2 and execute the appropriate stored procedures.

I assumed when I dug into the Data tab in Excel 2010 to get external data, that something would jump out at me and allow me to connect to a stored proc. This is not the case. Let me walk you through the process of connecting to a stored procedure in Excel 2010.

1) In the Excel Data tab, choose from other sources, from SQL Server 2008 R2.

2) Enter the servername and credentials needed.

3) Choose your database and table. I know, we don’t want to connect to a table, we want a stored procedure, but just choose any table and we will change it later to point to a stored proc.

4) Save your data connection files and click finish. Relax; we are not actually finished yet.

5) Click on the Properties button. Do NOT click on the OK button. We will go into the properties and modify the connection.

6) Click on the Definition Tab. Then in the Command Type field, choose SQL. In the Command Text field type in the name of your stored procedure. If the stored procedure takes an argument(s), then include them after the stored procedure. Then click OK.

Excel must warn you that you are changing the connection from the table that we originally started with, to the new stored procedure connection. Click Yes.

7) Now you can click on the OK button and see the results of your stored procedure in the Excel Spreadsheet.

There you have it, the excel document has been saved to the SQL Server 2008 R2. I’m going to keep pushing Report Builder 3.0 on them, but until they see the light, this should get the job done.

Thank you!

Larry Heppelmann, QuickStart Intelligence, Systems Engineer

Should you have SQL Server Enterprise Edition for Data Warehouse?

Thu, 14 Jul 2011 22:38:00 GMT

I have recently been teaching a lot of Analysis Services classes at QuickStart, and as we go through the class, features and functionality come up that are only available in SQL Server Enterprise Edition. Some of these are nice to have, but others seem more basic or necessary, depending on your circumstances. In these times of economic hardship and tight budgets, many organizations make the decision to go the SQL Server 2008, but tell their DBA/developers: “We’re getting Standard Edition”. Some students are moving from SQL Server 2000 to 2008, while others are getting started on a data warehouse, and the organization is getting ready to start the project.

Here I’m going to talk only about the first of two SQL Server 2008 Enterprise Edition features: data partitioning. First you need to understand that a data warehouse database is a denormalized database. There is a fact table, which has the measures your business is interested in. A fact table has measures: sales, weight, units shipped, number of visits, length of call, or number of times an ad has run. Measures or facts are given meaning by dimensions. Since we’re looking at partitioning, we’re only interested in the fact table.

Just as with a relational database, a fact table can be partitioned. This is usually done by date, though it can be done in some other way – regions, product lines, etc. Let’s assume we’re doing it by date. Remember that the reason we have a Data Warehouse with a cube, is that the cube has pre-aggregated data, and it has hierarchies of data. Hierarchies can be for products: Product Category (Bikes), Product Sub Category (Racing Bikes), Product (Racing Bike model LA, 42”). Another common hierarchy is time: Years, Quarters, Months, Days or the level of detail that your source data allows you to have.

In order to get aggregations at all those levels, the cube has to be processed. Depending on how many transactions are loaded in to your fact table, it can have millions of rows. And of course, it keeps growing as you add the most recent transactions. Reprocessing the whole fact table can therefore start taking more and more time. But what if you can partition your data.

Let’s say that you have data for this year – 2011. It’s now November. You are only making accounting adjustments to data in October. So all the data for the prior months is stable, and the aggregations should be stable. Similarly for the prior years – those aggregations could be recalculated each time you process the cube, but since the measures or facts are not changing the aggregations don’t change.

With partitioning of your fact table, you can choose to only reprocess the most recent partitions: October and November. In fact, using the new merge functionality, you could have merged the January, February and March partitions into a Q1 partition, the April, May and June into a Q2, and the July, August and September into Q3, while the prior year’s data could be a single partition, and earlier years another single multi-year partition. Now your processing time is greatly reduced.

By having these partitions SQL Server 2008 Enterprise Edition enables you to take advantage of another feature: partition specific aggregation design. In many businesses, more recent data is of the most interest: this year compared to last year, this quarter to the same quarter a year ago, this quarter to last quarter, etc. Given the high interest in recent data, you can design an aggregation scheme with a higher level of aggregation for recent data, and a lower or even zero level of aggregation for old data. Combining this with usage based optimization of aggregations will give you the right aggregations for the most frequently accessed data, and little or no aggregation for less accessed data.

In addition to being able to choose an aggregation design for a partition, SQL Server 2008 Enterprise Edition enables you to choose a storage mode. Your main choices are storing data and aggregations in the cube – multidimensional OLAP (MOLAP), or storing the data (and possibly the aggregations) in the relational database – relational OLAP (ROLAP). For many companies, data older than a few years is accessed using the cube only rarely. Let’s assume access is frequent enough we want to keep the data accessible in the cube, that we’re not going to prune it out of the cube. In that case, we can minimize storage and processing both by using ROLAP storage and a zero level of aggregation for the archived partitions. (We use a zero level since the aggregated numbers are rarely used. ) For current year and prior year data we use MOLAP storage and an appropriate level of aggregation to optimize query responsiveness when users are analyzing the data.

Without fact table partitioning, you will have to reprocess the aggregations for the whole fact table, and have the same aggregation design for the whole fact table. SQL Server 2008 Enterprise Edition allows for fact table partitioning which we can tune our cube so that data that needs to be pre-aggregated for frequent analysis is processed with the appropriate aggregations, while older data, or rarely used data, can be in separate partitions that is stored efficiently in ROLAP storage.

So, one big reason for using SQL Server Enterprise Edition is the functionality of partitioning, which allows you to optimize the design of your data storage, and optimize the design of your data aggregations. In a following article, I will address another feature of SQL Server Enterprise Edition that many organizations find useful: semi-additive measures.

To see the scenario comparison: http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx

To see a complete listing of the features in SQL Server 2008 Enterprise Edition compared to Standard Edition: http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx. Note that there are two categories to look at – Data Warehousing and Analysis Services.

To learn more about SQL Server 2008 Enterprise Edition and our SQL Server training classes, check out our complete course catalog. Or if you have any questions or comments, please reply below.

Thank you!

Saskia Schott, Systems Engineer, QuickStart Intelligence

SQL Server 2008: Database Auditing Standards and Best Practices

Fri, 03 Jun 2011 20:56:00 GMT

This will be the first in a series of blog posts in the next several months designed to touch on many of the topics covered in QuickStart's SQL Server 2008 training courses; mores specifically database auditing with guidance and discussion for SQL Server 2008 and SQL Server 2008 R2. If the next version of SQL Server (code named 'Denali') adds or changes this, I will cover that towards the end of the series. In this first blog, we’ll look regulatory requirements, general organizational security practices, and auditing best practices for databases irrespective to the DBMS chosen. The second blog will continue looking at best practices, specifically what audit event data needs to be in the log and protecting audit systems and data. In the third blog, I’ll discuss the available auditing methods in SQL Server 2008/R2 and the advantages and disadvantages of each one. In the fourth and fifth blogs we’ll do a deeper dive into two specific auditing mechanisms, SQL Server Audit (built-in fine grain auditing introduced in SQL Server 2008), SQL Server Event Notifications (introduced in SQL Server 2005).

SQL Server 2008 Training: Auditing Standards and Requirements

With the growth in importance and pervasive of IT in today’s business has grown government regulations at all levels, from local government requirements all the up to federal and even international regulations and guidelines. This blog is not meant to provide legal advice against the myriad of requirements that exist for data privacy and protection. For that, you will need qualified legal guidance.

A quick search for regulatory audit requirements generates a long list of governmental mandates. The list includes well known laws of the last decade or so, like HIPAA and Sarbanes-Oxley, for health care and financial services industries, respectively. Other regulations exist, such as GLBA, Basel II, 21 CFR Part 11, State data breach disclosure laws, FISMA, FERC, NERC {As an aside, let me introduce you to one of my favorite websites: http://www.acronymfinder.com/ – great for finding the definition of hundreds of thousands of acronyms and abbreviations). To help support the laws, various organizations and partnerships have generated guidance documents and policies, like CoBIT and ITIL.

SQL Server 2008 Training: Auditing Best Practices - What Data to Audit

Let’s take a look at Sarbanes-Oxley (SOX): generally, to comply, you will need to answer who changed or deleted data, made changes to database schema (e.g. dropped a table or a column) and all with special detailed emphasis on privileged users. More of a grey area is auditing unsuccessful attempts to perform those changes. You are generally not required to audit for who accessed data (reads), just for changes. SOX compliance is more about preventing manipulation of data than privacy requirements.

HIPAA on the other hand is definitely concerned with data privacy and will include requirements for who has access – all types of access, including read access – and who has used that access. The need to audit who reads data generally adds additional complexity to auditing solutions and will reduce the choices of available methods.

Auditing best practices require us to analyze our data needs, the regulatory requirements, any additional organizational auditing needs to create an auditing plan. As we configure auditing, we want to only audit for what we need. It is often very easy to include far more information than we need in our audit reports. Too much information can prevent us from recognizing security compliance violations which can be more damaging than just not having all the information asked for by an external audit. Most regulations state which types of information need to have access tracked.

In my next blog we will continue your SQL Server 2008 training as we discuss specific pieces of information that all good auditing solutions include for every auditing event. We’ll also look at specific tools and mechanisms for enabling auditing compliance.

Questions? Please share them below. And of course, if you’re Interested in learning more, look into one of our many SQL Server 2008 training courses.

Thank you!

Steven Allen, QuickStart Intelligence, System Engineer

Microsoft Report Builder 3.0

Mon, 28 Feb 2011 22:09:00 GMT

With the release of SQL 08 R2, Microsoft is making Report Builder 3.0 available. It has been a long road from the original Report Builder 1.0. It was a little clunky, not very intuitive, and was dependent on Report Models. Report Models are an abstraction of the data, and are generally designed by the BI Developer to expose data to the Business User for report creation.

While there are some benefits to using Report Models, (You can give fields and views logical names, group data logically, define relationships where none existed, and use them to enforce security to name a few), it still falls on the BI Developer to create them.

With the advent of Report Builder 2.0, we are no longer dependent on Report Models. Users can connect directly to the data source. The beauty of Report Builder 2.0 was that the clunky interface of Report Builder 1.0 was replaced by a much more intuitive “Office-like” interface. This gives users familiar with the standard Office 2010 interface a head start in understanding the environment. The remaining challenge is that they understand the data that they are working with, but there isn’t much we can do about that here.

Report Builder 3.0 picks up where 2.0 leaves off. It keeps the same intuitive interface, the ability to connect directly to various data sources, but it brings in some new features and functionality that really elevate this product. While it is still designed to be accessible to the Business User (or Information Worker, Microsoft is very careful about not describing users of Report Builder as End-users), the functionality available makes it a viable tool for experienced Report Developers.

Let’s look at some of the new functionality in Report Builder 3.0.

Report Parts

Report parts are report items (charts, graphs, text boxes, etc,…) that you store on a report server, or on a SharePoint site that is integrated with a report server. You can now reuse these report parts throughout your environment in multiple reports. This allows dev groups to utilize the individual strengths of the team members.

When you add a report part to your report, it maintains a relationship to the original instance on the site or server. You can then modify the report part independent of the original report part object. After you have modified your version of the report part, you can save the modified report part back to the site or server, either adding a new report part, or over-writing the original. When someone modifies the original report part on the site or server, you can choose to accept the modification to the report part in your report, or decline it.

Shared Data Sets

A shared dataset provides a way to share a query to help provide a consistent set of data for multiple reports. These data sets can connect to various external data sources, and can include parameters. You are able to configure the data set to generate cached results for a specific parameter combination using a schedule, or just specifying first use.

As with Report Parts, the shared dataset maintains a relationship with the original instance of the data set on the site or server.

Data Sources

In addition to the wide variety of data sources available, Report Builder 3.0 has added a few new data sources.

Report Builder 3.0 also exposes data from SQL Azure, allowing reports to be built from data bases in the Cloud. Data in SharePoint lists can be exposed using the SharePoint List Extension and we can expose data from the Microsoft SQL Server Parallel Data Warehouse as well.

Maps

Report Builder 3.0 adds a Map Wizard, and a Map Layer Wizard as well. A map layer displays map elements based on spatial data from a map in the Map Gallery, from a SQL Server query that returns SQL Server spatial data, or from an Environmental Systems Research Institute, Inc. (ESRI) shapefile.

This allows us to build reports that display data through a geographical interface. You can add interactive features such as tooltips and drillthrough links, or provide parameters that enable a user to interactively control the visibility of each layer.

Report Builder 3.0 also comes with some additional BI type tools, such as sparklines, databars, and indicators. These tools allow us to convey a lot of information, in a relatively small space by displaying a graphical icon to represent large volumes of data.

The more time you spend in Report Builder 3.0, the harder it is to look at it as a tool for non-technical users. It has become a very robust BI tool that every BI Developer should take advantage of. While it is doubtful (to me) that the average Business User will take advantage of all the robust functionality of the product, it is still a benefit to expose this product to them as an ad-hoc report tool, even if they never take it out of second gear. I think Report Builder has made huge strides in becoming a reporting tool that is available to all, yet still has the functionality that only the BI Developers are going to fully appreciate.

Larry Heppelmann, Senior Systems Engineer, QuickStart Intelligence

Reasons Why to Take a Bootcamp Class at QuickStart

Tue, 22 Feb 2011 19:29:00 GMT

With my time at QuickStart I have seen people take training classes for a variety of reasons. Usually, the reasons for attending a class fall under one of these three categories:

I need training for a important project
I need to achieve a certification for my job
I need to retool/recharge

If you want to put forth the work and time, our Bootcamp series can help you achieve your goals. Let me break down how our Bootcamps can help and why you should consider them for your professional development.

I need training for a important project:

Ever feel that “Hurry up and wait” is the pace of your work life? It seems that we usually know what projects we have coming up well ahead of time but we don’t always get to have the timeline we originally plan on. This creates a situation where you don’t have the time to study all that you need to know on your own time or over a period of a few classes. So with our Bootcamps we have created a class that is condensed and covers the technology efficiently, in depth, and focused to maximize your time out of the office.

One of the best examples is our 50292 - MCITP: Windows 7 Enterprise Desktop Support Technician Boot Camp where during a 5 day period you will “learn to identify technical problems that can occur on your organization's client computers, and you'll discover the Windows 7 tools used to monitor and maintain those computers.”

If your situation is that you need to know as much as possible in the shortest amount of time the Bootcamps could be a great fit. Also, with QuickStart's Cancellation Free Schedule and our Lifetime Retake Policy makes it so that you don't have to worry about QuickStart canceling on you and if you need/want to retake the class in the future for any reason, you can sign up for the class again (upon space availability).

I need to achieve a certification for my job:

With all of the pressures from work on our customers we want to help make certification as easy as possible for those who wish/need to certify. With QuickStart’s Bootcamp program we wanted to help streamline this process by being efficient (with time and money) and by providing tools to help achieve your certification goals.

All of our Microsoft Bootcamps use your time efficiently by combining multiple classes that help with certification in a 5 day period. We extend the hours of the class, 8am to 6pm, provide lunch during the week, and cut out course overlap. This is to make your time out of the office efficient and productive as possible. This saves your company money and helps keep disruption from you being out of the office to a minimum. Also, as an added benefit, many times there is cost savings in taking a bootcamp class vs. taking the classes individually.

On tools that QuickStart gives help with certification, all of QuickStart’s Microsoft Bootcamps come with Transcender prep exam(s) and a certification testing voucher. If you had to purchase these tools on your own the cost would be at least $250.

A great example of how we combine classes to achieve certification is if you wanted to achieve your Microsoft Certified Technology Specialist (MCTS) in Business Intelligence Development and Maintenance (Exam 70-448). It is suggested that you take 3 classes:

QuickStart has combined these three 3 day classes into a 5 day period in our MCTSBI08 - Microsoft SQL Server 2008 Business Intelligence Bootcamp to certify. This saves you 4 days (32hrs) of time out of the office and comes with the exam and exam prep tools.

If you want to take a class to help achieve certification please be sure to mention this to your Account Manager. We can help you take the right Bootcamp or class to achieve the certifications you want.

I need to re-tool/re-charge

In the work world we often become specialized in what we need to do in order to complete our jobs and we don't always have a chance to work on new skills and professional development. I personally have found that taking some time out of the office to learn and develop skills can really help re-energize me when I return to work and get me excited to take on new projects. Many of our customers, after training in our bootcamps, come back to work refreshed, excited about their work, become more efficient at there jobs, and can help me take on more responsibility.

One of our most popular bootcamps is our MCTSW2008 - Upgrade from Windows Server 2003 to Windows Server 2008 Boot Camp. This class combines multiple training classes on Active Directory, Network Infrastructure, and Application Infrastructure into a single 5 day course. Despite the name of the class, this course is for anyone familiar with Windows Server 2003 or 2008 who wants to work to get a breadth and depth of knowledge in Windows Server 2008.

I hope that this break down shows the reasoning behind QuickStart's Bootcamp classes and helps make it easier for you to decide if a Bootcamp is the right fit for you. If you want to look at more specific information on classes please reach out to your Account Manager or fill out your information on our contact page and someone will be able to help answer your questions.

Have you ever taken any bootcamps with QuickStart?

Thank you!

Jeff Gunn, Account Manager, QuickStart Intelligence

New Year, New Classes at QuickStart

Mon, 07 Feb 2011 23:27:00 GMT

Well here I am again about to climb back up on my soap box and deliver what I feel are a few important items that I hope you find value in also. As I have been working with our new schedule this year, I have been seeing some trends happening and some new courses rising to the top of the demand lists. I will list these courses and their general topics below, but also want to point out that any class QuickStart offers on our public schedule is a GTR (guaranteed to run) class.

For all of the 22+ years I have been in the technical training industry one thing has plagued CIOs, IT Managers, IT Directors, Project Managers, etc.; having a class cancelled due to low enrollment 1-2 weeks before their employee is scheduled to attend. Class cancellations caused delays in project rollouts, implementations, upgrades, POCs, whatever they were working on because the training vendor called 1-2 weeks before class saying that they did not have enough students enrolled (including yours) to run the class.

To help alleviate this problem, last year QuickStart took the huge step and made a commitment to our customers that we would no longer cancel classes due to low enrollment. So if even one student enrolls in any of our eight QuickStart locations we would run that class. This way our clients can be guaranteed that they will get the training they need when they need it and at the location that is most convenient to them.

I have heard from many of my clients regarding this commitment we made and how pleased they are about it. Of course it would not be possible without our outstanding Distance Learning Program - QSLive - which gives us the ability to offer our classes at all eight of our locations simultaneously, so that our students can attend at the location closest to them or even from their home or office.

The cancellation-free schedule and our commitment to our clients regarding GTR classes, is yet another example of how we here at QuickStart are determined to continue being the leader in quality, hands-on, lab-based, technical training.

These are the four new classes I see taking off in 2011:

50237 - Microsoft Operations Framework (MOF) 4.0 Foundation Certification Course
(Brad's note - Got MOF? Now you can learn the SMFs in the PDO lifecycle phases and not only integrate MOF into your organization but prepare yourself to get the certification as well!)

10337 - Updating your Microsoft SQL Server 2008 BI Skills to SQL Server 2008 R2
(Brad's note - R2 was a huge release for Microsoft. There are a lot of changes and improvements that R2 brought to bear. You will learn all the key changes and features in R2 from Data Warehousing to customized reporting to advanced business intelligence planning.)

50412 - Implementing Active Directory Federation Services 2.0 for Windows Server 2008
(Brad's note - AD FS has been anticipated for some time. Now the course is here and you can learn how to plan, implement, and deploy federation services as well as install and configure PKI for AD FS and learn to troubleshoot AD FS 2.0)

SP726 - SharePoint 2010 for Business Users, Power Users, and Site Owners
(Brad's note - this QuickStart developed course is one of the most in demand new courses we have on this year's schedule of classes. The February and March classes are filling up now. The amount of valuable information covered in this three-day SharePoint course makes it a must attend for anyone responsible for designing, implementing, administering, and maintaining a SharePoint 2010 site.)

Now go learn something!

Brad Fischl, Senior Account Manager, QuickStart Intelligence

SQL Server 2008 Training Opportunities

Tue, 28 Sep 2010 16:28:00 GMT

As SQL Server 2008 R2 has become the next generation of data management and business intelligence data platform, it introduces a number of new features and enhancements including new editions, enhanced business intelligence, enterprise-level scalability, and a better integration with Microsoft Office 2010. Check out one of our blogs from the past on the new SQL Server 2008 R2 features.

QuickStart wants to make sure that you are leveraging the latest SQL Server 2008 technology to help your organization gain business insights. Check out the Business Intelligence Trainings from QuickStart to gather the knowledge and stay competitive:

At the end of courses 6234, 6235 and 6236, you will be able to enhance your learning through the QuickStart exclusive MegaLab™- which you won’t want to miss.

If you are also interested in learning how to use SharePoint as your platform for Business Intelligence, check out:

At QuickStart we are committed to providing the highest quality learning experience to our customers. All of our Microsoft courses are Guaranteed to Run – If you sign up, we’ll deliver. No cancellations!

Also please don't forget about our SharePoint Intelligence Conference coming up on Nov.5 in Santa Clara Convention Center, if you haven't registered already. People that attended the same conference in SoCal last week said "this is the best event I have attended this year!" Click here to sign up.

Wendy Wu, Director of Marketing, QuickStart Intelligence