IO Resource Governance in SQL Server 2014

CPU and memory resource governance is available since SQL server 2008, However the IO resource governance is new in SQL server 2014.

Here is - a very good blog about "IO Resource Governance in SQL Server 2014"

"By configuring the IO settings on the Resource Pools we can control the resources we want to provide for each tenant. This allows us to guarantee predictable performance regardless of the activity from other tenants or even provide differentiation in SLA for the Database Service based on the amount of the resources customers sign up to reserve.

Another scenario that many of you might find applicable is isolating your OLTP workload from any maintenance operations that need to run in the database. Rebuilding an index, for example, is a common operation that can trigger a large number of IO requests, as it needs to scan the whole index or table. By using IO Resource Governance we can limit the number of IO operations these tasks can perform and guarantee predictable performance for concurrent OLTP workload."

automated way to generate classes from a database

Problem:
Is there a quick way to generate classes based on DB schema by a few clicks in Visual Studio?

This seems a common enough problem to worth having an automated solution.

Solution: Turned out Microsoft has something to offer already. Its DBContext generator in ADO.Net entity data model does exactly what I am looking for.

Pr-requisites:
- create your relational database first on a SQL server, and apply physical foreign keys (FK). With FKs, the generator would add child collections in their parent classes. Below is my sample db schema.

- have DBContext generator template activated in your VS. If not, find it by searching online templates.

Steps:

1. Create a new class library project, and add a new item: "Data" - "ADO.Net entity data model".

2. Follow through a few prompts, and connect to the above database and select all tables.

3. Under the model ".edmx" branch, expand your ".tt" file to see the auto-generated classes. 

Interestingly, the class name is changed from "Parents" plural to "Parent" singular. But, didn't bother to change "Children" to Child. Obviously Microsoft didn't invest money here to use an English dictionary. Lol.

4.   Here you go - the nice and neat auto-generated code. Null-able types stay as null-able. 

Very cool. Happy coding by not coding too much!

understanding SQL server 2012 columnstore index

In the past few months, I spent some time playing with SQL server 2012 ColumnStore (CS) index. One of the tests was to apply CS index on tables with 50 million and 500 million rows. On average, query execution time reduced from 10 - 15 minutes to less than 10 seconds. Realized later that even with CS, you might still need to apply tranditional row-based indices to maximize query optimization. One simple way is to let the query optimizer decide when to use which. In general, CS improves performance on large tables using joins and aggregations. For look-ups, the row-based index doing seeks still works faster in many cases.

CS works so fast due to its in-memory technology. It compresses tables on columns and puts them in-memory as a whole. For columns with many repeating values, if they are integers, date-times and numerics with precisions less than 18, they are compressed the most. If you inlcude many text fields with high cardinality, the CS compression won't help much. These texts would also increase storage spaces and consume lot of memories. Had one interesting observation that when I was opening a tabular project in visual studio 2010, the tabular was loading into memory instantly. It consumed about 2 GB of memory for about 1.5 GB of data.

SQL server 2012 SSAS tabular and PowerPivot have built-in CS indices. Tabular runs on a server, while PowerPivot runs on a client PC. If you import millions and millions rows to a PowerPivot into your Excel, you need minimum 4 GB RAM based on Microsoft, 6 or 8GB based on my testing. Your PC must have 64-bit OS.

Some other basics learned about CS -

- CS cannot be applied to views

- After you apply CS to a table, the table becomes read only.

- CS cannot be combined with page / row compression.

reference - http://msdn.microsoft.com/en-us/library/gg492088.aspx

Customize SharePoint top navigation bar by working with Master page, CSS and JQuery

Worked on SharePoint 2010 top navigation drop-down menu for a project lately. It was quite straightforward to use SharePoint designer to edit master page. However, don't overwrite the original v4.Master page without making a copy for roll-back. Enabling the drop-down menu turned out to be easier than I thought. All you need to do is to update SharePoint:AspMenu - ID = "TopNavigationMenuV4" ' s attribute "MaximumDynamicDisplayLevels" from "1" to "2". Done. You can then update CSS combined with JQuery to apply some visual effects and themes. Make sure to register your new CSS file in the master page and provide a correct order. Usually you would order your custom CSS file running after the coreV4.CSS file. The class Dynamic is used for UL and LI to apply drop-down menu themes.

One thing got me is that after I completed the master page, I didn't realize that I needed to publish it as a major version at least once to the master page document library for approval. Once it is approved, this new master page then becomes available for site collection, sites and sub-sites.

It's nice to make a face lifting for your entire site by re-using the traditional .net master page technology. :)

SharePoint 2010 meta-data service

Spent some time to understand SharePoint 2010 meta-data service and the steps to activate it. The meta data service works as the rest of the SharePoint services by creating a service proxy in your farm. Besides, in order to make this available for your site collection, you need to grant site app pool service account permission to the newly created meta-data service. Otherwise, you would get an error message telling you that the service proxy is unavailable.

Then, you can start adding users to be term store administrators for managing and approving document keywords for tagging. They can be built in hierarchical structure to mimic your organizational chart. This tree structure can be used as site menu for navigation. Moreover, these keywords become available instantly in search box. Nice!

SharePoint 2007 does not have this feature. In my opinion, it is hard to claim your site as a content or document management system without a customizable meta-data search-engine. ;)

Enable & Deploy SharePoint Sandbox solution in farm running on Windows 7

Curious about SharePoint 2010 sandbox solutions. Started an empty SharePoint project and selected to deploy it as a sandbox solution on the prompt. Created two simple web parts (not visual web parts), and also added a feature event receiver to create a list at root web, then activated the feature. Got through solution built step successfully, but got an error message during deploy - "Error occurred in deployment step 'activate features' the sandboxed code execution request was refused because the sandboxed code host service was still initializing". 

Back to MSDN, and found this useful link to help me verify if my sandbox solution is ready in my farm, or not.

To summarize

In your central admin,

1.    go to your System Settings -> Manage Services On Server, and make sure "Microsoft SharePoint Foundation Sandboxed Code Service" is showed as started. If not, start it.
2.    go to Manage Applications -> Site Collection -> Configure Quotas and Locks, and scroll down to the bottom to check that your sandbox solution resource quota has a value in field "Limit maximum usage per day to". The default it 300.

 If the daily quota is exceeded, the sandbox solution in your farm will stop executing.

After above steps, deployment went through fine. 

Below are the things I learned about Sandbox, and are worth of noting down.

• It is only good, if you need this solution to be ran in one site collection. You cannot run it across multiple site collections.
• Sandbox solution runs in its own process - SPUCHostService.exe, SPUWorkerProcess.exe, instead of w3wp.exe. 
• For artifacts that require to be deployed to the SP 14 (GAC) folder, sandbox solution usually does not work. However, learned lately that the Visual Web Part (the one that adds ascx file to the GAC) can now be used in Sandbox solution w/ Visual Studio SharePoint Power Tools. For application pages (aspx), they still cannot be used in a sandbox solution.

I also read about using full-trust proxy to allow a sandboxed solution call to a trusted assembly outside the sandbox. Sounds like a work-around here. Overall, not super impressed by this Sandbox feature in SharePoint.

SharePoint 2010 Search 101

SharePoint 2010 has three different types of searches. SharePoint foundation server search, SharePoint server search and SharePoint FAST search. The FAST search requires additional license = $$$.

Not sure how FAST search works. For SP foundation and SP Server searches, each has its own web app pool. By default, the app pools are named as GUID (yike). The search also has their own backend databases. One for crawl, one for application, and one for property store.

Here is a link to get search related PowerShell cmdlets.

Here is a microsoft link to show you how to remove URL from search, if needed. This can be very useful, when you want to hide any sensative information from searching.

After I installed my SharePoint 2010 and started all the services, I got errors in my event log to tell me that "The mount operation for the gatherer application has failed because the schema version of the search gatherer database is less than the minimum backwards compatibility schema version supported for this gatherer application." After done some research online, I found out that I needed to upgrade to SharePoint SP1. Here is a useful Micorosft link with some detailed steps for the upgrade. In order to find out if your environment requires an upgrade or not, you can run this PowerShell command - "(get-spserver $env:computername).NeedsUpgrade". If it returns TRUE, time to upgrade. After install SP1, need to run "psconfig –cmd upgrade –inplace b2b -wait".

All above just touched surface of SharePoint search. It is a BIG topic on its own. I recalled the days I served as server engineer to install TREX to crawl and index SAP documents. It was not an easy task. New to SharePoint 2010 search. I guess that the fun / pain has just started...