Archive for SQL Server

Training: 44CO175 – SharePoint 2010 Administrator Bootcamp

44CO175 – SharePoint 2010 Administrator Bootcamp (BPIO)

Step-by-step understanding is key to successfully implementing and deploying SharePoint 2010. This 15-module course will guide you through each critical stage, giving you exactly the skills you need to leverage full value from the latest SharePoint technology.

Module 0 – Getting Ready for a SharePoint 2010 Implementation: Why Governance and Business Requirements are Essential for a Successful Implementation.
Module 1 – Understanding the Architecture of SharePoint Server 2010
Module 2 – Installing SharePoint Server 2010.
Module 3 – Administrating and Configuring Farm Settings in SharePoint Server 2010
Module 4 – Understanding and Administrating Web applications
Module 5 – Managing Web Applications
Module 6 – Introduction to Site Collections
Module 7 – Creating Site Collections
Module 8 – Managing Site Collections
Module 9 – Working with SharePoint Server 2010 Portals
Module 10 – Governance and Information Assurance
Module 11 – Enterprise Content Types & Managed Metadata
Module 12 – Managing Documents and Records
Module 13 – Workflow
Module 14 – Implementing and Managing Search
Module 15 – People & Social Networking
Module 16 – Disaster Recovery

A handful of notes from the week..

Installation Preparation for Service & Install Accounts 

The SharePoint Installation Account

While I’ve become used to installing applications on servers with either the local or domain admin account, SharePoint 2010 really really really needs to have its own account created for the installation process. If you don’t you’ll have to correct the various service and app pool accounts later and that’s a pain.

Key points about the account used to install with –

  • It will become the application pool account used in IIS for the Central Admin
  • If you do plan to let SharePoint create your databases (content DBs’ etc…) this account needs rights to the SQL database to create DB’s (DBCreator and DBAdmin)
  • If DB’s are already created then it only needs DBAdmin
  • It only needs Local Admin permissions on the installation machine
  • Not to be used in day-to-day admin
  • Imagine this account to be “enterprise admin” of SharePoint.
  • It should never be used again after the initial install, obviously that doesn’t mean disable or delete it.

Example User Account – spinstall

The Farm Admin Account

 The farm admin account is an account that should be used by as few people as possible.

  • It either needs to be a local admin on the SharePoint front end or create a new supper user or login as spinstall(logging in as spinstall is not recommended)
  • The farm admin account change services running on the machine hence the need for local admin rights, farm admin right in SharePoint central admin isn’t enough

Example User Account – spfarmadmin

During the SharePoint 2010 install

 Make sure you move index location during install off from the C:\…\14\data location

 This index file is a flat file used in search and can grow very large in next to no time.

After the install – the Configuration Wizard

Do not use it, it doesn’t follow best practice.

 Performance Tip for SharePoint databases 

Turn off Auto Growth

While it’s been mentioned a thousand times and shouldn’t need to be mentioned…

To prevent it happening in existing installations for any new databases:

SQL Management  Studio > Database > Model. Under the file groups section  change the properties for .mdf to grow by 50MB at a time

Fixing it for existing databases:

Open the properties of each database and under the file groups section, change the properties for .mdf to grow by 50MB at a time

Note: Why 50MB? Because it’s just right for SharePoint since it aligns with the default maximum upload file size.

Registering Managed Service Accounts for SharePoint 2010

  • Best practice is to add them here first then start to use them to run services
  • All are generally fine  as just domain user accounts without elevated privileges

Application Pool Accounts in IIS for SharePoint 2010

 Never change the app pool account from inside IIS as the config DB will not know about the change, web apps will not know about the change and new servers added to the farm will not know which account to use.

One of the few things you can and do need to configure in IIS is SSL certificates and also needs to be done on each and every web front end.


Comments (1)

Training – SQL Server MasterClass (Kim Tripp & Paul Randal –

As posted many times before, continous professional development through training and education is absolutely core of our IT consulting business, The Full Circle (

Today is a good day for that mantra as we are attending an excellent training seminar by SQL MVP’s and founders of – SQL Server MasterClass

My scribbles from the day.. and the formal agenda, speaker bio’s, etc. at the end

Bridging the gap between Development & Production..

DBA’s & Developers not communicating and the reluctant/involentary DBA..

The dilema of development, change control, production scheduling, and customer demands for new features

4 features that break an Enterprise to Standard restore – Partitioning, data compression, …, …

How Design Affects Production
Design considerations such as capacity planning up front to scale databases correctly, try to avoid autogrowth! & Log file fragmentation (LVF’s etc.)

Use of correct data types e.g. Use of bigint for small numbers, datetime when you only really need date, etc. – why use 16-bytes when you can use 4!

Disk maybe cheap, but memory overhead across a system is not.

Indexing – design them, not ad-hoc!

Resouce Governer – limit ad-hoc SQL query users

Naming conventions – obvious in any system (or should be!), makes long term management easier

Design choices affecting maintenance e.g. Fixed vs. Variable length impacting the ability to perform online index rebuilds or not!

Even some some MS apps break this such as SharePoint!

Nested Transactions – avoid!  ..they are evil!  (nested begins, only free resource at end of first transaction)

Instant File Initialisation (IFI) – restores create the file the full with zeros unless IFI is enabled.

Parameterization – trying to get your plan in cache, optimize at dB level.

Phyical dB layout – as dB’s become larger partioning into multiple filegroups is necessary – what’s a large dB? over 100GB you need to consider, TB absolutely!

Measuring your restores to check against your SLA’s – restores take longer than backup, ballpark 25% more, you can break your maintenance window (& SLA’s) by underestimating the restore time.

Primary Key Choice – obvious but not always followed.

Indexing Strategies – difficult!

Database Maintenance – lack of causes problems!

Real Testing!!! – developers need access to similar environment to live otherwise

Myth Buster – top 10 myths debunked, taken from Paul’s Myth a day posts in April at

e.g. Failover Clustering is best HA for SQL..? It depends!
Read more at “Proven SQL Server Architectures for High Availability and Disaster Recovery –”

Tempdb or transaction log should be x% of the size of the largest dB – it depends! If you had a 10TB dB would you have 25% allocated for the log? e.g. 2.5TB wasted on logs.. NO!

use of single user mode, and admin connections.. the :m. and :a. startup switches

DR handbook should be written  by the most experienced member of the team, but tested by the most junior – who’d going to be holding the pager when the system goes bang at 02:00 on a Sunday…?  likely not the architect!

GUIDs: Use, Abuse, and How To Move Forward
Dilema of GUIDs everywere, great for uniqueness, not so great if used for non-clustered indexes and used as a primary key (due to it’s uniqueness) – back to using the correct data types, why use a 64-byte key when a 4-byte key will suffice – for a 10,000 row table this can mean an internal system index of 400Mb, a 64-byte key will be nearer 5GB – just for the system!

Massive performance and storage gains to be had from correct use of GUIDs – see Kimberly’s blog post on this for more info at

The Top-Ten List…

10) Data File Management – you can have up to 32768 files per database, and a minimum of two being the MDF & LDF, but just because you can doesn’t mean you should!  However, careful placement of physical files on different storage types can add huge value to your SQL environment..

9) Transaction Log file management – only one log file is necessary, try and isolate from the data files to avoid disk head contention, use appropriate RAID (not RAID 5 as has high overheads for write & logs are write intensive)

8) Tempdb – tempdb is a special case as it is very susceptible to contention issues because only 1 per instance and 1 tempdb file per processor core, you can now create multiple files to spread the contention. KB328551 maybe suitable in your environment.
Guideline for number of tempdb files, .25-.5 the number of cores.

7) Indexing Best Practice – dont’t go crazy with indexes, as with data files, just because you can.. doesn’t mean you should!  if anything try to reduce your indexes!  Good workload analysis to determine characteristics and query priority as over-indexing can be worse than under indexing.  Poorly maintained indexes can be more trouble in the long term – be sure to automate index maintenance.  A narrow index has very few issues, a wide one has the potential for many more!

6) Statistics

5) Index Fragmentation – Fragmentation Defined.. two broad types: internal (wasted space on data/indexes aka physical defragmentation) & logical (..)
Main causes.. GUID as high-order key, updates to variable length columns, badly configured FILLFACTOR, wide schemas that only fit a small number of rows per page
Keys to success.. how to read the data
How to correct.. create index with drop_existing, rebuild the index, defrag the index
Most folks rebuild or reorganise but their are tradeoffs.. how much disk space have you got? 😉 


3) I/O errors – 823/824 errors have already been retried 4 times before you find out that the I/O subsystem has transient problems.  monitor for error 825 – read errors

2) DBCC CHECKDB – see the blog post
How often should you run CHECKDB – at least once per week – yikes!, how long will it take? – it depends!   but if it starts to take longer it normally means a problem.

1) Recovering Using Backups – best way to avoid data loss but not necessarily the best way to avoid downtime so consider other mechanisms to full backups such as log shipping, mirroring, etc.  Recommend at least Full & Log backups but frequently e.g. full weekly, logs as often as every 5 minutes… however Differentials are better – redundancy in the backups

Q&A – I asked about placement of tempdb in a failover cluster and if there is a downside to putting it on non-cluster shared storage e.g. a local SSD drive or RAMdisk – no, this is fine as long as the same drive letter is available on each node as the tempdb is flushed and recreated everytime SQL starts anyway & got yet another geek polo shirt – my wife will love that… 😉

more info at.. – over 50hrs of free online training resources

Formal Event notes, background, agenda, speaker bio’s, etc.
This special one-day event will focus on some of the top issues companies face when implementing and maintaining a SQL Server-based solution. Very often there is no dedicated DBA and so the IT managers struggle to keep the data tier performing well and the data available. This can be especially troublesome when the development team is unaware of how application design choices affect database performance.

During the day Kimberly and Paul will:
Debunk many of the ingrained misconceptions around SQL Server’s behaviour
Show you disaster recovery techniques critical to preserving your company’s life-blood – the data
Explain how a common application design pattern can wreak havoc in the database
Walk through the top-10 points to follow around operations and maintenance for a wellperforming and available data tier

KEYNOTE: Bridging the Gap Between Development and Production
Applications are commonly developed with little regard for how design choices will affect performance in production. This is often because developers don’t realize the implications of their design on how SQL Server will be able to handle a high workload (e.g. blocking, fragmentation) and/or because there’s no full-time trained DBA that can recognize production problems and help educate developers. The keynote sets the stage for the rest of the day. Discussing some of the issues that can arise, explaining how some can be avoided and highlighting some of the features in SQL 2008 that can help developers and DBAs make better use of SQL Server, and troubleshoot when things go wrong.

SESSION ONE: SQL Server Mythbusters
It’s amazing how many myths and misconceptions have sprung up and persisted over the years about SQL Server – after many years helping people out on forums, newsgroups, and customer engagements, Paul and Kimberly have heard it all. Are there really non-logged operations? Can interrupting shrinks or rebuilds cause corruption?
Can you override the server’s MAXDOP setting? Will the server always do a table-scan to get a row count? Many myths lead to poor design choices and inappropriate maintenance practices so these are just a few of many, many myths that Paul and Kimberly will debunk in this fast-paced session on how SQL Server operates and should be managed and maintained.

SESSION TWO: Database Recovery Techniques Demo-Fest
Even if a company has a disaster recovery strategy in place, they need to practice to make sure that the plan will work when a disaster does strike. In this fast-paced demo session Paul and Kimberly will repeatedly do nasty things to databases and then show how they are recovered – demonstrating many techniques that can be used in production for disaster recovery. Not for the faint-hearted!

SESSION THREE: GUIDs: Use, Abuse, and How To Move Forward
Since the addition of the GUID (Microsoft’s implementation of the UUID), my life as a consultant and “tuner” has been busy. I’ve seen databases designed with GUID keys run fairly well with small workloads but completely fall over and fail because they just cannot scale. And, I know why GUIDs are chosen – it simplifies the handling of parent/child rows in your batches so you can reduce round-trips or avoid dealing with identity values. And, yes, sometimes it’s even for distributed databases and/or security that GUIDs are chosen. I’m not entirely against ever using a GUID but overusing and abusing GUIDs just has to be stopped! Please, please, please let me give you better solutions and explanations on how to deal with your parent/child rows, round-trips and clustering keys!

SESSION 4: Essential Database Maintenance
In this session, Paul and Kimberly will run you through their top-ten database maintenance recommendations, with a lot of tips and tricks along the way. These are distilled from almost 30 years combined experience working with SQL Server customers and are geared towards making your databases more performant, more available, and more easily managed (to save you time!). Everything in this session will be practical and applicable to a wide variety of
databases. Topics covered include: backups, shrinks, fragmentation, statistics, and much more! Focus will be on 2005 but we’ll explain some of the key differences for 2000 and 2008 as well.

Paul and Kimberly are a husband-and-wife team who own and run, a world-renowned SQL Server consulting and training company.
They are both SQL Server MVPs and Microsoft Regional Directors, with over 30 years of combined experience on SQL Server. Paul worked on the SQL Server team for nine years in development and management roles, writing many of the DBCC commands, and ultimately with responsibility for core Storage Engine for SQL Server 2008. Paul writes extensively on his blog
( and for TechNet Magazine, for which he is also a Contributing
Editor. Kimberly worked on the SQL Server team in the early 1990s as a tester and writer before leaving to found SQLskills and embrace her passion for teaching and consulting. Kimberly has been a staple at worldwide conferences since she first presented at TechEd in 1996, and she blogs at They have written Microsoft whitepapers and books for SQL Server 2000, 2005 and 2008, and are regular, top-rated
presenters worldwide on database maintenance, high availability, disaster recovery, performance tuning, and SQL Server internals. Together they teach the SQL MCM certification and throughout Microsoft.
In their spare time, they like to find frogfish in remote corners of the world.

“To call them good trainers is an epic understatement. They know how to deliver
technical material in ways that illustrate it well. I had to stop Paul at one point and
ask him how long it took to build a particular slide because the animations were so
good at conveying a hard-to-describe process.”
“These are not beginner presenters, and they put an extreme amount of
preparation and attention to detail into everything that they do. Completely,
utterly professional.”
“When it comes to the instructors themselves, Kimberly and Paul simply have no
equal. Not only are they both ultimate authorities, but they have endless
enthusiasm about the material, and spot on delivery. If either ever got tired they
never showed it, even after going all day and all week. We witnessed countless
demos over the course of the week, some extremely involved, multi-step
processes, and I can’t recall one that didn’t go the way it was supposed to.”
“You might think that with this extreme level of skill comes extreme levels of
egotism and lack of patience. Nothing could be further from the truth. … They
simply know how to teach, and are approachable, humble, and patient.”
“The experience Paul and Kimberly have had with real live customers yields a lot
more information and things to watch out for than you’d ever get from
documentation alone.”
“Kimberly, I just wanted to send you an email to let you know how awesome you
are! I have applied some of your indexing strategies to our website’s homegrown
CMS and we are experiencing a significant performance increase. WOW….amazing
tips delivered in an exciting way! Thanks again”
Kimberley L. Tripp
Paul S.Randal

Leave a Comment