Category: SQL Server


Recently, I’ve been working on a project where the reference data is stored in SharePoint lists. While it is possible to get the information out of the SQL Server database directly, using something like the T-SQL below, it’s a bit messy.

 1: SELECT      dbo.UserData.tp_ID,
 2:    dbo.UserData.tp_ListId,
 3:    dbo.UserData.tp_Author,
 4:    dbo.UserData.nvarchar1,
 5:    dbo.UserData.nvarchar2,
 6:    dbo.UserData.nvarchar3
 7: FROM            dbo.Lists
 8: INNER JOIN
 9:                  dbo.UserData ON dbo.Lists.tp_ID = dbo.UserData.tp_ListId
 10: WHERE    (dbo.Lists.tp_title like 'TestList')
 11:

I wasn’t able to use this to get the data out, as the client doesn’t allow direct access to the SharePoint database, which is entirely reasonable, given that it’s their corporate intranet.

To get around this, I found a very useful set of additional modules for Integration Services (http://sqlsrvintegrationsrv.codeplex.com/), one of which is a SharePoint List Source and Destination. These then allow you to read the data directly.

Using the SharePoint List Source & Destinations

1. The first step is to download the SharePoint List Source and Destination module from http://sqlsrvintegrationsrv.codeplex.com/, and install it.

2. Having done that, you need to start up BIDS (BI Development Studio / VS 2008) and create an ‘Integration Services Package’.

3. You’ll need to add the two new Data flow items into the Toolbox (in Tools > Choose Toolbox Items, in the SSIS Data Flow Items section)

image

4. Add a Dataflow Task to the Control Flow in the SSIS Package.

image

5. Right click on the Connection Manager Section at the bottom of the Control Flow, and choose SPCRED (Connection Manager for SharePoint Connections). Click OK, when the Dialog for the SharePoint Connection opens.

image

6. Then drill into the Data Flow Task, to take you to the Data Flow. In there, drag in a SharePoint List Source

image

7. Right click on the List Source, choose Show Advanced Editor. In the Connection Managers tab, pick the SharePoint Connection you created in step 5.

image

8. Next, click on the Component Properties tab. In this tab, you need to specify the Name of your SharePoint list (SiteListName) and the URL of your SharePoint server (SiteUrl). The SiteUrl is the Parent site within which your List appears. If you want to filter the information from SharePoint, you can modify the CamlQuery section in here, and add a SharePoint CAML query.

image

9. Once you’ve populated this, click on Refresh, and if everything is working, you’ll be able to move to the next tab. If there are errors (such as an incorrect SiteUrl), you’ll get errors like the one below.

image

10. Moving on to the Column Mappings tab, then gives you a list of fields and mappings, representing the Available fields from SharePoint (on the left) and fields that will be available to pass out of the List Source (on the right). You can remove fields that are not relevant here, if you’d like, then click Ok, to return to the Data Flow.

image

11. We need to add an OLE DB Connection manager, by right clicking Connection Managers at the bottom, and choosing ‘New OLE DB Connection’.

12. To get the SharePoint list contents into a database table, we need to add an OLE DB Destination, so drag that into the Data Flow and hook the Green output from the SharePoint List Source to the top of the OLE DB Destination. You’ll then see that there is a red X on the OLE DB Destination, so we need to make some changes.

image

13. Since we need to make changes to the OLE DB Destination, double click on the OLE DB Destination. As shown below, we need to specify a table for the SharePoint data to go to. The drop down list has a list of the tables in the database connected to the OLE DB Connection Manager, so pick a table (if you’ve made one already) or click new to create a new table.

image

14. Then click ‘Mappings’ on the left, and it’s possible to link the field in the source (SharePoint List) to your destination table.

image

15. You’ll then be able to run this SSIS Package, and assuming all is running successfully, you’ll see green boxes.

image

NOTE: Any text fields that are stored in SharePoint Lists, are stored as Unicode strings in the database (so nvarchar).

Further documentation on using these adapters is available here.

This is the first in a series of blog posts I’m planning to do, in preparation for a potential SQLBits session in March 2012.

This article will introduce how, at the most basic level, SQL Server can be communicated with using C++ and Native code, rather than using the .NET Framework.

The code shown below follows through the basic process, defined in the general flowchart for ODBC Applications as seen on MSDN. This was created in VS2010.

Using this process, we connect to a server (a local copy of SQL Server, with AdventureWorks 2008 R2, and does a straightforward query against it to do a Row Count of the Person table.

 1: // The bare basics to query SQL Server, using the Native Client, in C++
 2: //
 3: #include "stdafx.h"
 4: #include <iostream>
 5: using namespace std;
 6:
 7: #define _SQLNCLI_ODBC_
 8: #include "sqlncli.h"
 9: #include "sqlext.h"
 10:
 11: int _tmain(int argc, _TCHAR* argv[])
 12: {
 13:     // Define Handles
 14:     SQLHANDLE hEnv, hDBCCount, hStmtCount;
 15:     SQLINTEGER iRowCount, iRowCountInd;
 16:
 17:     char sConnString[120] = "Driver={SQL Server Native Client 10.0};Server=localhost;Database=AdventureWorks2008R2;Trusted_Connection=yes;";
 18:
 19:     // Step 1 - Assigning an Environment Variable
 20:     SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
 21:
 22:     // Step 1 - Declaring the use of ODBCv3
 23:     SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
 24:
 25:     // Step 1 - Creating a Connection Handle
 26:     SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDBCCount);
 27:
 28:     // Step 1 - Setting Connection Attributes
 29:     SQLSetConnectAttr(hDBCCount, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_OFF, SQL_IS_INTEGER);
 30:
 31:     // Step 1 - Initiating the connection to SQL Server
 32:     SQLDriverConnect(hDBCCount, NULL, (SQLTCHAR *) sConnString, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
 33:
 34:     // Step 2 - Creating a Handle for the Statement
 35:     SQLAllocHandle(SQL_HANDLE_STMT, hDBCCount, &hStmtCount);
 36:
 37:     // Step 3 - Connecting to AdventureWorks2008R2
 38:     SQLExecDirect(hStmtCount, (SQLTCHAR *)"USE AdventureWorks2008R2;", SQL_NTS);
 39:     cout << "USE AdventureWorks2008R2;" << endl;
 40:
 41:     // Step 3 - Executing Query against Person.Person table
 42:     SQLExecDirect(hStmtCount, (SQLCHAR *)"select count(1) from Person.Person;" , SQL_NTS);
 43:     cout << "select count(1) from Person.Person;" << endl;
 44:
 45:     // Step 4a - Assigning a variable to the return column
 46:     SQLBindCol(hStmtCount, 1,SQL_C_ULONG, &iRowCount, 0, &iRowCountInd);
 47:
 48:     // Step 4a - Retrieving the data from the return dataset
 49:     SQLFetch(hStmtCount);
 50:
 51:     cout << "Rows = " << iRowCount << endl;
 52:
 53:     // Step 4a - Remove the Cursor
 54:     SQLCloseCursor(hStmtCount);
 55:
 56:     // Step 5 - Closing down and Cleaning up
 57:     SQLDisconnect(hDBCCount);
 58:     SQLFreeHandle(SQL_HANDLE_DBC,hDBCCount);
 59:     SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
 60:
 61:     return 0;
 62: }

Over the coming weeks, I’ll be expanding on this to get better performance for more complex processes and going through what each of these sections to.

I hope you find these articles interesting.

SQL Server ODBC on Linux

Disclaimer : I’m not a Linux Expert, and I’m sure that doing everything as root is bad, just like doing everything as a Domain Admin account is bad.

Having seen that the CTP version of the Microsoft SQL Server ODBC Driver for Linux has been released, I thought that it would be an interesting thing to play with. Particularly since it might be something I’ll interact with using C++.

Getting Ready

Officially, it’s supported on Red Hat Enterprise Linux, but I’ve not got that, and you have to pay for it (not much, but still). Having downloaded Fedora 16,installed it in a VM (VMWare Workstation), and fired it up, I needed to install a number of prerequisites.

Using the Add/Remove Software option in Applications –>System Tools, I installed these Packages:

  • Development Libraries
  • Development Tools

I also needed to install wget. Type it into Filter box, tick the box against the result and click Apply.

Then download the driver from here: http://www.microsoft.com/download/en/details.aspx?id=28160

Note, that you’ll also need the unixODBC Driver manager, and the current version is 2.3.1. I couldn’t get that working, but 2.3.0 does work, and is available to download here (unixODBC-2.3.0).

Installing it

To get everything to work, I downloaded the files into the Downloads directory, and follow the instructions on the MS Downloads page (copied below, and with an item (3) added by me to make life easier).

To install the driver manager:

  1. Make sure that you have root permissions.
  2. Navigate to the directory where you downloaded sqlncli-11.0.1720.0.tar.gz and extract it:
    cd ~/Downloads/
    tar xvf sqlncli-11.0.1720.0.tar.gz.
  3. (added by me) Copy the unixODBC-2.3.0.tar.gz file into the  sqlncli-11.0.1720.0 folder with
    cp unixODBC-2.3.0.tar.gz sqlncli-11.0.1720.0/
  4. Change to the sqlncli-11.0.1720.0 directory, where you can run build_dm.sh to install the unixODBC Driver Manager:
    cd ./sqlncli-11.0.1720.0
    ./build_dm.sh –help
  5. You can install the driver manager by executing the following command:
    ./build_dm.sh
    Note: you can also download the driver manager manually at http://www.unixodbc.org/ and use the downloaded archive locally:
    ./build_dm.sh –download-url=file://unixODBC-2.3.0.tar.gz
  6. Type “YES” to proceed with unpacking the files. This part of the process can take up to five minutes to complete.
  7. After the script stops running, follow the instructions on the screen to install the unixODBC Driver Manager.

Next up, we need to install the driver, again, follow the instructions from the MS Download page (copied here):

To install the driver:

  1. Make sure that you have root permissions.
  2. Navigate to the directory where you downloaded sqlncli-11.0.1720.0.tar.gz and extract it:
    cd ~/Downloads/
    tar xvf sqlncli-11.0.1720.0.tar.gz.
  3. Change to the sqlncli-11.0.1720.0 directory, where you can run install.sh to install the driver:
    cd ./sqlncli-11.0.1720.0
    ./install.sh –help
  4. (Optional) You may want to make a backup of odbcinst.ini. The driver installation will update odbcinst.ini. odbcinst.ini contains the list of drivers that are registered with the unixODBC Driver Manager. Execute the following command to discover the location of odbcinst.ini on your computer:
    odbc_config –odbcinstini.
  5. Before you install the driver, you may run a verify step to check if your computer has the required software to support the Microsoft SQL Server ODBC Driver for Linux:
    ./install.sh verify
  6. When you are ready to install the Microsoft SQL Server ODBC Driver for Linux CTP, run the install script:
    ./install.sh install
  7. After reviewing the license agreement, type “YES” to continue with the installation.
  8. Verify that Microsoft SQL Server ODBC Driver for Linux CTP was registered successfully:
    odbcinst -q -d -n “SQL Server Native Client 11.0”

Resolving library issues

That then completed the installation. However, I did get a couple of issues when running sqlcmd. These issues were down to different versions of a couple of Linux SSL libraries being installed, rather than the expected version. Having had a root (pun not intended) around, the issues were resolved by adding a couple of symbolic links (kind of like shortcuts, kind of…), by doing this:

ln –s /lib64/libcrypto.so.1.0.0.e /lib64/libcrypto.so.6
ln –s /usr/lib64/libssl.so.10 /usr/lib64/libssl.so.6

Time to Play!

As if by magic, I can now query a SQL Server database, from Linux!

image

This was surprisingly straightforward I thought. My next thoughts will be to see if I can communicate with it from code (C++ since it’s Linux ).

Update – 26/1/2012

It’s been requested that I post the odbc.ini and odbcinst.ini files I used. These are shown below, and are unchanged by me.

ODBC.INI

<empty file>

ODBCINST.INI

[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0
UsageCount=1

Update 16/12/2011 :  I’ve Passed the exam! If you are taking it, the very best of luck!


I’m writing this now, as I have the exam on Monday, and after that point any comments I make on the exam will be influenced by taking it, and therefore restricted by the NDA.

So, here are a list of the Resources I’ve used to prepare for this exam:

Microsoft Exam Preparation Page – This has alot of links to Whitepapers, and books. I’ve read almost all of the whitepapers (all of them by Monday), and 60% of the books.

Online Training videos / Associated Resources – These are a phenomenal resource. Specific, highly detailed training by Paul Randal, Kimberley Tripp, Brent Ozar and Bob Beauchemin. These are useful, even if you don’t want to do the Master Certification.

SQL Skills Training IE1 – I attended the UK course held by SQL Skills in the UK. Ideally, I’d have attended all 4, but due to Training budgets and Time, I couldn’t. This was a great course, and I highly recommend their courses. Being taught SQL Server by such highly skilled Trainers (Paul Randal and Kimberley Tripp) is an amazing experience.

Community Training Notes – Hosted by Neil Hambly (who was also on the IE1 training), this is a great resource also, and has sections of all of the areas an MCM should know. Thanks Neil.

Community events – There are many, SQLBits, SQLPass (24Hours of PASS, and the Nordic Rally event, in particular), SQL Maidenhead, SQL in the Evening, they are all really helpful, and thank you.

SQLBits also have a huge number of videos of the conference sessions available.

Of course, on top of this is the past 12 years of experience I’ve had with SQL Server (though the most has been over the past  7-8 years). For that, I’d like to thank my previous employer JI Software and most of all to my current employer, TAH Limited. I’ve been fortunate enough to work on some really challenging Data warehousing projects over the past few years, including a huge one for Vodafone (read more here).

If you are looking to do the certification, the very best of luck to you!

It’s MemeMonday : What SQLFamily means to you.

Everyone has a family. Only the lucky few (comparitively) have a #sqlfamily. It’s a great way to think of the people we interact with. Some a cool, some are annoying, but all of them bring their own thoughts and experiences.

I’ve not been actively taking part of the SQL community for long, only the past 6 months or so. Before that i was mainly involved with the UK developer community, and when I say involved, I mean attended events. My developer skills were nowhere near good enough to keep up with the likes of Jon Skeet, or many of the other great community-developers.

I’m hoping to become a more active member of the SQL community in the UK, as I go through the SQL MCM certification. I’ve already made a few friends in there, and am starting to feel confident enough to do presentations on SQL Server topics. Even started to do presentations on it internally at work. Maybe that makes me the annoying cousin who tries to hard? That’ll have to be for someone else to decide. 🙂

I’m writing this, sitting in a Starbucks at London Heathrow, on the way to the SQL Rally Nordic event in Stockholm, where I’ll be able to meet some more #SQLFamily members. Mostly the cool uncles & aunts. it’s shaping up to be a great event.

What does SQLFamily mean to me? I love you guys, and appreciate the openness and way everyone is so willing to share their knowledge. Thank you!

I passed this exam on Friday. This was a hard one. Having taken it just over 2 months ago, and failing it, I changed the way I prepared for exams.

Previously, I’d been reading the material on the MS Learning Plan for the exam, and any associated books. For the 70-451 exam I did 6 weeks or so back, I did alot of reading of blog articles, MSDN and Books Online. For this exam however, I didn’t find the Learning plan overly helpful, and there are a huge amount of blog articles on Database Administration (some of which is contradictory).

So, having failed the exam, which isn’t a bad thing, as it proves that the exams aren’t so straightforward that everyone can pass first time, I made notes on the areas I was weakest. Then looked for relevant articles in those areas. The difficult part was finding blog articles by people who knew what they were talking about. A couple of the most useful were John Sansom (particularly the Something for the Weekend: SQL Server Links posts), and the articles from Jonathan Kehayias from SQLSkills (particularly those on Extended Events and Clustering). Thanks to you both!!

I did also find a useful book, and even better, it was one I already had. SQL Server 2008 Administrator’s Pocket Consultant (2nd Edition) by William Stanek was a great help, and while I’d read bits and pieces before, going through it from cover to cover was really helpful.

Having completed this exam, I’ve now completed the prerequisites for the SQL Master Certification, and I’ll be looking to take the Knowledge exam before Christmas.

First though, I’m going to have a break from studying for SQL Server exams. Just for a couple of days. Something different for a change, PowerShell or C++. We’ll see… Just for a couple of days though…

Having spent some time creating posts and doing some work on Virtual Clusters, I saw that Jonathan Kehayias from SQL Skills had also done a set. They are linked below:

Building a Completely Free Playground for SQL Server – (1 of 3) – Downloading the Software and Setting up a Windows Server 2008R2 VM Template
Building a Completely Free Playground for SQL Server – 2 – Setting up Active Directory and the iSCSI Virtual SAN

Building a Completely Free Playground for SQL Server – 3 – Creating the Failover Cluster Nodes and Configuring the iSCSI Environment
Building a Completely Free Playground for SQL Server – 4 – Creating the Cluster

I also suggest looking at his posts, as they cover alot of the functionality that I have, but in some greater detail. Additionally, he covers using the iSCSI Target for Windows Server, which I didn’t know about (Every day’s a school day!). Mine are here (for completeness).

Creating a Virtual Cluster – Part 1 – The Storage
Creating a Virtual Cluster – Part 2 – The Windows Cluster

Creating a Virtual Cluster – Part 3 – SQL Server

Enjoy!

This is part 3 of a set of posts around creating a Virtual cluster on SQL Server, which I’m creating to raise my awareness of clustering.

Part 1 of this series created the Shared Storage used by the cluster, using FreeNAS.MyCluster

Part 2 of this series created a Windows Cluster using this storage.

This section will cover creating a SQL Server cluster using this environment.

The architecture diagram to the right shows the cluster we are creating. This post will cover the clustering of SQL1 and SQL2.

Step 1 – Starting the Process

The first step is to get the SQL Server media available to the server. I’ve taken a copy of the DVD and placed it on the C Drive on the first node of the cluster.

Run Setup.exe, which’ll install some pre-requisites (you should do this on both nodes, to make for an easier life). When the server’s have restarted, run setup.exe on the active node (SQL1 in my case).

Within SQL Server Installation Center, click the Installation option on the Left, and choose New SQL Server Failover cluster Installation.

image

The Wizard will start, and as normal, will take you through installing the setup support files. Once this has completed, the Setup support rules will run, and any issues will be flagged. This includes a set of checks around Clustering.

image

The one that has a warning here, checking to see if MSDTC is clustered, effectively means that there may be issues with linked server queries, and SSIS. It’s not required for the Cluster to build however, so lets press on. Clicking Next gives us the Feature Selection screen.

image

I’m only going to install the Database engine, Integration Services and Management tools. Click Next.

In the Instance Configuration screen, enter the name for the SQL Cluster.(SQLCluster in my case).

image

Click next to confirm the disk usage requirements, Confirm the name of the Cluster Resource group (selecting the default).

image

Click next, and we are prompted to select the disks for the SQL Cluster. I’m selecting all the unused disks.

image

Click Next, and confirm the Network settings for the Cluster.

image

Click next and confirm the Cluster Security Policy (the default being to use the Service SIDs).

image

Click Next to continue,  for the Services, provide a Domain account, and click next.

Next you are prompted for Database engine configuration as normal, click Next, Click Next on the Error Reporting screen. The next screen is the Cluster Installation rules. These should all be passed, so click Next.

image

Click next and next to start the Installation. The installation will take a little while to run.

Upon completion, you’ll get this:

image

If you now look in the Failover Cluster Manager, you’ll see the SQL Cluster in Services and Applications.

image

Next, you can fire up SQL Server Management Studio and connect to the Cluster.

If you go to Server Properties, you can see that the Server is Clustered.

image

The Default System databases (Master, MSDB, Model and Tempdb) are all now stored in one of the cluster drives, and the default drive for databases will also be that drive.

Step 2 – Adding the Second Node

Next we need to add the second node to the Cluster, so switch to your other Member server, and run setup.exe, from the Installation section choose ‘Add node to a SQL Server failover cluster’.

Run through the installation screens, and note that it should pickup the SQL Server Cluster name when you get to the Cluster Node configuration screen.

image

Click Next, enter the passwords for the Domain accounts on the on the Service accounts page, and click next on the Error Reporting page.

The Add Node Rules page should complete successfully, so click Next.

image

Then Click Install to continue.

When this has been successful, you’ll see:

image

Step 3 – Test it out

I’ve taken a backup of the AdventureWorks DW 2008 R2 database from my laptop and copied the backup to SQL1. Note that to restore a database, it must be on one of the Cluster drives, so having copied the Backup file to the shared drives, I can now restore it.

image

Having restored this database, I can now run queries against it, and see what activity is taking place, through the performance graphs on FreeNAS. You can see from the graphs below that are spikes when the Cluster nodes were connecting, and the then later when the database was copied over and restored.

image

image

image

The next post will be around optimising and getting some performance metrics off this cluster.

imageThese are the sessions I’m planning on ‘virtually’ attending at the 24 Hours of PASS event today and tomorrow (7th-8th Sept 2011). I’m planning on adding to this post to give thoughts after the sessions.

Session 02 – Start time 13:00 GMT on Sept 7
SAN Basics for DBAs
Presenter: Brent Ozar

Session 03 – Start time 14:00 GMT on Sept 7
Diving Into Extended Events
Presenter: Jonathan Kehayias

Session 05 – Start time 16:00 GMT on Sept 7
Why PowerShell?
Presenter: Aaron Nelson

Session 10 – Start time 21:00 GMT on Sept 7
Secrets of the SQLOS – Leveraging Microsoft SQL Server Internal Operating System for Improved Scalability and Performance
Presenter: Maciej Pilecki

Session 11 – Start time 22:00 GMT on Sept 7
Hardware 301: Diving Deeper into Database Hardware
Presenter: Glenn Berry

Would have liked to see Grant Fritchey presenting Execution plans, but that’d be finishing at 1am in the UK, and that’s too late..

Session 13 – Start time 12:00 GMT on Sept 8
Zero to Cube – Fast Track to SSAS Development
Presenter: Adam Jorgensen

Session 15 – Start time 14:00 GMT on Sept 8
Disaster Recovery Is Not Just About Technology
Presenter: Edwin Sarmiento

Session 18 – Start time 17:00 GMT on Sept 8
Baseline Basics or: Who Broke the Database
Presenter: Adam Machanic

Session 22 – Start time 21:00 GMT on Sept 8
Important Trace Flags That Every DBA Should Know
Presenter: Victor Isakov

Session 23 – Start time 22:00 GMT on Sept 8
Policy-Based Management in a Nutshell
Presenter: Jorge Segarra

During the my preparation for the 70-451 Certification, I did some work on Partitioning. I found this to be quite an interesting exercise, so wanted to share it.

There are a number of reasons that you could do Partitioning, though primarily they are related to performance, and easier maintenance. However, while you can get an increase in performance, it isn’t the answer to all your problems.

The performance gain is through the ability to have a database table spread over multiple I/O devices (through filegroups), though this also gives you the ability to do partial database restores.

Step 1 – Create a database

The First step, is obviously to create a database to play with.

CREATE DATABASE [sandpit] ON  PRIMARY
( NAME = N'sandpit', FILENAME = N'C:\temp\sandpit.mdf' , SIZE = 200mb ,
   MAXSIZE = UNLIMITED, FILEGROWTH = 256000KB )
 LOG ON
( NAME = N'sandpit_log', FILENAME = N'C:\temp\sandpit_log.ldf' ,
   SIZE = 50Mb , MAXSIZE = 2048GB , FILEGROWTH = 256000KB )
GO
use sandpit;
go

Step 2 – Create a Partition Function

The Partition function is used to determine where data appears in the partitions. You specify it as left or right, so the partition value is either to the Left or to the Right. The sample below is right, so 15/10/1992 (19921015) appears in the second partition.

Partition 1 being infinity to  17530101

Partition 2 being 17510101 to 19990101

Partition 3 being 20000101 to 20101231 etc

create partition function myPF (datetime)
as range right for values
('17530101','20000101','20100101','20110101','20120101','20130101','99990101')

Step 3 – Create a Partition Scheme

The Partition Scheme is used to determine which file group the data goes into. There needs to be as many entries in here, as there are in the Partition Function. You cannot specify less, and if you specify more, they will be used in the next partitions (so if you use the Split function, coming later on!). Also in here, we specify the Partition function that we defined previously, to link the two  together.

CREATE PARTITION SCHEME [myPS] as Partition [myPF]
to ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY])

Step 4 – Create a table using the Partition Scheme

Next we need to create a table. Rather than specifying ‘on <filegroup>’ as normal, we specify the Partition Scheme, and the field used to partition the data. In this case, I’m using the date field for partitioning.

CREATE TABLE [dbo].[myPTable](
    [pKey] [bigint] IDENTITY(1,1) NOT NULL,
    [pDateTime] [datetime] NOT NULL,
    [uidGuid] [uniqueidentifier] NULL,
    [tDesc] [varchar](100) NULL,
 CONSTRAINT [PK_myPTable] PRIMARY KEY NONCLUSTERED
(
    [pKey] ASC,    [pDateTime] asc
))
on     myPS(pDateTime);

This will create our table, but we need data in it, so if you run this script for a while, it’ll populate the table with some random data.

while 1=1
begin
    insert into myPTable
        (pDateTime, uidGuid,tDesc)
    Values (
        dateadd(day, -1 * abs(convert(varbinary, newid()) % ((200*365))),
        dateadd(year,2,getdate())), NEWID(),GETDATE())
end

This script will continue until you run out of disk space, or until the end of time, so you can stop it. You can check the spread of data by running this script. This queries the location of data, gets the partition number, Row count, Min and Max values for the table.

select $partition.myPF(pDateTime) as PartitionNumber, COUNT(1) as RowsInPartition,
    MIN(pDateTime) as MinDateInPartition,MAX(pDateTime) as MaxDateInPartition from myPTable
group by $partition.myPF(pDateTime)

This gives me:

image

Step 5 – Splitting the Partition

As you should (hopefully) see from the query above, there will be significantly more data in the 2nd Partition, than in the others. To help with this, we can split this partition. This can be achieved in two steps: First add a filegroup to the Partition Scheme, then add a split to the Partition Function.

alter partition scheme myPS next used [Primary]
alter partition function myPF() split range ('19500101')

Running the distribution script above, now gives me:

image

There are still quite alot in the 2nd Partition, so lets Split again:

alter partition scheme myPS next used [Primary]
alter partition function myPF() split range ('19000101')

Now we get:

image

Extra Credit 1 – Data Compression by Partition

Partitioning is an Enterprise (and Developer!) edition feature, and so is Data Compression. Given this, we can use data compression on the Partitions, and also have different compression levels on each Partition. So, by using the script below, we can have Page compression on Partition 2 and Row compression on 3-5. (Creating Compressed Tables and Indexes – MSDN)

USE [sandpit]
ALTER TABLE [myPTable]
REBUILD PARTITION = ALL
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1 to 2),
DATA_COMPRESSION = ROW ON PARTITIONS(3 TO 6)
) ;
GO

Also note, that if you split a compressed table, the new partition will keep the compression from the partition before it was split.

Extra Credit 2 – Data Compression by Index

Interestingly, you can also change the Partitioning on an index. This can be carried out in the following manner:

create clustered index IX_myPTablePKey
on myPTable(pKey,pDateTime)
with (data_compression = Page on Partitions(1 to 2),
data_compression = row on Partitions(3 to 7))

There is further information around Creating Compressed Tables and Indexes here.