Azure FAQ 2 : How to migrate on premises SQL Server database to SQL Server on Azure VM (IaaS)

This post will provide the step by step process of migrating or moving or copy a  database from On premises server to Azure VM installed with SQL Server (IaaS)

Step 1 : Create a VM in Azure with SQL Server Installed : Assuming you are aware of the process of creating a VM in Azure.  Here is the screen shot of VM created in my account

Step 2 : Connect to VM by clicking on Connect button as shown in the below picture and it will prompt you to save the RDP File which you can save locally. 

Step 3 :  Save the RDP File locally and Right click and Edit the file. 

Step 4 : The below picture shows how you can edit the RDP File and attach the local drive to Azure VM.  Once you complete the mapping click OK 

Step 5 : Double click on the RDP file to connect to VM. 

Step 6 : The below picture shows the logged in VM and my laptop D drive mapped to VM. 

Step 7 : Now that my Local drive is mapped to the VM, i can copy paste any files from my local drive to VM drive. In a scenario where we need to move a backup file (provided it is small in size, if not there are other optimized way to do it which will be covered in other post) , we can just open the the mapped drive of our source machine and copy paste the file to Azure VM. 

The next picture shows the file i copy pasted from my local drive to Azure VM. I copied a 50 MB backup file from my local drive to Azure VM which took close to 3 Minutes.  After the file copied and the file was unzipped and the total file was of 150 MB approx. 

Once the backup file is copied to a local drive in Azure VM, restoration of the backup file is same as we do in on premises systems. Assume you are aware of the restoration process and below picture shows the restored DB. 

Summary : Moving a on premises database to a Windows Azure VM (IaaS) is as simple as moving a DB from one local on premises server to another Server.  This shows how quickly can we setup a VM and start our project. Moving a 150 MB DB from my local SQL Server Instance to SQL Server on Azure VM took  less than half an hour for me. 

Azure FAQ 1 : How to view Windows Azure usage and Billing summary

1.       Login to Azure Portal using Azure account - https://portal.azure.com/

2.       Click on the subscriptions in the Hub Menu

3.       You will have all the subscriptions available for the logged in account.

4.       Click on the subscription for which you need to view the bill

5.       Click on Manage; this will open a new window. https://ea.azure.com/report/usagesummary

6.       In this window you can see the complete billing info and you can download the report as well. 

FAQ: What is Powerview

Official Answer to this question is – An interactive data exploration, visualization and presentation experience . It is one of the core component of MS Managed Self-service BI initiative. Power View, formerly known as “Project Crescent,” is an end-user focused tool ie UI that provides easy to use ad-hoc reporting and data exploration for users ranging from analysts to information workers. Reports can be created from data in PowerPivot workbooks or tabular and multidimensional (SQL Server 2014+)   BI Semantic Models deployed to SQL Server 2012 Analysis Services. Running in a browser, powered by SharePoint Server 2010+, Power View enables users to present their insights through highly interactive visual presentations.

Powerview was first introduced in SQL Server 2012 and in Office 2013 release Powerview was integrated to Excel ( Powerview is not integrated with prior version of Excels).  As Powerview is one of the core components in Microsoft Self-service BI initiative, it helps business analysts or business users (non-IT crowd) to build various types of BI reports without much support from IT.  (Reportbuilder was the first step towards the adhoc reporting capability in SQL Server reporting service but it had lot of limitations. )

Powerview is a Metadata driven reporting solution. it connects to Analysis Services BI Semantic Model.  What it means is, Powerview directly does not connect to raw data/data warehouse, it connects to BI Semantic model and model intern connects to raw data(please note that even Powerpivot has its own BI Semantic model). BI Semantic model provides analysts or users a universe from where they can create reports.

Powerview can connect to a local BI Semantic model builtin which Office 2013.  When you use powerview in this mode, it has analysis service engine build into excel.   

FAQ : Why we need BI Semantic Model?

The Semantic Model helps us in various ways. The major benefits of Semantic model are as follows : -


Single Source of Truth : By providing a BI Semantic layer with user friendly definition, Relation , business logic and data access methods,  all those who consume the data will have consistent and correct data.

Readability :  Often the object names may not be user friendly because of various reasons like Product limitation, legacy database as data source or the technical terminology in the physical data model.  In BI Semantic model we can provide more meaningful object names. We can also define the relations even if it is not available in the data warehouse and there by provide more readability.

Layer of Abstraction : BI Semantic Model provides a layer of abstraction since the users access the BI Semantic Layer not the raw data(data warehouse)

Self Service/Personal BI  :  Once the Semantic Layer is defined and published, the data consumer can function independently without support from IT.  This provides a capability of Self-service BI and Personal BI.

FAQ :What is BI Semantic Model in MSBI?

Wiki page says “A semantic layer is a business representation of corporate data that helps end users access data autonomously using common business terms”. BI Semantic Model in MSBI  is a layer between Data warehouse and reporting tools/Users. BI Semantic Model maps the data structure/schema of data warehouse or any data source in more business oriented terms with all the possible relations defined and business logic implemented which can be easily understood by both the business users and the IT Professional.  In general, we do not provide direct data warehouse (Raw data) access to the data consumers. Data Consumer will be accessing the BI Semantic Model , BI Semantic Model will be in turn connecting to the data warehouse or data source and in that way it gives a layer of abstraction. BI Semantic Model can address all the BI requirement be it Reporting, analytical, scorecard & dashboard , Personal/Self Service BI and custom application.  In larger organizations each department may have their own BI Semantic layer as “Single source of truth” so that the users can consistently consume the correct data.

 BI Semantic Model in SQL Server has three major elements. Data Model, Business logic  and Data Access.  There are three different approach to create BI Semantic Model in SQL Server. PowerPivot Workbook, Tabular Model and Multidimensional Model

SQL Server 2012 Licensing made easy

My association with a leading technology service provider has provided me with ample opportunities to work on numerous upgrades and cross platform migration proposals (of course SQL Server as target). It is observed that, in general customers are expecting a sizing solution along with the proposal response, to facilitate their initial Program/project budgeting. Sizing includes both hardware and software aspects. Actually speaking, the SQL Server licensing cost (for that matter any product) may vary from customer to customer based on the partnership that they have with Microsoft. However, during proposal, the customer would need an approximate number based on the listed price. Since SQL Server 2012 release, it is been little puzzling for me to provide a best cost effective approach in proposal because of the new licensing model. That is the reason I thought to get some more insight to SQL Server 2012 license model. We can always use  Microsoft License Advisor   Tool,  but there are case by case migration plan also available for the existing license model (SQL Server 2008 Socket based or CAL Based) . Before getting into the details, you may need to understand few jargons used on Microsoft licensing topology.

Enterprise Agreement (EA)- is a volume licensing package offered by Microsoft. It primarily targets large organizations that have 250 or more personal computers. The Enterprise Agreement, whose price is tiered to the number of computers being licensed, is a three-year contract which covers all software licensing and updates for one client system. An option is given at contract termination to renew for one or three additional years.

Software Assurance(SA). Wiki says “The core premise behind SA is to give users the ability to spread payments over multiple years, while offering "free" upgrades to newer versions during that time period.” To put it in perspective, what it means is, if you have SQL Server 2005 license and you have been paying SA cost (yearly) for each year , you will be automatically eligible for all the new versions like 2008, 2008 R2 and 2012. Please read this wiki page for more info. There are many other benefits you get with SA.

Server +Client Access License (CAL) : Precisely, Pay for the user connections that you need. You will have to buy licenses for the Server(s) and the number of users or devices connecting to it. Suppose you have a SQL Server Development project where in 25 Developers are going to work, then what you may need is, One SQL Server 20012 Developer Edition Server License + 25 CAL license. (if you have MSDN Subscription of course developer edition is for free) . This licensing model is NOT available for SQL Server 2012 Enterprise edition by default. But under SA/EA there are migration plan with certain condition which you will have to talk to Microsoft licensing expert and they will tell you the options based on the relationship your organization have with Microsoft.

Socket or Processor based Licensing model : - Prior to SQL Server 2012, SQL Server used to follow Per Processor based licensing model. (I used to sell this as the strong business case for Migration to SQL Server from Oracle or any other products). Irrespective of CORE you have, all that you pay is , for the processor or Socket you had. This does not mean that those who have 2008 R2 Enterprise edition will have to pay extra for migrating to 2012. Microsoft has provided a migration plan for such scenario which is explained below.

Core Based License : Precisely, pay for the horsepower you need. Available for both enterprise and standard editions. Microsoft claims that this change is to address the changing market. This helps you to have a consistent licensing topology for your enterprise, regardless where it is deployed on premises or on cloud. Buying license in this model is simple, just count the core in the server and buy licenses accordingly. As per this model the customer has to buy MINIMUM 4 CORE per Processor even if you do not have that many cores. Ie. Even if you have two processors with dual core you will have to buy 8 CORE license. CAL is not required for CORE based licensing.

SQL Server 2012 licensing fundamentals

SQL Server 2012 is moved to a CORE based licensing model from Socket based or Server+CAL based model (there are exceptions; this statement is TURE only for Enterprise Edition). This move is to be more in line with the emerging technology, like cloud based solutions and to meet the needs of the modern day organizations requirements in a competitive cost. Apart from Licensing model change SQL Server 2012 also introduced new Editions to meet the need of the market.

SQL Server 2012 Editions :In simple term, 2012 has retired Data Center, Workgroup and Web(only available for service providers) and added new BI edition .

Enterprise Edition: Enterprise edition is the high-end edition of SQL Server 2012 with all the features for critical data centers and applications. There is no Data center edition in SQL Server 2012. Enterprise edition is ideal for mission critical OLTP and DW or Business intelligence application. Enterprise Edition includes all the features of 2012 including new BI features. This edition support only CORE Licensing model. But those customer upgrading to 2012 and had Server+CAL in previous version and under SA will have different migration plan which Microsoft licensing team can guide case by case.

Business Intelligence Edition: This edition includes all the standard edition features and supports all the BI new features to build and deploy a manageable and secured BI solution in an enterprise. Support Server+CAL based licensing model, thereby provides a cost effective solution for the customer.

Standard Edition : This edition is for tier 2 application which does not require a high-end high availability or management features. This edition will not have many enterprise level features like Encryption, HA and BI related features. This edition is available in Per Core and Server+CAL licensing model thereby enabling customer to select more suitable model for them.

Developer Edition : This edition is exact replica of Enterprise edition with licensing model change. This edition can be implemented only on Test and Development environments. If the organization have MSDN Subscription it can be installed for free. This comes with Server+CAL licensing model.

Web Edition : Server 2012 Web delivers a secure, cost effective and highly scalable data platform for public web sites. The Web edition is available to third party software service providers only through the service provider licensing agreement (SPLA) . It has only PER CORE based licensing model.

Express Edition: Free version with limited features. Ideal for small desktop applications

Compact Edition: Free embedded database that can be used in mobile applications.

How about Physical Server and Virtual Server licensing model? Are the same? No. The virtual server licensing model is not the same. I am not clear how it works. I was told that, if you have Enterprise Edition core licenses for the physical server and you organization is paying SA, then you can have unlimited virtual servers on that physical machine. But I am not sure about this and I would request you to confirm from a licensing expert.

Summary : All information mentioned above is based on the experience I had in various engagements. Microsoft License Advisor is the tool I generally use to get a quote based on the listed price. But to use that tool , the information provided above may be useful. I would also suggest to get assistance from Microsoft License Specialist and the toll free number is 1-800-426-9400

SQL Server New Version Denali - New Feature Sequence

One of the new featured in SQL Server 2011 – Denali is Sequence. Whoever have worked in Oracle or DB2 can easily connect to this feature and it is in the SQL Server wishlist for long time. While migrating Oracle database to SQL Server this was one area quite bit of effort needed because in previoius versions of SQL Server Sequence was not supported. Though there were workaround to achieve the same feature, there was no One to One mapping for sequence object in SQL Server. Now in SQL Server 2011 Microsoft has comeup with an equivalent. Having said that, there are behavioural changes in SQL Server sequcne from Oracle sequence. The syntex is different, there are limitation and flxibility as well. Let us try to understand more about SQL Server Sequence.

Problem :
In earlier versions of SQL Server, to Generate sequence of numeric values for a Surrogate primary key or otherwise we used to use Identity property on a Numeric Column. The Identity column was tightly coupled with the Table and it had its own advantage and disadvantage. The main issue was if you want to have same Sequence to be shared accross many tables or columns , then we had to go for a workaround. In SuperType subtype kind of relations we used to face this problem of sharing same sequence across multiple tables.

Solution
In SQL Server 2011, Microsoft has added a new Object called Sequence which is more or less similar to Oracle Sequence object.

What is Sequence in SQL Server?
A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Generally it is used to create auto generated Primary key (surrogate key) value.

Create Sequence
As usual, you have TSQL command and GUI. TSQL Syntax is as follows

CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH ]
[ INCREMENT BY ]
[ { MINVALUE [ ] } | { NO MINVALUE } ]
[ { MAXVALUE [ ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ ] } | { NO CACHE } ]
[ ; ]
You can refer this Link for more detail on Syntax.

Eg. Creating a Sequence with all arguments

CREATE SEQUENCE [dbo].Seq_EmployeeID
START WITH 0
INCREMENT BY 2
MINVALUE 0
MAXVALUE 2000
CYCLE
CACHE 20

Eg. Creating Table with Sequence as Default value for PK. This is another way of usage.

CREATE TABLE Audit.ProcessEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
EventCode nvarchar(5) NOT NULL,
Description nvarchar(300) NULL
) ;
GO

Other system Functions and Objects related to Sequence

NEXT VALUE FOR : Generates a sequence number from the specified sequence object. The NEXT VALUE FOR function can be used in stored procedures and triggers. This function also have few limitations you may want to read the details here

Sys.Sequence : Contains a row for each sequence object in a database. For more info refer this link

sp_sequence_get_range: Returns a range of sequence values from a sequence object. The sequence object generates and issues the number of values requested and provides the application with metadata related to the range.

Modify Sequence
ALTER SEQUNCE : Modifies the arguments of an existing sequence object. If the sequence was created with the CACHE option, altering the sequence will recreate the cache. For more info refer this link

Drop a sequence
DROP SEQUENCE : After generating a number, a sequence object has no continuing relationship to the number it generated, so the sequence object can be dropped, even though the number generated is still in use. For more info refer this link

Important Architectural Consideration and Limitations

(a) By default Sequence uses BIGINT Datatype and it take 8 Byte. If the requirement is to store small number, then change the datatype to TinyInt, Smallint or Int accordingly
(b) If the starting value is not defined while creating, then SQL Server set it to the lowest value what that datatype can support. So by default if the datatype is BIGINT the starting number will be (-9,223,372,036,854,775,808)
(c) The Increment cannot be 0. If the increment is a negative value, the sequence object is descending; otherwise, it is ascending. By default the increment is 1
(d) If the requirement is to migrate Oracle Sequence to SQL Server, then select the datatype as Numeric(28,0)
(e) Specify CYCLE/NOCYCLE to define the behavior if t the sequence number reaches the maximum value
(f) SQL Server as of CTP 3 does not support Current Value
(g) Cache option is different from Oracle. In oracle by default database caches 20 Sequence but in SQL Server database engine select the size. If the cache option is enabled without specifying a cache size, the Database Engine will select a size. However, users should not rely upon the selection being consistent. Microsoft might change the method of calculating the cache size without notice.
(h) Sequence objects support ownership chaining. If the sequence object has the same owner as the calling stored procedure, trigger, or table (having a sequence object as a default constraint), no permission check is required on the sequence object. If the sequence object is not owned by the same user as the calling stored procedure, trigger, or table, a permission check is required on the sequence object
(i) Altering a column and Adding Identity is not possible in SQL Server. In such scenario if adding a new column with Identity , dropping the old column and renaming the new column is not possible then may be adding SEQUENCE as a default can be a workaround

How does it differ from Identity Column?

Sequences, unlike identity columns, are not associated with tables. An application refers to a sequence object to receive its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.
Unlike identity columns, whose values cannot be changed, sequence values are not automatically protected after insertion into the table. To prevent sequence values from being changed, use an update trigger on the table to roll back changes.

Performance Comparison of Sequence and Identity

Aaron Bertrand has covered this in detail. Check this Blog
Other than this I did test one more case, Table with Identity and Table with Sequence as Default for PK. In the table where sequence was used, I did not provide value for the PK column so that default gets fired. In this case Identity table insert perform almost 60% Better from a table with Sequence as default. Script to Reproduce the scenario I tested is as follows :-

-- Table with sequence as default

CREATE TABLE [dbo].[SequencePerf](
[EventID] [int] NOT NULL,
[Description] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SequencePerf] ADD DEFAULT (NEXT VALUE FOR [Seq_EventId]) FOR [EventID]
GO


CREATE TABLE [dbo].[IdentityPerf](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[Description] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
) ON [PRIMARY]

GO


-- Run both the insert in single batch and see the cost it take. In my case it was 71:29


Insert [SequencePerf] (Description)
Select top 100000 o.name From sys.objects o,sys.columns

Insert [IdentityPerf] (Description)
Select top 100000 o.name From sys.objects o,sys.columns


Different Scenario of Sequence Usage in SQL Server

Create database [TestDenali]
GO

USE [TestDenali]
GO
CREATE SEQUENCE [dbo].[Seq_EventId]
AS [bigint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
CYCLE
CACHE 200
GO


CREATE TABLE [dbo].[SequenceTest](
[EventID] [int] NOT NULL ,
[Description] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SequenceTest] ADD DEFAULT (NEXT VALUE FOR [Seq_EventId]) FOR [EventID]
GO

-- Not providing EventID value so that Default

INSERT INTO dbo.[SequenceTest](Description) select top 100 Name from sys.objects

-- Inserting bulk number of records , getting sequence in Select
INSERT INTO dbo.[SequenceTest] ([EventID],description)
SELECT top 100 NEXT VALUE FOR Seq_EventId AS SecondUse, Name from sys.objects

-- Insert a single row

INSERT [SequenceTest] ([EventID],description)
VALUES (NEXT VALUE FOR Seq_EventId, 'Test') ;

-- Usage with SELECT INTO
SELECT NEXT VALUE FOR Seq_EventId AS EventID, Description
INTO [SequenceTest_clone]
FROM [SequenceTest] ;
-- Temp table
SELECT NEXT VALUE FOR Seq_EventId AS EventID, Description
INTO [#SequenceTest_clone]
FROM [SequenceTest] ;

-- Creating Temp table with Sequence as Defualt

CREATE TABLE #SequenceTest (
[EventID] [int] NOT NULL DEFAULT (NEXT VALUE FOR Seq_EventId) ,
[Description] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
) ON [PRIMARY]

INSERT #SequenceTest ([EventID],description)
VALUES (NEXT VALUE FOR Seq_EventId, 'Test') ;
INSERT #SequenceTest ( description)
VALUES ( 'Test') ;
Select *from #SequenceTest

-- Creating Table Variable with Sequence as Default


Declare @SequenceTest TABLE (
[EventID] [int] NOT NULL DEFAULT (NEXT VALUE FOR Seq_EventId) ,
[Description] [nvarchar](300) NULL)

INSERT @SequenceTest ([EventID],description)
VALUES (NEXT VALUE FOR Seq_EventId, 'Test') ;
INSERT @SequenceTest ( description)
VALUES ( 'Test') ;

Select *From @SequenceTest
-- Table Valued Parameter
CREATE TYPE [dbo].[SequenceTestTableType] AS TABLE
(
[EventID] INT DEFAULT (NEXT VALUE FOR Seq_EventId), description VARCHAR(128)
)
/*
Msg 11719, Level 15, State 1, Line 3
NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views,
user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.
*/

Create view vwTestSequence
as
SELECT top 10000 NEXT VALUE FOR Seq_EventId AS SecondUse,description FROM dbo.Sequencetest

/*
Msg 11719, Level 15, State 1, Procedure vwTestSequence, Line 4
NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views,
user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.

*/

-- Capture the Inserted Sequencenumber using OUTPUT. In SuperType-Subtype scenario may be usefule
Declare @tab table (Eventid int)
Insert [SequencePerf] (Description) OUTPUT inserted.[EventID] Into @tab
Select top 100000 o.name From sys.objects o,sys.columns
SELECT *From @tab


Reference

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/11/sql-server-11-denali-using-sequence.aspx
http://blogs.msdn.com/b/ssma/archive/2011/07/12/converting-oracle-sequence-using-ssma-for-oracle-v5-1.aspx

SQL Server New Version - Denali - Step by Step Installation

SQL Server 2011 – Denali CTP 3 is recently released and as usual there are many new features which will enhance SQL Server footprint in Enterprise Database arena. The main advantage with Microsoft what I see is, they have all three layer in their control ie. Operating system , Database and Programming languages. They can leverage this freedom and integrate all these three layer well and come up with good end products which of course have a limitation of Only on Windows platform. This is the reason why we have great tool like Management Studio which is powered by Visual Studio and makes database administrators /developers/users life easy.

The major new configuration change in installation process is Distributed Reply Component Configuration. This component enable Realtime stress Workload testing against your database server. If you don’t want to install this option while selecting services don’t opt “Distributor Replay Controller” and “Distributed Replay Controller Client”

Another new service you can see while installation is “Data Quality Service”. This is a new feature added in Denali for knowledge-driven data cleansing solution.
You may want to see the FAQ link for DQS…

For any installation you must be aware of the Hardware and software requirement. So for Denali. Here is the link for Pre-requisite

Once you ensure that you have a system which meets the mentioned hardware and software requirement , here is the next steps

SQL Server Code-Named “Denali” Evaluation Edition is supported on the Windows Vista SP2, Windows Server 2008 SP2, Windows 2008 R2 SP1, and Windows 7 SP1 operating systems.

Pre-Installation Steps
• Refer the above mentioned sites and ensured that your system meet all the requirements
• Ensure that you have planned your Instance name if it is a named Instance
• Ensure that you have planned all the Data Directory. Ie. Where to keep System Databases, TempDB, and User Databases.(if not you can go by Default but it is not generally recommended).
• Ensure that you have planned Startup Account for all the services. (if not you can go by Default but it is not generally recommended.)
• Ensure that you have a list of features that you wants to install. Like if you are not going to use Filestream no need to configure that during installation

My System Configuration
• Operating System : Windows Vista SP2
• Existing SQL Server Instance
o Default Instance : SQL Server 2008 SP 1
o Named Instance 1: SQL Server 2008 RTM
o Named Instance 2 : SQL Server 2008 R2 RTM
Named Instance 3 : SQL Server 2005 Express Edition SP3

Step 1 : Download the Binary from http http://www.microsoft.com/sqlserver/en/us/future-editions.aspx

Step 2 : Extract the downloaded binary and start installation as shown below

Figure 1

Figure 2



Figure 3



Figure 4



Figure 5



Figure 6



Figure 7



Figure 8


Figure 9


Figure 10



Figure 11


Figure 12


Figure 13


Figure 14


Figure 15


Figure 16



Figure 17



Figure 18



Figure 19




Figure 20



Figure 21




Figure 22



Figure 23



Summary : I had SQL Server 2005 Express Edition, SQL Server 2008 Developer Edition, SQL Server 2008 R2 Evaluation Edition on my Machine and all of these instance in the first look working fine. Not found any abnormal behaviour so far. Hope all these versions can co-exists. My SQL Server 2011 CP3 is installed and good to go

FAQ : What is Partition Scheme in SQL Server?

Once you have created the Partition Function then the next step is to create Partition Scheme. As already mentioned, One partition function may have one or more Partition Scheme. . The partition scheme defines how any resulting partitions will be stored on filegroups. You list the filegroups for a partition scheme in the same order that you want to have them map to the partitions defined by the partition function. Creating a partition scheme assumes that you have already created the filegroups. You can mention ALL TO keyword to create all the partition to a single filegroup.

Eg. From BOL
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg);

Edit : Should Filegroup single file or multiple files considered as out of scope in this context. But selecting single filegroup VS multiple filegroup and Single files per filegroup VS multiple files also a significant decision to be made in terms of performance.

FAQ : What is Partition Function in SQL Server?

Partition functions are special kind of objects (of course created by user) created in a database which can be used only by partition scheme. Partition function contains a data type , that must be the same data type as the partition column chosen in the table. Partition Function as such not directly related to any table.

NOTE: Even though Partition Function to Partition scheme is One to Many, in sliding window scenario moving data between in and out of partition is a common requirement, which require Partition function modification. So practically it makes each Partition table should have exclusive partition function and scheme.
Partition functions are not user defined functions.

Here are few of facts to be known about partition functions• Partition functions are not listed as database objects in the sys.all_objects or sys.objects system tables; instead, you can find them listed in sys.partition_functions.
• Partition functions are not contained by a database schema.
• Special commands must be used for creating, altering, and dropping a partition function:
o CREATE PARTITION FUNCTION
o ALTER PARTITION FUNCTION
o DROP PARTITION FUNCTION
• Partition functions can be invoked interactively in Transact-SQL by using the $PARTITION function.

Partition Functions System Objects

• sys.partition_functions : Contains a row for each partition function.
• sys.partition_range_values : Contains a row for each range boundary value of a partition function of type R.
• sys.partition_parameters : Contains a row for each parameter of a partition function.
• $PARTITION. :Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.
Eg. SELECT $PARTITION.RangePF1 (10) ;