Prince2 Project Management Simplified Revision

PRINCE2: (Project Management IN Controled Environment)

Project: A PROJECT is a temporary organization that is created for the purpose of delivering one or more business products according to an agreed Business Case.

Key features of PRINCE2:

•Focus on business justification
•Defined organisation structure for the project management team
•Product-based planning approach
•Emphasis on dividing the project into manageable and controllable stages
•Flexibility that can be applied at a level appropriate to the project.

6 Aspects of PRINCE2


4 Elements of Prince2

•7 Principles
•7 Themes
•7 Processes
•Tailoring PRINCE2 to a project environment.

7 Principles of PRINCE2

1.Continued Business Justification: (why to start, why to continue)
2.LEARN from experience.(learn from previous, lessons are recorded)
3.Define Role and Responsibilities (stakeholders)
4.Managed by STAGE
5.Managed by EXCEPTION
6.Focus on PRODUCT
7.Tailor to suit project environment.

7 Themes of PRINCE2

1.Business case (WHY)
2.Organization (WHO)
3.Quality (WHAT/End product quality)
4.Plans (How? How much? When?)
5.Risk (What if?)
6.Change (What is the impact)
7.Progress (Where are we now in Project? where are we going next?)

7 Processes of PRINCE2

1.Starting up a project process (SU)
2.Directing a Project (DP)
3.Initiating a Project (IP)
4.Controlling a Stage (CS)
5.Managing Product Delivery (PD)
6.Managing Stage Boundaries (SB)
7.Closing Project (CP)

Key benefits of PRINCE2

•Proven best practice
•Applied to any type of projects
•Promoting effective communication (same language) and control
•Explicit recognition of RESPONSIBILITIES
•Managed by exception – economical/wise use of management time
•PRINCE2 is like a diagnostic tool.

Stakeholders in Project

1.Business (People who endorse the project)
2.Users (People who will use the output of the project)
3.Suppliers (provide resources both INTERNAL or EXTERNAL or both)

Project Logs and Registers used:

1.Risk Register
2.Quality Register
3.Issue Register
4.Configuration Item Records
5.Lesson Log
6.Daily Log


•Checkpoint Report: team managers to the project manager
•Highlight Report: project manager to the project board
•End Stage Report: Project Manager to the Board
•End Project Report – Project Manager to Board
•Lessons Report – Project Manager

For more info refer to website here.

In Memory Optimized Tables SQL 2014

    In Memory Optimized Tables SQL 2014

What are Memory Optimized Tables in SQL Server?
Memory Optimized tables are newly introduced in SQL Server and they are fully ACID compliant data storage structures, which stores the data mainly within the system memory(RAM), allowing users to access data very very quickly compared to the stored File system stored Table.

The transactional consistency is maintained in Memory Optimized Tables in SQL Server 2014 by:

In-Memory Row Versioning:
Memory Optimized tables maintains various versions of the same row dispersed over the in-memory data structure of the table which allows the reading and writing of the rows concurrently without transactional inconsistency issues. It is equivalent to SNAPSHOT ISOLATION LEVEL where row-versioning handles conflicts by throwing an error to the application which expects the application to implement some sort of retry attempts logic for failed transactions.

Data durability:
Transaction Log: Logging to the Transaction Log is a disk-based operation, and so this might become a bottleneck in a highly transactional In-Memory environment.

Checkpoint and Delta files:
These Files are placed on a special FileGroup for Memory-Optimized storage which are written to sequentially and are on SQL Server start-up to load in the Memory-Optimized tables in a database to Memory.

To create Memory Optimized tables:

Step 1:

USE [master]
ALTER DATABASE [In-Mem] ADD FILE ( NAME = N'In-Mem_mod1', FILENAME = N'C:\In-Mem\In-Mem_mod1' ) TO FILEGROUP [mem]

Step 2

USE [In-Mem]

CREATE TABLE dbo.In-MemTable (
Id int not null primary key nonclustered hash with (bucket_count=10000),
AltId int not null index ix_naturalId nonclustered hash with (bucket_count=5000),
Name nvarchar(100) null

I will add more options for these tables shortly.

SQL Server 2014 Compared to older versions


SQL Server 2014 Compared to older versions



Features Feature Description SQL Server 2014 SQL Server 2012 SQL Server 2008ÿR2 SQL Server 2008
Performance In-memory OLTP* Yes No No No
In-memory ColumnStore* Yes Yes No No
Buffer pool extension to SSD Yes No No No
Resource Governor Yes Yes Yes Yes
Availability AlwaysOn* Yes Yes No No
Enhanced virtualization support and live migration Yes Yes Yes No
Security Transparent data encryption* Yes Yes Yes Yes
Backup encryption support Yes No No No
Fine-grained auditing Yes Yes Yes Yes
Separation of duties Yes Yes No No
Cloud-readiness Backup to Microsoft Azure Yes Yes No No
Disaster recovery to Microsoft Azure Yes No No No
Optimized VM images in Microsoft Azure gallery Yes Yes No No
Management & programmability Distributed replay Yes Yes No No
Policy-based management Yes Yes Yes Yes
Enhanced programmability Yes Yes Yes Yes
BI & analytics PowerPivot for Excel Yes Yes Yes No
Integration services managed as a server Yes Yes No No
Hadoop connector via Apache Sqoop Yes Yes Yes Yes
Tabular BI semantic model* Yes Yes Yes No
Master data services* Yes Yes Yes No
Data quality services* Yes Yes No No

SQL Server 2014 released..New features

SQL Server 2014 released…

New features will be listed here which are really fantastic….

Note: Database Compatibility Level of 90 compatibility level is not valid in SQL Server 2014.

I will also post some examples of how to use these features in coming days…
Database Engine Feature Enhancements  For more info click here for microsoft site
Memory-Optimized Tables: In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine.

Buffer Pool Extension: The Buffer Pool Extension provides the seamless integration of solid-state drives (SSD) as a nonvolatile random access memory (NvRAM) extension to the Database Engine buffer pool to significantly improve I/O throughput.

Incremental Statistics: CREATE STATISTICS and related statistic statements now permits per partition statistics to be created by using the INCREMENTAL option. Related statements allow or report incremental statistics. Affected syntax includes UPDATE STATISTICS, sp_createstats, CREATE INDEX, ALTER INDEX, ALTER DATABASE SET options, DATABASEPROPERTYEX, sys.databases, and sys.stats.

SQL Server in Windows Azure: SQL Server Data Files in Windows Azure enables native support for SQL Server database files stored as Windows Azure Blobs. Also Host a SQL Server Database in a Windows Azure Virtual Machine

Backup and Restore Enhancements: 

SQL Server Backup to URL: SQL Server Backup to URL was introduced in SQL Server 2012 (SP1 CU2) supported only by TSQL, PowerShell and SMO. In SQL Server 2014 you can use SQL Server Management Studio to backup to or restore from Windows Azure Blob storage service. The new option is available both for the Backup task, and maintenance plans.
SQL Server Managed Backup to Windows Azure: Built on SQL Server Backup to URL, SQL Server Managed Backup to Windows Azure is a service that SQL Server provides to manage and schedule database and log backups. In this release only backup to Windows Azure storage is supported.
Encryption for Backups: 
You can now choose to encrypt the backup file during a backup operation. It supports several encryption algorithms including AES 128, AES 192, AES 256, and Triple DES. You must use either a certificate or an asymmetric key to perform encryption during backup. For more information, see Backup Encryption.

New Design for Cardinality Estimation: The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance.
Delayed Durability:  SQL Server 2014 introduces the ability to reduce latency by designating some or all transactions as delayed durable. A delayed durable transaction returns control to the client before the transaction log record is written to disk. Please be aware of down side of using this feature like you may lose some transactions where they are not written to disk yet. Durability can be controlled at the database level, COMMIT level, or ATOMIC block level.

AlwaysOn Enhancements: 
An Add Azure Replica Wizard simplifies creating hybrid solutions for AlwaysOn availability groups.

The maximum number of secondary replicas is increased from 4 to 8.
When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads.
Failover cluster instances (FCIs) can now use Cluster Shared Volumes (CSVs) as cluster shared disks.

A new system function, sys.fn_hadr_is_primary_replica, and a new DMV, sys.dm_io_cluster_valid_path_names, is available.

Partition Switching and Indexing: 
The individual partitions of partitioned tables can now be rebuilt.
Managing the Lock Priority of Online Operations
The ONLINE = ON option now contains a WAIT_AT_LOW_PRIORITY option which permits you to specify how long the rebuild process should wait for the necessary locks. The WAIT_AT_LOW_PRIORITY option also allows you to configure the termination of blocking processes related to that rebuild statement.
Columnstore Indexes: Use a clustered columnstore index to improve data compression and query performance for data warehousing workloads that primarily perform bulk loads and read-only queries. Since the clustered columnstore index is updateable, the workload can perform many insert, update, and delete operations.
SHOWPLAN:  displays information about columnstore indexes. The EstimatedExecutionMode and ActualExecutionMode properties have two possible values: Batch or Row. The Storage property has two possible values: RowStore and ColumnStore.
Archival data compression : ALTER INDEX … REBUILD has a new COLUMNSTORE_ARCHIVE data compression option that further compresses the specified partitions of a columnstore index. Use this for archival, or for other situations that require a smaller data storage size and can afford more time for storage and retrieval.

Resource Governor Enhancements for Physical IO Control:The Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within a resource pool. In SQL Server 2014, you can use the new MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings to control the physical IOs issued for user threads for a given resource pool.

Online Index Operation Event Class:The progress report for the online index operation event class now has two new data columns: PartitionId and PartitionNumber.

Transact-SQL Enhancements: 

Inline specification of CLUSTERED and NONCLUSTERED. Inline specification of CLUSTERED and NONCLUSTERED indexes is now allowed for disk-based tables. Creating a table with inline indexes is equivalent to issuing a create table followed by corresponding CREATE INDEX statements. Included columns and filter conditions are not supported with inline indexes.

The SELECT … INTO statement is improved and can now operate in parallel. The database compatibility level must be at least 110.

Also Transact-SQL Enhancements for In-Memory OLTP

System View Enhancements include below
System View Enhancements for In-Memory OLTP

Security Enhancements
A new server level permission. Grant CONNECT ANY DATABASE to a login that must connect to all databases that currently exist and to any new databases that might be created in future. Does not grant any permission in any database beyond connect. Combine with SELECT ALL USER SECURABLES or VIEW SERVER STATE to allow an auditing process to view all data or all database states on the instance of SQL Server.
A new server level permission. When granted, allows a middle-tier process to impersonate the account of clients connecting to it, as it connects to databases. When denied, a high privileged login can be blocked from impersonating other logins. For example, a login with CONTROL SERVER permission can be blocked from impersonating other logins.
A new server level permission. When granted, a login such as an auditor can view data in all databases that the user can connect to.

Also I will post comparison of  new features introduced in 2014 vs 2012 vs 2008 R2 in another post.

CTE vs Temp Table vs Temp variables

Temp tables: they are just like any normal tables except that they are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that it calls. Just like normal tables and have primary keys, constraints, indexes and column statistics which are kept for the table which makes query execution really fast in most cases.

Temp tables have space assigned to them in the TempDB database and are generally accessed only from memory, unless the server is under memory pressure or the amount of data in the table is large.

Table Variables: These tables behave very much like other variables in their scoping rules. They are created when they are declared and are dropped when they go out of scope. They cannot be explicitly dropped. Like with temp tables, table variables also reside in TempDB. They are also generally accessed only from memory, unless the server is under memory pressure or the amount of data in the table is large.

Table variables can have a primary key, but no indexes can be created on them. They don’t have any statistics maintained on the columns. This makes table variables less optimal for large numbers of rows, as the optimizer has no way of knowing the number of rows in the table variable.

CTE: CTE’s are more like temporary views than anything else. When you look at the execution plan, you’ll see that they are substituted/inlined into the query but not materialized and stored anywhere. With the exception of recursion, they’re more to make queries simpler to write than faster to run.

Hope this helps…

SQL Server DBA Home