Category Archives: SQL Server

SharePoint | Creating A Skills Matrix From SharePoint User Profiles Properties

So recently on a LinkedIn discussion it was asked if it was possible to build an out of the box Skills Matrix from the information stored in the Skills field of the SharePoint User Profiles.  Having written about how to create a SQL View to get User Profile Property Bag Values in a previous post, I thought this would be another interesting exercise on a real world use for these views.

Setting up the view is pretty simple, here is the structure we’ll use (See original post for how to set everything up):

CREATE VIEW [dbo].[vw_UserProfilePropertyBag]
AS
SELECT     upf.NTName AS Login, upf.PreferredName AS Name, upf.Email, upf.LastUserUpdate AS Modified, pl.PropertyName AS Property, upv.PropertyVal AS Value, 
                      upv.SecondaryVal AS SecondaryValue
FROM         SharePoint_User_ProfileDB.dbo.UserProfile_Full AS upf INNER JOIN
                      SharePoint_User_ProfileDB.dbo.UserProfileValue AS upv ON upf.RecordID = upv.RecordID INNER JOIN
                      SharePoint_User_ProfileDB.dbo.PropertyList AS pl ON upv.PropertyID = pl.PropertyID

Quick note, in my original posting last year, I only looked for value.  However, with certain SharePoint fields where keywords are used, Value ends up being a GUID and what we’re really interested in is the SecondaryValue field which has human readable content, or as I like to call them actual words.

Once you have your view, running it will produce some nice results:

Login

Name
Email
Modified
Property

Value

SecondaryValue
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 UserProfile_GUID 1A89D7FA-BLAH-BLAH-BLAH-1B5C0A3642DD NULL
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SID 0x010500000000000BLAHBLABLAHBLA NULL
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 AccountName Domain\Admin NULL
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 FirstName Admin NULL

 

Looking at the Property field, we can find the specific property we want (You can also go into Central Admin and manage User Profile Properties to see what the field names are called, but a lot of them will be self-evident by either their name or the values they contain) which in this case would be the “SPS-Skills” property, so a quick query of our PropertyBag view for just this Property will give us a great list of all the profiles with their associated skills:

SELECT *
  FROM [WSS_Content_Views].[dbo].[vw_UserProfilePropertyBag]
  where Property = 'SPS-Skills'

Login

Name
Email
Modified
Property

Value

SecondaryValue
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SPS-Skills D272CB6F-9286-4DFA-A62A-48AA4F189B3B Mad skills
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SPS-Skills 26F8647A-DE5C-48A1-A923-8E791093E23C C#
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SPS-Skills 6B8B9757-F7D8-4C8C-908B-873956DCD78C SharePoint
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SPS-Skills 86519AFC-3643-4D49-A77C-BBBE373EAFFC Social Media

So now that we have our raw data we can do a couple different things with it that aren’t possible with out of the box SharePoint.  Obviously we can query for a specific name to give us a list of their skills, but we can do that already in SharePoint by just looking at their user profile page.  However, we can also query for a specific SecondaryValue, and get a list of all the people who have that skill in their profile.  If we want to take this a step further and create a skills matrix of all the users in the company, just follow these steps:

  1. Run the query for SPS-Skills in something like SQL Manager
  2. Highlight the data in SQL Manager results table and copy and paste it into a new Excel Workbook
  3. Now highlight the data in Excel (I use Excel 2013), click the INSERT tab and click PivotTable
  4. image
  5. If you’ve done everything right so far, a dialog box will pop up looking like this:
  6. image
  7. Click the OK button to add the PivotTable into a new worksheet.
  8. From here, you can set up a basic PivotTable to show you a Users/Skills matrix by selecting the fields and dropping them into the correct Rows/Columns/Values regions:
  9. image
  10. And now you have what could be a very large User/Skills matrix.

That’s just the basics, obviously there’s lots of room to improve the functionality and usability.  You can for instance link the data as a query an external SQL data source.  You can include additional properties so you can further filter the PivotTable by department or office location.  You could event publish the Excel spreadsheet to SharePoint and use Excel services and web parts to expose the data to the end users on their own SharePoint sites.  So with a little creativity and a couple of views you can create some very slick “no-code” applications that rely on data stored in the User Profiles.

SharePoint–SQL Query to Find All Pages With WebPartTypeId

I know that Microsoft says don’t query the databases directly, and that’s still a good practice for any application level code.  But just like in http://iedaddy.com/2012/03/sharepoint-2010sql-view-to-get-user-profile-property-bag-values/ sometimes it’s a quick and dirty way of getting things done, especially when dealing with content database cleanup or when you only need to run the results once or twice to get the information you need.

I had an issue recently where a webpart was being deprecated, and as such we needed to remove it from several large site collections.  Problem was that as this was a Site Collection in the wild, with active users and admins whom have inserted this webpart all over the place, getting rid of the references seemed like it was going to be a slow and painful process.  Removing it from the WebApp and letting things break was not an option.  Now we could use PowerShell to run through all the webs and subwebs, gathering each page and loading up the WebParts collection to see if it’s in there, but in a live farm that gets expensive.

Instead, we can run a very simple SQL query against the content database looking for a specific WebPartTypeID, and then use the list generated to browse to each page and correctly either remove or substitute in a new webpart through the SharePoint UI (which would be Microsoft recommended).

Here is the T-SQL code:

SELECT DISTINCT 
CAST( D.DirName AS varchar) + '/' + CAST(D.LeafName as varchar) as PageURL, 
tp_ID As WebPartSK, 
WP.tp_WebPartTypeId
FROM       dbo.Docs D WITH (nolock) 
INNER JOIN dbo.Webs W WITH (nolock) ON D.WebID = W.Id
INNER JOIN dbo.WebParts WP WITH (nolock) ON D.Id = WP.tp_PageUrlID
WHERE WP.tp_WebPartTypeId='<your web parts id>'

This code will give you a nice list of all the URLs of pages that contain a particular type of WebPartTypeID.  If you don’t know what a particular WebPartTypeID is, the easiest way to find out is to create a page and place the target WebPart on the page, then run the query filtering for just the PageURL of the page you just created.

SQL Server: How to Remap Existing Database Users on New Instance

One common scenario I’ve faced is the need to have a SharePoint database replicated to a disaster recovery site.  Depending on the setup and how the DBAs have provisioned the backup databases, we sometimes face issues where there are orphaned users in the new instance.  Here’s the fix for remapping the orphan database users on the new instance:

/*******************************************************

This procedure should be created in the Master database. This procedure takes no parameters. It will remap orphaned users in the current database to EXISTING logins of the same name. This is usefull in the case a new database is created by restoring a backup to a new database, or by attaching the datafiles to a new server.

*******************************************************/

Use Master 

Go 

IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL

BEGIN

DROP PROCEDURE dbo.sp_fixusers

IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL

PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_fixusers >>>'

ELSE

PRINT '<<< DROPPED PROCEDURE dbo.sp_fixusers >>>'

END

GO

CREATE PROCEDURE dbo.sp_fixusers

AS

BEGIN

DECLARE @username varchar(25)

DECLARE fixusers CURSOR

FOR

SELECT UserName = name FROM sysusers

WHERE issqluser = 1 and (sid is not null and sid <> 0x0)

and suser_sname(sid) is null

ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers

INTO @username

WHILE @@FETCH_STATUS = 0

BEGIN

IF @username='dbo'

BEGIN

EXEC sp_changedbowner 'sa'

END

ELSE

BEGIN

EXEC sp_change_users_login 'update_one', @username, @username

END

FETCH NEXT FROM fixusers

INTO @username

END

CLOSE fixusers

DEALLOCATE fixusers

END

go

IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL

PRINT '<<< CREATED PROCEDURE dbo.sp_fixusers >>>'

ELSE

PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_fixusers >>>'

go

SQL Server – Database Consolidation

imageAs more and more databases come into an enterprise, the question of database expansion and consolidation is often brought to the table.  Capacity expansion costs money, not just software licenses, but also in maintenance and hardware.  At some point there has to be a trade-off between the cost of keeping up performance and storage and the performance of other applications.  This is especially true with the proliferation of SharePoint in many organizations, where the first impulse is to treat SharePoint like a file server which quickly eats through storage space.  However, through database consolidation companies can reduce the number of database servers that they have to manage and reduce the demand for servers and storage. Consolidation can also provide greater operational efficiency. If you can show a better Return On Investment (ROI) for capital expenses like servers, your finance department will love you. 

Types of database consolidation

There are three basic types of consolidation, this includes physical server consolidation, SQL Server instance consolidation and storage consolidation.

Physical server consolidation

This is one of the most popular consolidations, especially as companies find that they have multiple servers that are all underutilized and Microsoft has gone to the per-CPU licensing model.  The physical database server consolidation is the process of consolidating multiple underutilized physical SQL Servers into fewer physical SQL Servers. It is a common problem that many organizations have a number of physical database servers that are underutilized and time must be spent maintaining and patching them.  This often occurs in project-based environments where a new physical database server was commissioned each time an application requiring SQL Server database is deployed. As a result, these organizations end up having multiple instances of various versions of SQL Server servers scattered among various Windows servers running multiple Operating systems.

This type of consolidation helps organizations reduce the number of physical database servers by identifying and consolidating the multiple underutilized physical database servers into single physical database server running multiple instances of SQL Server. In this way, these organizations could end up with the same number of instances just placed in as few physical database servers.

Physical server consolidation is going to save you in terms of hardware, OS, patching maintenance and third-party software licensing.

SQL Server instance consolidation

Each instance of SQL takes up a chunk of memory.  Often the instances are underutilized, each instance being spun up in an attempt to isolate the application and rights to one particular instance of SQL.  This type of consolidation helps to reduce the number of SQL Server instances by consolidating the databases hosted on these instances to fewer instances, freeing up memory.  Instance consolidation might have to do with just dealing with one server, for example you might have seven or eight instances on one server and you want to combine those databases into one instance on that server. This type of consolidation saves on CPU and memory usage, as well as patch maintenance as you have fewer instances to patch during the maintenance window.

Storage Consolidation

Very common in today’s high end storage solutions where a SAN is used for shared storage.  By consolidating the SQL storage companies can invest in high end solutions that are shared among several physical servers, which allows the enterprise to centralize the storage among multiple servers. By consolidating the storage solution this makes a good case for buying some of the higher performing (and more expensive) storage solutions while at the same time making backup and data archiving much simpler and can even include incorporating things like SAN replication keeping in mind you’ll need to review LUN and RAID levels on a regular basis to make sure you’re not running into I/O conflicts with chatty databases that are more heavy towards read or write, for example transactional databases vs. data warehouses and reporting.

Hybrid consolidations techniques

For most enterprises, a combination of the three types of consolidation is usually the most economical depending on the business needs. For global organizations, it does not make sense to consolidate everything into one North American data center if there are specific applications only used in Europe or Asia.  Thus what you typically see is a main datacenter consolidation with satellite data centers consolidated based on performance needs.  However, experience has shown that where possible it is pest to centralize your data into one location in order to simplify administration and maintenance and reduce the overall administrative costs.

Advantages of Database Consolidation

Licenses

Microsoft has moved away from the per-seat licensing model and is headed full steam into the realm of per-CPU.  This means the more databases you can consolidate on a CPU, the more money you save on licensing.  This also means that if you can reduce the number of physical servers, you reduce the number of OS licenses, administrative tools, antivirus solutions, backup and monitoring tools, even rack space.  This can add up quickly to thousands of dollars in savings.  Especially as when our physical servers have proliferated and we have a whole rack or three of underutilized physical servers that can be services by a couple high performance servers.  Remember, if our databases are dispersed unnecessarily, this means that we are paying for extra licenses, which we probably don’t need. With the help of database consolidation, we can easily reduce this unnecessary license cost, because we don’t need to pay the extra licenses and maintenance fees for those decommissioned SQL Servers.

Servers

I’ve seen a lot of powerful servers out there, bought at the time because the project demanded the best in hardware and performance.  Those same servers are running at 5%-10% CPU utilization and only use 4GB of a 24GB memory stack.  It happens.  Departments are sometimes put in a position of use it or lose it and go a little hog wild with their purchases, or the original estimates for performance or capacity were overblown, or even egos get involved of having the biggest and meanest servers.  Database Consolidation helps us to reduce number of physical servers, and those freed up servers can be used elsewhere on new or existing projects.  This only really works well when you have a capacity management group that understands the history of an application and the resource utilization, but it’s well worth the time and effort.  Especially when you have a $60K server running an application where a $5K would perform just as well.  Better to take those servers and consolidate on a few high performing servers and free up the resources.

Soft costs

Electricity costs money, both to power the server and cool it down.  If you’re renting space in a CoLo datacenter rack space costs money.  Every SQL server you run should be managed by a DBA – generally the ratio is something like 12 servers to 1 DBA.  If you have 50 or 60 servers, then that forces you to hire an additional DBA that you might not need if you actually consolidated your databases down to 20 or 30.  Freeing up a DBA means they can work with development to help fine tune processes and be able to tweak systems to get the best performance possible instead of just barely being able to handle the day to day maintenance tasks.

Centralization

Database consolidation also allows us to spend less on network traffic for things like configuration management and monitoring software. For example, let’s say we’ve got some third party monitoring tools that is installed on each our windows servers that monitor and automate patching. If we consolidate our databases down to just a few servers, then this means that we have to do fewer configurations every time we changes to the software configuration, specially with Patch Tuesday and the various security bulletins that Microsoft puts out.  Less servers means a lighter load on the network from monitoring data packets and transmission of patch files.  It may also mean going from a 50 port switch to a 10 port switch.  Database consolidation reduces the networking costs, not only from physical assets such as routers, switches, and cables; But it also reduces the utilization because fewer payloads are being transmitted and potentially with fewer servers that means quicker patch cycles so less risk surface is exposed.

Standardization of SQL Server infrastructure

So this has managed to bite more more than once.  You think you have all your standards in place, you’ve tested your patched before deploying, and then you deploy and something breaks because there was this one server over here that is about 5 patch cycles behind that nobody remembered to test at the time.  If we have defined set of standards for our SQL Server instances and fewer instances to monitor, then there are less chances of a surprise when we are required to make changes.  More importantly, as your standards change you have fewer servers to update to stay in compliance with your standards.  It’s all about no surprises.

Security

Database consolidation also helps us to improve infrastructure security.  We can review the security levels and perform audits in less time because there are less servers.  If someone leaves the company and the policy is to change all passwords of accounts they have access to… imagine the overhead of doing this if they had access to SQL accounts instead of just rights given by AD (Which is a whole other topic).  By reducing servers you reduce surface levels of attacks. For example, if we have our databases dispersed among a one hundred servers as supposed to just ten servers then this means that there are 10x the number of chances of attack because we have to manage and maintain more users and logins.

Where to Start?

With a solid case for consolidation under your belt, the next step for enterprises is deciding how best to go about it. In general, like most projects, there is a logical set of steps to take in order to take on a database consolidation process, which can loosely be translated to the ALM process that Microsoft has made so popular in the Developer world with Team Foundation Server.

Typically database consolidation will involve at least these three steps, but of course mileage may vary and you’ll add additional steps as needed depending on the size of the consolidation, scope and any regulatory requirements.

Envision

Figure out what you’re going to do.  Why are you doing it?  What is the cost benefit ratio of doing this project and the risks if you do not.  The project may die on the vine at this step, and that’s OK.  This is the step where you figure out not only do you want to do the project and does it make financial sense, but where is the finish line.  What does the final result look like and how do you know if the project has succeeded.  Set your goals.

Create a Plan

One of my early managers was always fond of saying, “Plan the work, work the plan.”  If you don’t have a task list and people don’t know who is responsible for what, how do you hold them accountable and how do you know if you’re on track?  At what point are you behind schedule and what impact does it have on the rest of the timeline?  In this stage we identify and document the major steps that are involved in database consolidation project. For example, what kind of hardware do we need, who is going to order it, how much does it cost?  What are the lead times for installation and configuration?  We need to identify resources involved and make sure we’re on their calendar.  Most importantly we need to identify who the key stakeholder of the project is and who is bringing the money to the table to pay for all of this.  If they don’t agree with the goals you set during the Envisioning phase that needs to get hammered out now.  Ultimately, as fun as new technology is, if the business isn’t happy then nobody is going to be happy.  Make sure the goals and steps are clearly defined and that you also include in your plan frequent status meetings to keep key stakeholders up to date.  The biggest reason to have a plan is so that there are no surprises down the road, so remember that plans change and always keep yours up to date.

Implementation

Work the plan.  Order the hardware, get resources working on the project, assign tasks and make sure they are completed when expected.  Each step should be verified and checked off the list when complete.  The plan should be available to all (put it on a SharePoint site!) who are involved in the project.  Remember, a successful project means no surprises.

In Conclusion

Database consolidation has several benefits such as reducing administrative and operational costs, simplified administration, improved infrastructure security, and better usage of computing resources and capacity.  This is a topic that should be regularly visited.  Remember that the enterprise evolves and you consolidate when and where it makes sense.  One of the most frustrating things I’ve heard at companies is “Well, yes we looked at database consolidation a few years ago but it just didn’t make sense.”  This was back when they only had 10 servers and now they are running 40…  This should be a strategic policy and at least once a year should be revisited because businesses change, technology changes, and what may not have been a business need a year ago may be a money maker/saver today.

SQL Server–A Significant Part of SQL Server Process Memory Has Been Paged Out

Recently started seeing this Information Item pop in the event logs:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 2091292, committed (KB): 4140648, memory utilization: 50%%.

This occurs when you are using SQL Sever Server standard edition 64 bit on a Windows 2003 64bit system.  There are a couple of good support articles, but ultimately what happens here is SQL Server is forced to release memory to operating system because some other application or OS itself needs to allocate RAM.

We went through many support articles like:

  • 918483:
    How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
  • 905865:
    The sizes of the working sets of all the processes in a console session may be trimmed when you use Terminal Services to log on to or log off from a computer that is running Windows Server 2003
  • 920739:
    You may experience a decrease in overall system performance when you are copying files that are larger than approximately 500 MB in Windows Server 2003 Service Pack 1

Still had the same issue where this kept popping up and we had extremely slow performance.  Now this was a small VM with 4 GB RAM where 2 GB is maximum limit allocated to SQL Server. This left 2 GB for the OS and Team Foundation Server. We have also turned off antivirus and any large backup job. 2 GB RAM should be plenty because there’s no other app running on the SQL Server box. But the page out still happens. When this happens, SQL Server becomes very slow. Queries timeout, the error logs fill up, transactions abort. Sometimes this problems goes on for 30 to 40 minutes and TFS becomes slow/unresponsive during that time.

Now, what we tracked the cause down to for SQL Server to page out ended up being the File System cache somehow gets really high and forces SQL Server to trim down.  During this time SQL Server gets much less RAM than it needs. Queries timeout at very high rate like 15 per sec. Moreover, there’s high SQL Lock Timeout/sec.

The solution was to use this program: http://technet.microsoft.com/en-US/sysinternals/bb897561.aspx to manually set the Cache of the system file cache.  It’s not a great solution because it needs to be run each and every startup or else the system file cache resets back to unlimited, but useful when you’re working on a sever with limited resources to keep applications running smoothly.

image