Angela Cataldo works for Firebrand Training as a subject matter expert and instructor for SQL Server and System Centre. For over 10 years Angela specialized in SQL Server, delivering training and consultancy services to a number of companies throughout the UK and Europe guiding and mentoring customers to follow Microsoft Best Practice and assist in their understanding and adoption of SQL innovative features.

Before SQL Server 2012, databases have always had a degree of portability. But with SQL Server 2012 we can now embrace the powerful new manageability and security features of Contained Databases - which make a database much more portable.

Why do we need Contained Databases?

SQL Server security has always been managed at two levels:

Logins are managed at Server level, and users are managed at Database level. This means permissions for SQL Server have to be defined in two or more locations, and this can cause confusion.

Also, having to manage logins and users separately can cause problems when it comes to maintaining high availability and disaster recovery solutions. And the need to regularly synchronise logins against failover and secondary servers; to avoid problems such as orphaned users.

So with the introduction of containment and the concept of boundaries in SQL Server 2012; a database can become free of external dependencies, server level metadata, settings and security logins.

For a Database Administrator this can also potentially help with the problem we have all faced after recovering a database: repairing a large numbers of logins using the sp_change_users_login stored procedure.

What is a Contained Database?

In simple terms it is a database that is isolated from other databases, and isolated from the instance of SQL Server that is hosting the database.

There are four ways that SQL Server 2012 helps to isolate databases from the instance:

• Much of the metadata that describes a database is maintained in the database and not in the master database
• All metadata is defined using the same collation
• User authentication can be performed by the database, reducing the database’s dependency on the logins of the instance of SQL Server
• The SQL Server environment reports, DMVs and xEvents can act upon containment information.

How to create a Contained Database

In this example I am going to demonstrate - in four steps - how to create and authenticate against a Contained Database:

1. Enable Contained Database Authentication
2. Create a Contained Database
3. Create a user in the Contained Database
4. Authenticate a user against the Contained Database

Step 1

First I need to enable contained database authentication, by executing the following code as a New Query in SQL Server Management Studio (SSMS) against the master database:

The first sp_configure line reports on the current setting and the second sp_configure line enables the server-level setting. This allows SQL Server to defer authentication to the database, provided that we have configured the users correctly with the right authentication.

Step 2

Now I can create a contained database, executing the following code as a New Query in SSMS:

If we take a look at the Database properties of this database we can see on the Options Select a Page the menu option for Containment Type:

• Partial – boundary defined isolating the database from the server instance
• None – the default containment type for a database

You can also use SSMS to configure containment for databases.

Step 3

Now we have a contained database we next need to create a user by executing the following code as a New Query in SSMS:

For further syntax of Create User see SQL Server Books Online: CREATE USER, examples cover:

• Creating a contained user for a domain login
• Creating a contained user with a specific SID

You can also use SSMS to create a contained user, for User Type selecting SQL User with password:

We can also take an existing user and convert it to a contained user executing stored procedure sp_migrate_user_to_contained.

For an explanation of syntax see SQL Server Books Online: sp_migrate_user_to_contained

Step 4

Now we can take the final step and login in as a Contained Database user, ensuring that in the Connection Properties, under Connect to database, is our Contained Database AdventureWorks2012.

When connecting to a contained database, if the user does not have a login in the master database, the connection string must include the contained database name as the initial catalog. The initial catalog parameter is always required for a contained database user with password.

In four simple steps I have enabled database level authentication, created a contained database and contained user then logged into SQL Server Management Studio as the new user.

What else do I need to know?

As a Database Administrator, security is a major concern and there are unique threats when using Contained Databases that must be considered. Thankfully SQL Server Books Online has a dedicated page on these implications: Security Best Practices with Contained Databases.

An example is passwords in a database require to be strong, complex passwords - and cannot be protected by domain password policies. Therefore, wherever possible create contained users for domain logins and take advantage of Windows Authentication.

Contained databases are set to be one of the top new features for DBAs. Plus AlwaysOn Availability Groups are also new to SQL Server 2012 - helping to simplify environmental and failover concerns, to ensure a highly available disaster recovery solution. SQL Server 2012 Database Containment is simply one of the best things to have happened to SQL Server.

Microsoft recently introduced System Center 2012, a tightly integrated management solution built from the ground up for automated private cloud application and infrastructure management. IDC interviewed a range of System Center 2012 early-adopter customers about their private cloud strategies and the role that System Center 2012 is playing in support of those programs. This white paper discusses IDC's industry-wide views on private cloud management trends and priorities, describes how System Center 2012 is addressing these needs, and highlights System Center 2012 customer experiences and lessons learned. The goal of this paper is to equip IT decision makers with a context for designing their own private cloud management evaluations and pilot projects.

Download the WhitePaper

1. 1E Consultants took a deep dive into System Center 2012 Configuration Manager and here are their top tips for success.
2. Application model – ConfigMgr 2012 introduces a completely new alternative approach to software distribution – the Application Model. With the Application Model, an Application has a number of Deployment Types, each defining the required source files, install and uninstall command lines and user experience (e.g. does a user need to be logged in?), similar to the properties of the legacy Package and Program.
3. Site hierarchy – data can be now coordinated and replicated throughout the Central Administration Site (CAS), while boundaries can be set for all ‘sites’, providing a simpler, minimalist way to manage the IT infrastructure. A Single Site ConfigMgr 2012 hierarchy will be a reality for most organizations with less than 100,000 clients to manage.
4. Site-to-site replication – Site-to-Site communication has received a major overhaul in ConfigMgr 2012 with the introduction of SQL replication replacing most of the legacy file transfer in and out of inboxes. Changes in any Primary Site database will be replicated globally to all Sites in the hierarchy, not just the immediate parent or child.
5. Administration – The Admin Console in ConfigMgr 2012 has been completely redesigned and written from the ground up. It does not use MMC, displays only the features the admin has rights to and has a separate MSI for installation. The revamped admin security model offers a combination of security roles, collections and security scopes to define what objects an administrative user can see and the types of actions they can perform.
6. Managing clients over the internet – New to ConfigMgr 2012 is the possibility for internet-based clients to evaluate user-based policies (such as Application Deployments).
7. Scalability – support for up to 400,000 clients in a Single Site hierarchy when the database for the CAS is running SQL Server Enterprise. Each Primary Site can support up to 100,000 clients if the database and Primary Site roles are hosted on separate servers.
8. Distribution points – There are some notable changes in the role of the Distribution Point (DP) in ConfigMgr 2012. Essentially a single Distribution Point replaces the previous Branch Distribution Point.
9. User in control – ConfigMgr 2012 has been built with the user in mind. The Software Center provides an accessible interface for the user to view and manage all software installed, as well as a new level of control over the actions that will impact users the most.
10. Client health and efficiency – new agents such as Health Evaluation and Endpoint Protection, as well as the Automatic Deployment Rules feature, work to ensure clients remain healthy, operational and efficient.
11. Client configuration – the introduction of ‘profiles’ replace the previous blanket global approach to configuration, allowing more specific settings dependent on your needs. What is really cool with this interface is that new classes can be added by connecting to WMI on any computer and browsing to the class you want to report on.

If you would like a detailed version of these tips please mail: su.kent@1e.com. For further information on 1E’s integration capabilities with System Configuration 2012, please visit: http://www.1e.com/it-efficiency/solutions/system-management-services/

Useful Links

• Download System Center Configuration Manager
• Pricing & Licensing for System Center Configuration Manager
• Upgrade paths to System Center 2012
• VMware (finally) admits that its costs are higher than Microsoft’s

Paul Gregory is one of QA’s principal technologists – specialising in delivering training around Microsoft Server operating systems, virtualisation and systems management. During a 29-year career within IT, Paul has helped many international organisations develop infrastructure solutions based on Microsoft technologies, as well as supply training services during the last 14 years. Paul has helped QA deliver numerous Microsoft partner training skilling programmes for Microsoft – particularly around the areas of Microsoft Server operating systems, virtualisation and System Center. Paul was also heavily involved in the recent Microsoft Windows 8 / Server 2012 TAP programme where he played a key role in the testing of core Windows Server 2012 technologies and positioning this information back to product specialists in Redmond. With the advent of the Microsoft Private Cloud solutions based on System Center 2010 & 2012 Paul have been responsible in helping Microsoft prepare the Partner channel both in the US and Europe for these technologies.

Often customers I come across install SCOM and panic the main reasons for this are:

1) Trying to do too much too soon

2) Not fully understanding their environment

3) Not understanding SCOM tries to predict issues

There are a few other reasons but we do not need to worry about them here. But this gets me to where I want to be the noise. Starting with item (3) it is important that SCOM tries to predict events so there is always a balance between being noisy and missing events which need to be reported to predict a future event and where that line needs to be drawn will vary from one organisation to another.

One area I see people struggle with this is managing basic hardware capacity issues. For example monitoring free disk space. The main problem is most systems today will have fairly small OS drives and much larger data volumes so different thresholds need to be set. However the default rules for managing free disk space apply to all drives in a computer. To be able to manage this correctly a number of things need to be put in place for best practise.

1) Standardize Server Builds – I often here that server builds are a bit random, it is never too late to standardize the build.

2) Create SCOM Groups for each drive (steps below)

3) Set Overrides for each drive group and for each OS type.

This model will then allow different disk space thresholds to be set for each group of hard drives.

SCOM Drive Groups

1) From within the SCOM administration console select the Authoring panel

2) Select Groups and choose Create Group on the right

3) Give the group a name and description and create a new management pack for storing Windows Server Hardware Monitoring Overrides in if one does not exist

4) Press Next until on the Dynamic Members page

5) Press the Create/Edit button

6) In the drop down box choose either “Windows Logical Hardware Component” or “Logical Drive (Server)”. These allow you to select drives based on name or other properties. Press Add

7) In the table change the first Drop Down box to “Display Name” in the third box enter C: Press OK

8) Complete the wizard

9) Repeat to create any other groups for other Drive letters you wish to set separate rules for.

By Paul Gregory

System Center configuration Manager introduced many new features. One of the features revolving around the new User Centric element of the product is the Application Catalogue which allows users to select software they would like to install and if required have it Approved by an Administrator.

One question I get asked a lot is supporting this functionality it untrusted forests and this is possible. To enable this support a few things need to be considered

· The Application Catalogue server has to be able to authenticate the users that connect to it

· Configuration Manager needs to know about the users that will request applications

To enable this cross forest support the following steps need to be performed

1) Install the Application Catalogue Web Service in the same forest as the SCCM database

2) Install the Application Catalogue Website in the untrusted forest giving SCCM credentials to deploy the role to a member server in the remote forest

3) The Application Catalogue Web Service and Website will communicate using Self-Signed certificates these can be replaced with certificates from a PKI infrastructure if needed

4) Enable User Discovery or User Group Discovery for the remote forest in SCCM. This is needed because applications displayed in the catalogue are based on the collection targeting so the applications will need to be targeted within SCCM to the users in the remote forest.

Paul Gregory is one of QA’s principal technologists – specialising in delivering training around Microsoft Server operating systems, virtualisation and systems management. During a 29-year career within IT, Paul has helped many international organisations develop infrastructure solutions based on Microsoft technologies, as well as supply training services during the last 14 years. Paul has helped QA deliver numerous Microsoft partner training skilling programmes for Microsoft – particularly around the areas of Microsoft Server operating systems, virtualisation and System Center. Paul was also heavily involved in the recent Microsoft Windows 8 / Server 2012 TAP programme where he played a key role in the testing of core Windows Server 2012 technologies and positioning this information back to product specialists in Redmond. With the advent of the Microsoft Private Cloud solutions based on System Center 2010 & 2012 Paul have been responsible in helping Microsoft prepare the Partner channel both in the US and Europe for these technologies.

Last week Andrew and I presented a number of sessions at TechDays Online in the UK and we received numerous requests during the day to publish the decks so that people can peruse them at their leisure.  Well those decks are now here available for you to click through and download if you so wish.

Windows Server 2012

Windows 8

Private Cloud

Have you heard Microsoft talking about the System Center 2012 Orchestrator IT Process Automation solution? Do you wish you knew...

· What Orchestrator is?

· How Orchestrator has helped companies reduced their datacenter operational costs by 20‐40%?

· How to find out where Orchestrator can help in your business?

Kelverion are System Center 2012 Orchestrator experts.  We have more than 5 years’ experience in building large, complex automation solutions using Orchestrator and Opalis and over the years have faced virtually every challenge thrown up when implementing Orchestrator.

Attend this valuable webcast System Center 2012 Orchestrator Overview, Wednesday, November 7th at 11:00 am Greenwich Mean Time.
Learn ...

1. What Orchestrator is
2. What automated use cases other customers have implemented
3. How to find out where Orchestrator can help in your business

Join Kelverion for System Center 2012 Orchestrator Overview, Wednesday, November 7th at 11:00 am Greenwich Mean Time.

HDInsight is Microsoft’s 100% Apache compatible Hadoop distribution, supported by Microsoft. HDInsight, available both on Windows Server or as an Windows Azure service, empowers organizations with new insights on previously untouched unstructured data, while connecting to the most widely used Business Intelligence (BI) tools on the planet.

Please visit the Microsoft Big Data home page here for more details, to download HDInsight for Windows Server, or to sign up for HDInsight on Azure.

Mark ‘Fitz’ Fitzgerald is the principle technologist for business intelligence, covering SQL, PerformancePoint and ProClarity at QA. He is a twenty-year veteran of the IT industry, with experience that ranges from mainframes, help desk and MIS systems. Mark has experience developing business applications in a range of products which enhance and distribute accurate, timely information within organisations. Mark has been with QA since 2000, and in 2003 & 2006 he won QA’s Trainer of the Year Award. Mark’s enthusiasm knows no bounds and training sessions often spill into breaks, lunch times and early evenings if not interrupted!

Business Diagrams using SSRS Map Control

Many businesses need to be able to produce business-oriented diagrams using SQL Server data. This can pose a problem for the SQL Server user, many of whom rely on Visio services or third party tools to be able to produce the reports which the business demands.

However, it is possible to use spatial maps within the reporting services element of Microsoft SQL Server 2008 R2, to create diagrams for use within business reports – whether this is from data stored in SQL Server as geometry/geography data types, embedded within the control itself (US only) or by using an ESRI shapefile. The diagrams below illustrate the level of reporting capability possible using this technique. All of the diagrams below are calculated from a standard parent and child relational source.

Below is a list of types of diagrams typically requested by the business:

• Hierarchies : a simple parent and child hierarchy, allowing for multiple root members, colouring of the boxes (to show seniority or performance), naming of the boxes and providing tooltips
• Multiple proportional pie charts : changing the size of the charts for successive periods to show growth or shrinkage, maintaining the essence of the proportions for each segment
• Nightingale Rose: similar in design to a pie chart combined with a stacked bar. The segments around the chart are evenly spaced, but the radius of the segment changes and are proportional to the values shown
• Extending maps with Sparklines: taking a geometric map (such as that above, based upon UK Ordnance Survey data using eastings and northings) as a base, sparklines charts can be added with links to the geometric shapes.
• Networks: a simple diagram showing objects (tasks, stages, people) and the flows between them. This could be a workflow, a rail network or sites to specific network links
• Gantt charts: although not a replacement for project management software and professional diagrams, a Gantt can be produced using geometry shapes

Making these available using SSRS will allow clients to visualise the data better and give the developers additional options for display. It is not likely to replace the common chart types available within the product, but with a little thought and effort most diagrams are possible.

All of the diagrams below are possible using standard TSQL objects (user defined table data types, user defined functions and stored procedures). No CLRs are used in creating the diagrams and each performs adequately.

Chart Type and description	Example
Hierarchy : hierarchical view of items dependent upon parent and child arrangement - organisation chart, hierarchical KPI, viewing a decision tree
Multiple proportional pies : growth of sales over time with the proportion of each sector
Nightingale Rose : changing sizes and proportions over time
Geometric map with Sparkline pies included : proportion of sales by category split regionally
Gantt Chart : tasks to the performed with dates
Network : tasks and dependencies between them

Useful Links

• Download SQL Server 2012
• SQL Server Product Page
• TechNet Library for SQL Server 2012 (Includes Whitepapers, Documentation & Samples)