Like TechNet UK on Facebook
TechNet Team Blogs
<a href="https://saraallison.wufoo.com/forms/m7p7a7/">Fill out my Wufoo form!</a>
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:
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:
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.
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:
You can also use SSMS to configure containment for databases.
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:
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
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
If you would like a detailed version of these tips please mail: firstname.lastname@example.org. For further information on 1E’s integration capabilities with System Configuration 2012, please visit: http://www.1e.com/it-efficiency/solutions/system-management-services/
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.
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.
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
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 ...
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:
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
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