Task: to show how to create universal codebooks and hierarchies for Operational Data Storage (3NF) in DW solutions
a) universal codebooks:
CREATE TABLE [dbo].[CodeBook]( [AutoID] [int] IDENTITY(1,1) NOT NULL, [CodeBookTypeAutoID] [int] NULL, [CodeBookID] [nvarchar](50) NULL, [CodeBookText] [nvarchar](50) NULL, [CodeBookTextEnglish] [nvarchar](50) NULL, [SourceSystemAutoID] [int] NULL, [ExtractionDate] [int] NULL, CONSTRAINT [PK_CodeBook] PRIMARY KEY CLUSTERED ( [AutoID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[CodeBookType]( [AutoID] [int] IDENTITY(1,1) NOT NULL, [CodeBookTypeID] [nvarchar](50) NULL, [CodeBookTypeText] [nvarchar](50) NULL, [CodeBookTypeTextEnglish] [nvarchar](50) NULL, [SourceSystemAutoID] [int] NULL, [ExtractionDate] [int] NULL, CONSTRAINT [PK_CodeBookType] PRIMARY KEY CLUSTERED ( [AutoID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[CodeBook2Contract]( [CodeBookTypeAutoID] [int] NULL, [CodeBookAutoID] [int] NULL, [ContractAutoID] [int] NULL, [ValidFrom] [int] NULL, [ValidTo] [int] NULL, [SourceSystemAutoID] [int] NULL, [ExtractionDate] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Contract]( [AutoID] [int] IDENTITY(1,1) NOT NULL, [ContractID] [nvarchar](50) NULL, [ValidFrom] [int] NULL, [ValidTo] [int] NULL, [SourceSystemAutoID] [int] NULL, [ExtractionDate] [int] NULL, CONSTRAINT [PK_Contract] PRIMARY KEY CLUSTERED ( [AutoID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CodeBook2Contract] WITH NOCHECK ADD CONSTRAINT [FK_CodeBook2Contract_CodeBook] FOREIGN KEY([CodeBookAutoID]) REFERENCES [dbo].[CodeBook] ([AutoID]) GO ALTER TABLE [dbo].[CodeBook2Contract] CHECK CONSTRAINT [FK_CodeBook2Contract_CodeBook] GO ALTER TABLE [dbo].[CodeBook2Contract] WITH NOCHECK ADD CONSTRAINT [FK_CodeBook2Contract_CodeBookType] FOREIGN KEY([CodeBookTypeAutoID]) REFERENCES [dbo].[CodeBookType] ([AutoID]) GO ALTER TABLE [dbo].[CodeBook2Contract] CHECK CONSTRAINT [FK_CodeBook2Contract_CodeBookType] GO ALTER TABLE [dbo].[CodeBook2Contract] WITH NOCHECK ADD CONSTRAINT [FK_CodeBook2Contract_Contract] FOREIGN KEY([ContractAutoID]) REFERENCES [dbo].[Contract] ([AutoID]) GO ALTER TABLE [dbo].[CodeBook2Contract] CHECK CONSTRAINT [FK_CodeBook2Contract_Contract] GO ALTER TABLE [dbo].[CodeBook] WITH NOCHECK ADD CONSTRAINT [FK_CodeBook_CodeBookType] FOREIGN KEY([CodeBookTypeAutoID]) REFERENCES [dbo].[CodeBookType] ([AutoID]) GO ALTER TABLE [dbo].[CodeBook] CHECK CONSTRAINT [FK_CodeBook_CodeBookType] GO SET IDENTITY_INSERT [dbo].[CodeBookType] ON GO INSERT [dbo].[CodeBookType] ([AutoID], [CodeBookTypeID], [CodeBookTypeText], [CodeBookTypeTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'CS', NULL, N'Contract status', NULL, NULL) GO SET IDENTITY_INSERT [dbo].[CodeBookType] OFF GO SET IDENTITY_INSERT [dbo].[CodeBook] ON GO INSERT [dbo].[CodeBook] ([AutoID], [CodeBookTypeAutoID], [CodeBookID], [CodeBookText], [CodeBookTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, 1, N'CSO', NULL, N'Open contract', NULL, NULL) GO SET IDENTITY_INSERT [dbo].[CodeBook] OFF GO SET IDENTITY_INSERT [dbo].[Contract] ON GO INSERT [dbo].[Contract] ([AutoID], [ContractID], [ValidFrom], [ValidTo], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'1', NULL, NULL, NULL, NULL) GO SET IDENTITY_INSERT [dbo].[Contract] OFF GO INSERT [dbo].[CodeBook2Contract] ([CodeBookTypeAutoID], [CodeBookAutoID], [ContractAutoID], [ValidFrom], [ValidTo], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, 1, 1, NULL, NULL, NULL, NULL) GO
b) universal hierarchies:
CREATE TABLE [dbo].[HierarchyType]( [AutoID] [int] IDENTITY(1,1) NOT NULL, [HierarchyTypeID] [nvarchar](50) NULL, [HierarchyTypeText] [nvarchar](50) NULL, [HierarchyTypeTextEnglish] [nvarchar](50) NULL, [SourceSystemAutoID] [int] NULL, [ExtractionDate] [int] NULL, CONSTRAINT [PK_HierarchyType] PRIMARY KEY CLUSTERED ( [AutoID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Hierarchy]( [AutoID] [int] IDENTITY(1,1) NOT NULL, [HierarchyID] [nvarchar](50) NULL, [HierarchyTypeAutoID] [int] NULL, [HierarchyText] [nvarchar](50) NULL, [HierarchyTextEnglish] [nvarchar](50) NULL, [SourceSystemAutoID] [int] NULL, [ExtractionDate] [int] NULL, CONSTRAINT [PK_Hierarchy] PRIMARY KEY CLUSTERED ( [AutoID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[HierarchyLinkType]( [AutoID] [int] IDENTITY(1,1) NOT NULL, [HierarchyLinkTypeID] [nvarchar](50) NULL, [HierarchyTypeSourceAutoID] [int] NULL, [HierarchyTypeDestinationAutoID] [int] NULL, [HierarchyLinkTypeText] [nvarchar](50) NULL, [HierarchyLinkTypeTextEnglish] [nvarchar](50) NULL, [ValidFrom] [int] NULL, [ValidTo] [int] NULL, [SourceSystemAutoID] [int] NULL, [ExtractionDate] [int] NULL, CONSTRAINT [PK_HierarchyLinkType] PRIMARY KEY CLUSTERED ( [AutoID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[HierarchyLink]( [AutoID] [int] IDENTITY(1,1) NOT NULL, [HierarchyLinkID] [nvarchar](50) NULL, [HierarchyLinkTypeAutoID] [int] NULL, [HierarchySourceAutoID] [int] NULL, [HierarchyDestinationAutoID] [int] NULL, [ValidFrom] [int] NULL, [ValidTo] [int] NULL, [SourceSystemAutoID] [int] NULL, [ExtractionDate] [int] NULL, CONSTRAINT [PK_HierarchyLink] PRIMARY KEY CLUSTERED ( [AutoID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Hierarchy] WITH NOCHECK ADD CONSTRAINT [FK_Hierarchy_HierarchyType] FOREIGN KEY([HierarchyTypeAutoID]) REFERENCES [dbo].[HierarchyType] ([AutoID]) GO ALTER TABLE [dbo].[Hierarchy] CHECK CONSTRAINT [FK_Hierarchy_HierarchyType] GO ALTER TABLE [dbo].[HierarchyLinkType] WITH NOCHECK ADD CONSTRAINT [FK_HierarchyLinkType_HierarchyType] FOREIGN KEY([HierarchyTypeSourceAutoID]) REFERENCES [dbo].[HierarchyType] ([AutoID]) GO ALTER TABLE [dbo].[HierarchyLinkType] CHECK CONSTRAINT [FK_HierarchyLinkType_HierarchyType] GO ALTER TABLE [dbo].[HierarchyLink] WITH NOCHECK ADD CONSTRAINT [FK_HierarchyLink_Hierarchy_S] FOREIGN KEY([HierarchySourceAutoID]) REFERENCES [dbo].[Hierarchy] ([AutoID]) GO ALTER TABLE [dbo].[HierarchyLink] CHECK CONSTRAINT [FK_HierarchyLink_Hierarchy_S] GO ALTER TABLE [dbo].[HierarchyLink] WITH NOCHECK ADD CONSTRAINT [FK_HierarchyLink_Hierarchy_D] FOREIGN KEY([HierarchyDestinationAutoID]) REFERENCES [dbo].[Hierarchy] ([AutoID]) GO ALTER TABLE [dbo].[HierarchyLink] CHECK CONSTRAINT [FK_HierarchyLink_Hierarchy_D] GO ALTER TABLE [dbo].[HierarchyLink] WITH NOCHECK ADD CONSTRAINT [FK_HierarchyLink_HierarchyLinkType] FOREIGN KEY([HierarchyLinkTypeAutoID]) REFERENCES [dbo].[HierarchyLinkType] ([AutoID]) GO ALTER TABLE [dbo].[HierarchyLink] CHECK CONSTRAINT [FK_HierarchyLink_HierarchyLinkType] GO SET IDENTITY_INSERT [dbo].[HierarchyType] ON GO INSERT [dbo].[HierarchyType] ([AutoID], [HierarchyTypeID], [HierarchyTypeText], [HierarchyTypeTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'ORG_L1', NULL, N'Level1', NULL, NULL) GO INSERT [dbo].[HierarchyType] ([AutoID], [HierarchyTypeID], [HierarchyTypeText], [HierarchyTypeTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (2, N'ORG_L2', NULL, N'Level2', NULL, NULL) GO SET IDENTITY_INSERT [dbo].[HierarchyType] OFF GO SET IDENTITY_INSERT [dbo].[Hierarchy] ON GO INSERT [dbo].[Hierarchy] ([AutoID], [HierarchyID], [HierarchyTypeAutoID], [HierarchyText], [HierarchyTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'ORG_L1_1', 1, NULL, N'ORG Level1 A', NULL, NULL) GO INSERT [dbo].[Hierarchy] ([AutoID], [HierarchyID], [HierarchyTypeAutoID], [HierarchyText], [HierarchyTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (2, N'ORG_L2_1', 2, NULL, N'ORG Level2 A', NULL, NULL) GO SET IDENTITY_INSERT [dbo].[Hierarchy] OFF GO SET IDENTITY_INSERT [dbo].[HierarchyLinkType] ON GO INSERT [dbo].[HierarchyLinkType] ([AutoID], [HierarchyLinkTypeID], [HierarchyTypeSourceAutoID], [HierarchyTypeDestinationAutoID], [HierarchyLinkTypeText], [HierarchyLinkTypeTextEnglish], [ValidFrom], [ValidTo], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'ORG_L1_L2', 1, 2, NULL, N'ORG Level1 - Level2', NULL, NULL, NULL, NULL) GO SET IDENTITY_INSERT [dbo].[HierarchyLinkType] OFF GO SET IDENTITY_INSERT [dbo].[HierarchyLink] ON GO INSERT [dbo].[HierarchyLink] ([AutoID], [HierarchyLinkID], [HierarchyLinkTypeAutoID], [HierarchySourceAutoID], [HierarchyDestinationAutoID], [ValidFrom], [ValidTo], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'ORG_L1_1-ORG_L2_1', 1, 1, 2, NULL, NULL, NULL, NULL) GO SET IDENTITY_INSERT [dbo].[HierarchyLink] OFF GO
Conclusion:
a) universal codebooks: one set of tables (3x codebook + 1 per entity) can contain more codebooks’ records (e.g. Contract status, Contract payment frequency, Client type etc.)
b) universal hierarchies: one set of tables (4 “common” tables) can contain more hierarchies’ records (e.g. Organisational structure, Business structure, Sales channels etc.) By using entities “HierarchyLinkType and HierarchyLink” is possible to create combinations between “source” and “destination” (bi-directional connections.)
Source code:
Additional references:
- Displaying Sorted Hierarchies (SQL Spackle) by Jeff Moden
- Hierarchical Data (SQL Server) by MSDN
- Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets by Jeff Moden
- Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations by Jeff Moden
SharePoint BI and mobile BI on SQL Server platform
Posted: August 17, 2013 in MSSQLTags: Mobile BI, SharePoint BI
Task: to bring a brief overview based on my own experience acquired during the last few days in our company about building mobile BI (Business Intelligence) solutions related to SQL Server. This concept will be demonstrated mainly by means of a virtualized three-server testing environment for SharePoint/mobile BI platform via Hyper-V as a virtualization technology by Microsoft
At the time of writing this blog post in the world of Microsoft, there doesn’t exist any comprehensive cross-platform for the mobile BI (Apple, Android, Microsoft) environment for easy-building mobile BI solutions, including tablets and smartphones as well (iPad/iPhone, Android and Windows 8/Phones devices). The future Microsoft sees itself in the project called Mobile Helix Link, Power Map (codename “GeoFlow”) + Power Query (codename “Data Explorer”) for Excel and mainly in the technology “InfoNav” (codename) as a feature of Power BI (note: Power BI – self-service “power” tools: Power Pivot, Power View, Power Query and Power Map). Unfortunately, nowadays we can build our mobile BI solutions based on SQL Server as a data source in the following (different) ways:
- IIS (Internet Information Services; as a very low cost solution): SSRS (SQL Server Reporting Services; note: limited use for the Apple platform, details are described below)
- SharePoint Server: Excel Services (easier solutions), PerformancePoint Services (much more complex solutions)
- OWA Server (Office Web Apps): Excel
- 3rd party developer tools/platforms or ready-to-use solutions
Below, there is described a hardware and software configuration of a three-server testing environment for building SharePoint BI in general and mobile BI solutions based on this Microsoft platform.
Three-server SharePoint testing environment:
- Hardware configuration of a three-server SharePoint 2013 testing environment:
- OS: Windows Server 2012 Standard Edition
- CPU: 1x
- Memory: 1GB RAM
- HDD: 32GB
- OS: Windows Server 2012 Standard Edition
- CPU: 4x
- Memory: 8GB RAM
- HDD: 1x 80GB (OS and SQL Server installation) and 1x 40GB (database installation, SSD)
- OS: Windows Server 2012 Standard Edition
- CPU: 4x
- Memory: 12GB RAM
- HDD: 1x 80GB
- Software installation and configuration:
- Secure Store Service: Configure the Secure Store Service in SharePoint 2013
- Plan the Secure Store Service in SharePoint Server 2013
- note: Plan for Kerberos authentication in SharePoint 2013
- Excel Services: Configure Excel Services in SharePoint Server 2013
- Data authentication for Excel Services in SharePoint Server 2013
- Use EffectiveUserName with Excel Services (SharePoint Server 2013)
- PerformancePoint Services: Configure PerformancePoint Services (SharePoint Server 2013)
- note: ERROR – The data source provider for data sources of type ‘ADOMD.NET’ is not registered. Please contact an administrator. PerformancePoint Services error code 10115.
- note: SOLUTION – on SQL Server you need to install SQL Server 2008 R2 ADOMD.NET (i.e. to downgrade ADOMD.NET from 2012 to 2008 R2)
- PowerPivot (for SharePoint): PowerPivot for SharePoint (SSAS)
- Reporting Services (integrated mode): Install Reporting Services SharePoint Mode for SharePoint 2013
- View and Explore Native Mode Reports Using SharePoint Web Parts (SSRS)
- note: ERROR – Failed to extract the cab file in the solution.
- note: SOLUTION – instead of installing the version 2012 you must install the cab file for SQL Server 2008 R2 that is located at the C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Reporting Services\SharePoint\ (or directly for download here: RSWebParts.cab)
- PowerView for OLAP (multidimensional mode): Power View for Multidimensional Models
1. Windows Domain Controller:
2. SQL Server 2012 Enterprise Edition (or Business Intelligence Edition):
3. SharePoint 2013 Enterprise Edition:
1. Windows Domain Controller: Step-by-Step Guide for Setting Up Windows Server 2012 Domain Controller
2. SQL Server 2012 Enterprise Edition: Installation for SQL Server 2012
3. SharePoint 2013 Enterprise Edition: SharePoint 2013: Installation Step by Step
In a few bullets below, there are described the results of my investigation about some possibilities for mobile BI solutions based on the SQL Server platform.
Mobile BI (iPad/iPhone, Android, Windows 8/Phone devices) platform:
- SSRS:
- iPad: Safari/Chrome/Opera – NO, Mercury (Chrome and Firefox alternative) – NO, FF/IE – not supported
- either to use a native application from app store, e.g.:
- Mobi Reports Pro by Mobi Weave, Inc. (note: very good solution – I recommend it’s use)
- SSRS Report Viewer Pro by By Ororo a.s. (note: iPad only)
- or to use Remote Desktop Services/Terminal Services (Windows Server) instead of native application (note: in addition one CAL per user)
- Android: FF – OK, Chrome/Opera – NO, Safari/IE – not supported
- Windows 8: IE – OK, alternative – FF, Chrome/Opera – NO, Safari – not supported on tablet?
- Excel Services:
- iPad: Safari – OK (note: slicers – OK), alternatives – Chrome/Mercury (Chrome and Firefox alternative), Opera – NO, FF/IE – not supported
- Android: FF – OK (note: slicers – NO), Chrome/Opera/(Built-in Browser/Dolphin) – NO, Safari/IE – not supported
- Windows 8: IE – OK, alternatives – FF/Chrome/Opera, Safari – not supported
- PerformancePoint Services (Dashboards):
- OLAP/Excel Services:
- iPad: Safari – OK (note: slicers – OK), alternatives – Chrome/Mercury (Chrome and Firefox alternative), Opera – NO, FF/IE – not supported
- Android: FF – OK (note: slicers – NO), Chrome/Opera/(Built-in Browser/Dolphin) – NO, Safari/IE – not supported
- Windows 8: IE – OK, alternative – FF, Chrome/Opera – OK (note: zoom – NO), Safari – not supported
- SSRS:
- iPad: Safari/Chrome/Opera – NO, Mercury (Chrome and Firefox alternative) – NO, FF/IE – not supported
- Android: FF – OK, Chrome/Opera/(Built-in Browser/Dolphin) – NO, Safari/IE – not supported
- Windows 8: IE – OK, alternative – FF, Chrome/Opera – NO, Safari – not supported on tablet?
- PowerPivot/PowerView: the output is rendered in Silverlight
- iPad: SL – not supported on iOS (note: SL is available only for Windows and Mackintosh)
- Android: SL – not supported
- Windows 8: IE – OK, alternatives – FF/Chrome/Opera (note: scroll – NO), Safari – not supported
- Deploy Office Web Apps Server
- Configure SharePoint 2013 to use Office Web Apps
- Build cross-platform iOS, Android, Mac and Windows apps with C# and .NET by Xamarin
- UIFramework for .NET by ComponentArt
- Data Visualization for Visual Studio by ComponentArt
- Datazen – Mobile BI for Windows 8, iPad/iPhone and Android by ComponentArt
- Tableau Server by Tableau Software (note: I recommend it’s use)
- iPad: web browsers – OK and native free app – OK
- Android: web browsers – OK and native free app – OK
- Windows 8: web browsers – OK (note: a native app isn’t available)
- Mobi Reports Pro – BI for SSRS on iPad and iPhone by Mobi Weave
- Mobi Office – Mobile Content Management by Mobi Weave
1. IIS:
2. SharePoint 2013:
3. OWA 2013 (Office Web Apps):
4.a) 3rd party developer tools/platforms:
4.b) 3rd party ready-to-use solutions:
Testing devices:
- iPad: 6.1.3 (note: iPhone – not tested)
- Android: Samsung Tab 10.1 with OS: 4.0.3 (note: Android on a phone – not tested)
- Windows 8: ThinkPad Tablet (note: Windows Phone – not tested)
Recommendations:
- iPad: Mercury (note: for the sake of drill down in zoomed mode – PerformancePoint Services), alternatives – Chrome/Safari + Mobi Reports Pro for SSRS
- Android: FF (note: without slicers)
- Windows 8: IE (note: alternative – FF)
- note: cross-platform independence is possible to achieve by using Remote Desktop Services/Terminal Services (Windows Server; in addition one CAL per user)
Figure 1: Dashboard (PerformancePoint Services)
Figure 1: Dashboard (Tableau Server)
Conclusion: nowadays (at the time of writing the blog post), as a universal cross-platform solution for building mobile BI applications directly with Microsoft tools/frameworks, the use of Excel Services (more suited for analysts) or using dashboards (PerformancePoint Services) with charts, scorecards (more suited for top management) based on SharePoint platform seems to be the best but not ideal choice
Documents:
- Microsoft Power Map Preview for Excel (Getting Started)
by Microsoft - Power BI – Getting Started Guide
by Microsoft - Power BI – Provisioning Guide
by Microsoft - SharePoint Server for Business Intelligence
by Microsoft - SP2010 Kerberos Guide
by Microsoft - The final Kerberos guide for SharePoint technicians
by Thomas Balkeståhl
Additional references:
- Business Discovery: Business Intelligence For Everyone
by QlickView - Business Intelligence, Analytics & Mobile
by MicroStrategy - Compare all Office 365 for business plans
by Office Team - Compare SharePoint options
by Office Team - Day 2: PASS Business Analytics Conference, New 3D Mapping Analytics Tool for Excel
by SQL Server Team - Fully qualified domain name
by Wikipedia - How to License Office Web Apps Server
by TechNet - Installing and Configuring a Three-Server SharePoint 2013 Environment
by Randy Rempel - jQuery Mobile
by The jQuery Foundation - Microsoft Contoso BI Demo Dataset for Retail Industry
by Microsoft Download Center - Microsoft Office shared tools – Office Web Components (OWC)
by Wikipedia - Microsoft Office Web Apps Server
by Microsoft Download Center - Microsoft Power BI
by Microsoft Windows Store - Mobile Business Intelligence Apps & BI Analytics
by Roambi - Moonlight
by Mono - Planning for Reporting Services and Power View Browser Support
by MSDN - PrimeFaces Mobile
by PrimeFaces - PUSHBI
by PUSHB - Responsive SharePoint
by CodePlex - Scenario: Business intelligence for SharePoint 2013 IT pros
by TechNet - Service Principal Names
by MSDN - SharePoint Server 2013 Demo site (was WSSDemo.com)
by SPSDemo - Starter Master Pages for SharePoint
by CodePlex - Windows Phone SDK
by Windows Phone Dev Center
Task: to show how to render spatial data report in SSRS
New spatial data types geometry and geography were introduced in SQL Server 2008 and the new map feature in SQL Server Reporting Services 2008 R2.
The report will use the AdventureWorksDW2012 database and show reseller sales amount by state (US).
The process of creating the map report consists of the following steps:
Code 1: Create datasets
Data Source:
Data Source=localhost;Initial Catalog=AdventureWorksDW2012
Dataset:
dsResellerSales
SELECT g.StateProvinceCode, SUM(f.SalesAmount) AS 'SalesAmount'
FROM dbo.FactResellerSales f JOIN dbo.DimDate d ON d.DateKey = f.ShipDateKey
JOIN dbo.DimReseller s ON s.ResellerKey = f.ResellerKey
JOIN dbo.DimGeography g ON g.GeographyKey = s.GeographyKey
WHERE d.CalendarYear = @CalendarYear AND g.CountryRegionCode = 'US'
GROUP BY g.StateProvinceCode
Dataset:
dsOrderYears
SELECT DISTINCT YEAR(f.OrderDate) AS 'OrderYear'
FROM dbo.FactResellerSales f JOIN dbo.DimDate d ON d.DateKey = f.ShipDateKey
JOIN dbo.DimReseller s ON s.ResellerKey = f.ResellerKey
JOIN dbo.DimGeography g ON g.GeographyKey = s.GeographyKey
WHERE g.CountryRegionCode = 'US'
ORDER BY 'OrderYear'
Figure 1: New map layer – choose a source of spatial data
Figure 2: New map layer – choose spatial data and map view options
Figure 3: New map layer – choose map visualization
Figure 4: New map layer – choose the analytical dataset
Figure 5: New map layer – specify the match fields for spatial and analytical data
Figure 6: New map layer – choose color theme and data visualization
Figure 7: Map layers – right click – map properties
Figure 8: Report parameter properties – calendar year
Figure 9: Preview reseller sales amount map
Source code:
- SSRS_Map.zip
by Marian Placko
Additional references:
- Create CNN-style Map in Reporting Services
by Peichung Shih - Free Spatial Data
by DIVA-GIS - Generate SSRS reports from a SSIS Package
by Ravindra Chhabria - MapGallery of Reporting Services in SQL Server 2008 R2
by CodePlex - Migrating SQL Reporting Services to a new server
by Dale Kelly - Migrating SQL Reporting Services to a new server by moving the Reporting Services databases
by Dale Kelly - Moving the Report Server Databases to Another Computer
by TechNet - SQL SERVER – World Shapefile Download and Upload to Database – Spatial Database
by Pinal Dave - TIGER = Topologically Integrated Geographic Encoding and Referencing
by Census - Map Data
by VDS Technologies
An architecture for automated team-based development and deployment of DWH/BI projects
Posted: June 1, 2013 in MSSQLTags: DWH
Task: to design an architecture for automated team-based development and deployment of data warehouse and business intelligence projects by means of open source or free products
Figure 1: UML – Use Case (Enterprise Architect)
Abbreviations/Notes:
- SVN: Subversion (Apache, Open Source, Repository)
- UML: Enterprise Architect (EA, SparxSystems, Commercial Product, Script Generating)
- PowerDesigner (Sybase, Commercial Product)
- Visual Studio: Ultimate Edition (Architecture and Modeling) (Microsoft, Commercial Product)
- Office Visio (Microsoft, Commercial Product)
- wwwsqldesigner (Google, Open Source) or ArgoUML (Tigris, Open Source)
- CI: Continuous Integration (Jenkins, JAVA, Open Source)
- – – – optional
UML alternatives:
SVN+WinMerge+VS(Visual Studio: SSDT/BIDS – Free Product, Schema and Data Compare):
- server: VisualSVNServer (Standard Edition – Free Product)
- client for OS: TortoiseSVN (Open Source)
- plugin for VS: AnkhSVN (Open Source)
- WinMerge: diff tool (Open Source)
- SSDT: SQL Server Data Tools (Free Product)
SVN+VS:
Figure 2: SNV+VS (DB and BI Projects)
Jenkins:
Figure 3: Jenkins – Build Pileline (Source: Build Pipeline Plugin's Site)
Parameters:
- Environment (Choice): DEV, TEST, PROD
- INCLUDE_DB_STG (Boolean value): TRUE
- INCLUDE_DB_ODS (Boolean value): TRUE
- INCLUDE_DB_DWH (Boolean value): TRUE
- INCLUDE_DB_LOG (Boolean value): TRUE
- INCLUDE_SSIS_STG (Boolean value): TRUE
- INCLUDE_SSIS_ODS (Boolean value): TRUE
- INCLUDE_SSIS_DWH (Boolean value): TRUE
- INCLUDE_SSAS (Boolean value): TRUE
- INCLUDE_SSRS (Boolean value): TRUE
Source Code Management:
- Subversion: https://server-snv/dwh_bi/test
Build Triggers:
- Build periodically: H H(0-5) * * 1-5 (note: every working day during night hours)
Extra Plugins:
Build:
1. Execute windows batch command – BUILD_DEVENV.bat
"%WORKSPACE%\CI_JENKINS\BUILD_DEVENV.bat" %Environment%
1.1 BUILD_DEVENV.bat
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET devenv="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe"
SET solution="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\TEST.sln"
%devenv% %solution% /build Debug
GOTO END
:TEST
SET devenv="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe"
SET solution="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\TEST.sln"
%devenv% %solution% /build Release
GOTO END
:PROD
SET devenv="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe"
SET solution="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\TEST.sln"
%devenv% %solution% /build Release
GOTO END
:UNKNOWN
GOTO END
:END
2. Execute windows batch command – DB_RDB_BACKUP.bat
"%WORKSPACE%\CI_JENKINS\DB_RDB_BACKUP.bat" %Environment%
2.1 DB_RDB_BACKUP.bat
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET sqlcmd="sqlcmd.exe"
SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_RDB_BACKUP.sql"
SET server="server\test"
%sqlcmd% -S %server% -i %backup%
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
2.2 DB_RDB_BACKUP.sql
BACKUP DATABASE [DWH_TEST_DWH] TO DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_DWH.bak' WITH NOFORMAT, NOINIT, NAME = N'DWH_TEST_DWH-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [DWH_TEST_LOG] TO DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_LOG.bak' WITH NOFORMAT, NOINIT, NAME = N'DWH_TEST_LOG-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [DWH_TEST_ODS] TO DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_ODS.bak' WITH NOFORMAT, NOINIT, NAME = N'DWH_TEST_ODS-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [DWH_TEST_STG] TO DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_STG.bak' WITH NOFORMAT, NOINIT, NAME = N'DWH_TEST_STG-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
2.3 DB_RDB_RESTORE.bat (optional)
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET sqlcmd="sqlcmd.exe"
SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_RDB_RESTORE.sql"
SET server="server\test"
%sqlcmd% -S %server% -i %backup%
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
2.4 DB_RDB_RESTORE.sql (optional)
USE [master]
ALTER DATABASE [DWH_TEST_DWH] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DWH_TEST_DWH] FROM DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_DWH.bak' WITH FILE = 4, MOVE N'DWH_TEST_DWH' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_DWH.mdf', MOVE N'DWH_TEST_DWH_log' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_DWH.ldf', NOUNLOAD, REPLACE, STATS = 5
ALTER DATABASE [DWH_TEST_DWH] SET MULTI_USER
GO
USE [master]
ALTER DATABASE [DWH_TEST_LOG] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DWH_TEST_LOG] FROM DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_LOG.bak' WITH FILE = 4, MOVE N'DWH_TEST_LOG' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_LOG.mdf', MOVE N'DWH_TEST_LOG_log' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_LOG.ldf', NOUNLOAD, REPLACE, STATS = 5
ALTER DATABASE [DWH_TEST_LOG] SET MULTI_USER
GO
USE [master]
ALTER DATABASE [DWH_TEST_ODS] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DWH_TEST_ODS] FROM DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_ODS.bak' WITH FILE = 4, MOVE N'DWH_TEST_ODS' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_ODS.mdf', MOVE N'DWH_TEST_ODS_log' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_ODS.ldf', NOUNLOAD, REPLACE, STATS = 5
ALTER DATABASE [DWH_TEST_ODS] SET MULTI_USER
GO
USE [master]
ALTER DATABASE [DWH_TEST_STG] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DWH_TEST_STG] FROM DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_STG.bak' WITH FILE = 4, MOVE N'DWH_TEST_STG' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_STG.mdf', MOVE N'DWH_TEST_STG_log' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_STG.ldf', NOUNLOAD, REPLACE, STATS = 5
ALTER DATABASE [DWH_TEST_STG] SET MULTI_USER
GO
3. Execute windows batch command – DB_SSIS_BACKUP.bat
"%WORKSPACE%\CI_JENKINS\DB_SSIS_BACKUP.bat" %Environment%
3.1 DB_SSIS_BACKUP.bat
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET sqlcmd="sqlcmd.exe"
SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_SSIS_BACKUP.sql"
SET server="server"
%sqlcmd% -S %server% -i %backup%
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
3.2 DB_SSIS_BACKUP.sql
BACKUP DATABASE [SSISDB] TO DISK = N'D:\MSSQL\Backup\SSISDB.bak' WITH NOFORMAT, NOINIT, NAME = N'SSISDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
3.3 DB_SSIS_RESTORE.bat (optional)
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET sqlcmd="sqlcmd.exe"
SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_SSIS_RESTORE.sql"
SET server="server"
%sqlcmd% -S %server% -i %backup%
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
3.4 DB_SSIS_RESTORE.sql (optional)
USE [master]
ALTER DATABASE [SSISDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [SSISDB] FROM DISK = N'D:\MSSQL\Backup\SSISDB.bak' WITH FILE = 3, MOVE N'data' TO N'D:\MSSQL\Data\SSISDB.mdf', MOVE N'log' TO N'D:\MSSQL\Data\SSISDB.ldf', NOUNLOAD, REPLACE, STATS = 5
ALTER DATABASE [SSISDB] SET MULTI_USER
GO
4. Execute windows batch command – DB_SSRS_BACKUP.bat
"%WORKSPACE%\CI_JENKINS\DB_SSRS_BACKUP.bat" %Environment%
4.1 DB_SSRS_BACKUP.bat
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET sqlcmd="sqlcmd.exe"
SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_SSRS_BACKUP.sql"
SET server="server\test"
%sqlcmd% -S %server% -i %backup%
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
4.2 DB_SSRS_BACKUP.sql
BACKUP DATABASE [ReportServer$TEST] TO DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TEST.bak' WITH NOFORMAT, NOINIT, NAME = N'ReportServer$TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [ReportServer$TESTTempDB] TO DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TESTTempDB.bak' WITH NOFORMAT, NOINIT, NAME = N'ReportServer$TESTTempDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
4.3 DB_SSRS_RESTORE.bat (optional)
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET sqlcmd="sqlcmd.exe"
SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_SSRS_RESTORE.sql"
SET server="server\test"
%sqlcmd% -S %server% -i %backup%
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
4.4 DB_SSRS_RESTORE.sql (optional)
USE [master]
ALTER DATABASE [ReportServer$TEST] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP LOG [ReportServer$TEST] TO DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TEST_LogBackup_2013-05-25_11-54-42.bak' WITH NOFORMAT, NOINIT, NAME = N'ReportServer$TEST_LogBackup_2013-05-25_11-54-42', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [ReportServer$TEST] FROM DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TEST.bak' WITH FILE = 2, NOUNLOAD, REPLACE, STATS = 5
ALTER DATABASE [ReportServer$TEST] SET MULTI_USER
GO
USE [master]
ALTER DATABASE [ReportServer$TESTTempDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [ReportServer$TESTTempDB] FROM DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TESTTempDB.bak' WITH FILE = 2, NOUNLOAD, REPLACE, STATS = 5
ALTER DATABASE [ReportServer$TESTTempDB] SET MULTI_USER
GO
4.5 SSRS_BACKUP.bat (optional)
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET ItemPath="/SSRS_TEST"
SET ReportServerURL=http://server/ReportServer_TEST
SET BackupFolder="C:\temp\CI_JENKINS\Backups\SSRS"
SET ScriptFolder="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_BACKUP.rss"
RS -i %ScriptFolder% -s %ReportServerURL% -v ItemPath="%ItemPath%" -v BackupFolder="%BackupFolder%"
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
4.6 SSRS_BACKUP.rss (optional)
Public Sub Main()
Try
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim Items as CatalogItem()
Dim Item as CatalogItem
Dim ReportName As String
Items = rs.ListChildren(ItemPath, false)
Console.Writeline()
Console.Writeline("Reports Back Up Started.")
For Each Item in Items
ReportName = ItemPath + "/" + Item.Name
Dim reportDefinition As Byte() = Nothing
Dim rdlReport As New System.Xml.XmlDocument
reportDefinition = rs.GetReportDefinition(ReportName)
Dim Stream As New MemoryStream(reportDefinition)
Dim curDate as Date = Date.Now()
Dim strDate as String = curDate.ToString("dd-MM-yyyy")
Dim BackupFolderNew as String = BackupFolder+"\"+strDate+"\"+ItemPath
If(Not System.IO.Directory.Exists(BackupFolderNew )) Then
System.IO.Directory.CreateDirectory(BackupFolderNew)
End If
rdlReport.Load(Stream)
rdlReport.Save(BackupFolderNew + "\" + Item.Name +".rdl")
Console.Writeline("Report " + Item.Name +".rdl Backed up Successfully")
Next
Console.Writeline("Reports Back Up Completed.")
Console.Writeline()
catch e As Exception
Console.Writeline(e.Message)
End Try
End Sub
5. Execute windows batch command – SSAS_BACKUP.bat
"%WORKSPACE%\CI_JENKINS\SSAS_BACKUP.bat" %Environment%
5.1 SSAS_BACKUP.bat
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET ASCMD="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSAS_ASCMD.exe"
SET XMLA="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSAS_BACKUP.xmla"
SET SERVER="server\test"
%ASCMD% -S %SERVER% -i %XMLA%
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
5.2 SSAS_BACKUP.xmla
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>SSAS</DatabaseID>
</Object>
<File>SSAS.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>
5.3 SSAS_RESTORE.xmla (optional)
<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<File>SSAS.abf</File>
<DatabaseName>SSAS</DatabaseName>
<AllowOverwrite>true</AllowOverwrite>
</Restore>
6. Execute windows batch command – INCLUDE_DB_STG.bat
"%WORKSPACE%\CI_JENKINS\INCLUDE_DB_STG.bat" %INCLUDE_DB_STG%, %Environment%
6.1 INCLUDE_DB_STG.bat
IF [%1]==[true] (
GOTO INCLUDED
)
IF [%1]==[false] (
GOTO END
)
:INCLUDED
"C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_STG_DEPLOY.bat" %2
GOTO END
:END
6.2 DB_STG_DEPLOY.bat
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET SqlPackage="C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe"
SET publish="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_STG\DB_STG_server.publish.xml"
SET dacpac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_STG\bin\Debug\DB_STG.dacpac"
%SqlPackage% /pr:%publish% /sf:%dacpac% /a:Publish
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
7. Execute windows batch command – INCLUDE_DB_ODS.bat
"%WORKSPACE%\CI_JENKINS\INCLUDE_DB_ODS.bat" %INCLUDE_DB_ODS%, %Environment%
7.1 INCLUDE_DB_ODS.bat
IF [%1]==[true] (
GOTO INCLUDED
)
IF [%1]==[false] (
GOTO END
)
:INCLUDED
"C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_ODS_DEPLOY.bat" %2
GOTO END
:END
7.2 DB_ODS_DEPLOY.bat
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET SqlPackage="C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe"
SET publish="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_ODS\DB_ODS_server.publish.xml"
SET dacpac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_ODS\bin\Debug\DB_ODS.dacpac"
%SqlPackage% /pr:%publish% /sf:%dacpac% /a:Publish
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
8. Execute windows batch command – INCLUDE_DB_DWH.bat
"%WORKSPACE%\CI_JENKINS\INCLUDE_DB_DWH.bat" %INCLUDE_DB_DWH%, %Environment%
8.1 INCLUDE_DB_DWH.bat
IF [%1]==[true] (
GOTO INCLUDED
)
IF [%1]==[false] (
GOTO END
)
:INCLUDED
"C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_DWH_DEPLOY.bat" %2
GOTO END
:END
8.2 DB_DWH_DEPLOY.bat
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET SqlPackage="C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe"
SET publish="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_DWH\DB_DWH_server.publish.xml"
SET dacpac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_DWH\bin\Debug\DB_DWH.dacpac"
%SqlPackage% /pr:%publish% /sf:%dacpac% /a:Publish
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
9. Execute windows batch command – INCLUDE_DB_LOG.bat
"%WORKSPACE%\CI_JENKINS\INCLUDE_DB_LOG.bat" %INCLUDE_DB_LOG%, %Environment%
9.1 INCLUDE_DB_LOG.bat
IF [%1]==[true] (
GOTO INCLUDED
)
IF [%1]==[false] (
GOTO END
)
:INCLUDED
"C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_LOG_DEPLOY.bat" %2
GOTO END
:END
9.2 DB_LOG_DEPLOY.bat
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET SqlPackage="C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe"
SET publish="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_LOG\DB_LOG_server.publish.xml"
SET dacpac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_LOG\bin\Debug\DB_LOG.dacpac"
%SqlPackage% /pr:%publish% /sf:%dacpac% /a:Publish
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
10 Execute windows batch command – INCLUDE_SSIS_STG.bat
"%WORKSPACE%\CI_JENKINS\INCLUDE_SSIS_STG.bat" %INCLUDE_SSIS_STG%, %Environment%
10.1 INCLUDE_SSIS_STG.bat
IF [%1]==[true] (
GOTO INCLUDED
)
IF [%1]==[false] (
GOTO END
)
:INCLUDED
"C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSIS_STG_DEPLOY.bat" %2
GOTO END
:END
10.2 SSIS_STG_DEPLOY.bat
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET ISDeploymentWizard="ISDeploymentWizard.exe"
SET ispac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\SSIS_STG\bin\Development\SSIS_STG.ispac"
SET server="server"
SET SSIS="/SSISDB/TEST/SSIS_STG"
%ISDeploymentWizard% /S /SP:%ispac% /DS:%server% /DP:%SSIS%
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
11. Execute windows batch command – INCLUDE_SSIS_ODS.bat
"%WORKSPACE%\CI_JENKINS\INCLUDE_SSIS_ODS.bat" %INCLUDE_SSIS_ODS%, %Environment%
11.1 INCLUDE_SSIS_ODS.bat
IF [%1]==[true] (
GOTO INCLUDED
)
IF [%1]==[false] (
GOTO END
)
:INCLUDED
"C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSIS_ODS_DEPLOY.bat" %2
GOTO END
:END
11.2 SSIS_ODS_DEPLOY.bat
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET ISDeploymentWizard="ISDeploymentWizard.exe"
SET ispac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\SSIS_ODS\bin\Development\SSIS_ODS.ispac"
SET server="server"
SET SSIS="/SSISDB/TEST/SSIS_ODS"
%ISDeploymentWizard% /S /SP:%ispac% /DS:%server% /DP:%SSIS%
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
12. Execute windows batch command – INCLUDE_SSIS_DWH.bat
"%WORKSPACE%\CI_JENKINS\INCLUDE_SSIS_DWH.bat" %INCLUDE_SSIS_DWH%, %Environment%
12.1 INCLUDE_SSIS_DWH.bat
IF [%1]==[true] (
GOTO INCLUDED
)
IF [%1]==[false] (
GOTO END
)
:INCLUDED
"C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSIS_DWH_DEPLOY.bat" %2
GOTO END
:END
12.2 SSIS_DWH_DEPLOY.bat
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET ISDeploymentWizard="ISDeploymentWizard.exe"
SET ispac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\SSIS_DWH\bin\Development\SSIS_DWH.ispac"
SET server="server"
SET SSIS="/SSISDB/TEST/SSIS_DWH"
%ISDeploymentWizard% /S /SP:%ispac% /DS:%server% /DP:%SSIS%
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
13. Execute windows batch command – INCLUDE_SSAS.bat
"%WORKSPACE%\CI_JENKINS\INCLUDE_SSAS.bat" %INCLUDE_SSAS%, %Environment%
13.1 INCLUDE_SSAS.bat
IF [%1]==[true] (
GOTO INCLUDED
)
IF [%1]==[false] (
GOTO END
)
:INCLUDED
"C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSAS_DEPLOY.bat" %2
GOTO END
:END
13.2 SSAS_DEPLOY.bat
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET Deployment="C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe"
SET asdatabase="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\SSAS\bin\SSAS.asdatabase"
%Deployment% %asdatabase% /s
GOTO END
:TEST
GOTO END
:PROD
GOTO END
:UNKNOWN
GOTO END
:END
14. Execute windows batch command – INCLUDE_SSRS.bat
"%WORKSPACE%\CI_JENKINS\INCLUDE_SSRS.bat" %INCLUDE_SSRS%, %Environment%
14.1 INCLUDE_SSRS.bat
IF [%1]==[true] (
GOTO INCLUDED
)
IF [%1]==[false] (
GOTO END
)
:INCLUDED
"C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.bat" %2
GOTO END
:END
14.2 SSRS_DEPLOY.bat
@echo off
::Script Variables
SET LOGFILE="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_TEST.log"
SET SCRIPTLOCATION=C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS
REM SET REPORTSERVER=http://server/ReportServer_TEST
IF [%1]==[DEV] (
GOTO DEV
)
IF [%1]==[TEST] (
GOTO TEST
)
IF [%1]==[PROD] (
GOTO PROD
) ELSE (
GOTO UNKNOWN
)
:DEV
SET REPORTSERVER=http://server/ReportServer_TEST
GOTO END
:TEST
SET REPORTSERVER=http://server/ReportServer_TEST
GOTO END
:PROD
SET REPORTSERVER=http://server/ReportServer_TEST
GOTO END
:UNKNOWN
SET REPORTSERVER=http://server/ReportServer_TEST
GOTO END
:END
SET RS=rs.exe
SET TIMEOUT=60
::Clear Log file
IF EXIST %logfile% DEL %logfile%
::Write Log Header
ECHO Reporting Services Scripter Load Log >>%LOGFILE%
ECHO. >>%LOGFILE%
ECHO Starting Load at %DATE% %TIME% >>%LOGFILE%
ECHO SCRIPTLOCATION = %SCRIPTLOCATION% >>%LOGFILE%
ECHO REPORTSERVER = %REPORTSERVER% >>%LOGFILE%
ECHO BACKUPLOCATION = %BACKUPLOCATION% >>%LOGFILE%
ECHO SCRIPTLEVEL = SQL2008 >>%LOGFILE%
ECHO TIMEOUT = %TIMEOUT% >>%LOGFILE%
ECHO RS = %rs% >>%LOGFILE%
ECHO. >>%LOGFILE%
::Run Scripts
%rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST" -v DATASOURCE="" >>%LOGFILE% 2>&1
ECHO. >>%LOGFILE%
@echo off %rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_1" -v DATASOURCE="" >>%LOGFILE% 2>&1
@echo off ECHO. >>%LOGFILE%
@echo off %rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_2" -v DATASOURCE="" >>%LOGFILE% 2>&1
@echo off ECHO. >>%LOGFILE%
@echo off %rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_3" -v DATASOURCE="" >>%LOGFILE% 2>&1
@echo off ECHO. >>%LOGFILE%
@echo off %rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_4" -v DATASOURCE="" >>%LOGFILE% 2>&1
@echo off ECHO. >>%LOGFILE%
ECHO. >>%LOGFILE%
ECHO Finished Load at %DATE% %TIME% >>%LOGFILE%
ECHO. >>%LOGFILE%
14.3 SSRS_DEPLOY.rss
Public Sub Main()
Dim pathToReport As String = "../workspace/SSRS_TEST/"
Dim region As String = REGIONFOLDER
Dim folder As String = PARENTFOLDER
Dim name As String = RDLNAME
Dim data As String = DATASOURCE
Dim parent As String = region + "/" + folder
Dim location As String = pathToReport + name + ".rdl"
Dim overwrite As Boolean = True
Dim reportContents As Byte() = Nothing
Dim warnings As Warning() = Nothing
Dim fullpath As String = parent + "/" + name
'Common CatalogItem properties
Dim descprop As New [Property]
descprop.Name = "Description"
descprop.Value = ""
Dim hiddenprop As New [Property]
hiddenprop.Name = "Hidden"
hiddenprop.Value = "False"
Dim props(1) As [Property]
props(0) = descprop
props(1) = hiddenprop
'console.writeline("region: {0}", region)
'console.writeline("folder: {0}", folder)
'console.writeline("name: {0}", name)
'console.writeline("parent=region/folder: {0}", parent)
'console.writeline("location=name.rdl: {0}", location)
'console.writeline("fullpath=parent/folder/name: {0}", fullpath)
'Read RDL definition from disk
Try
Dim stream As FileStream = File.OpenRead(location)
reportContents = New [Byte](stream.Length-1) {}
stream.Read(reportContents, 0, CInt(stream.Length))
stream.Close()
warnings = RS.CreateReport(name, parent, overwrite, reportContents, props)
If Not (warnings Is Nothing) Then
Dim warning As Warning
For Each warning In warnings
Console.WriteLine(Warning.Message)
Next warning
Else
Console.WriteLine("Report: {0} published successfully with no warnings", name)
End If
'Set report DataSource references
''Dim dataSources(0) As DataSource
''Dim dsr0 As New DataSourceReference
''dsr0.Reference = region + "/Data Sources/MyDataSource"
''Dim ds0 As New DataSource
''ds0.Item = CType(dsr0, DataSourceDefinitionOrReference)
''ds0.Name = data
''dataSources(0) = ds0
''RS.SetItemDataSources(fullpath, dataSources)
''Console.Writeline("Report DataSources set successfully")
''Console.WriteLine("Report: {0} published successfully", name)
Catch e As IOException
Console.WriteLine(e.Message)
Catch e As SoapException
Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText + " (" + e.Detail.Item("Message").InnerText + ")")
Console.WriteLine("Report: {0} published with error", name)
End Try
End Sub
14.4 SSRS_TEST.log (output log)
Reporting Services Scripter Load Log
Starting Load at st 22. 05. 2013 10:40:36,81
SCRIPTLOCATION = C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS
REPORTSERVER = http://server/ReportServer_TEST
BACKUPLOCATION =
SCRIPTLEVEL = SQL2008
TIMEOUT = 60
RS = rs.exe
Report: Report_TEST published successfully with no warnings
The command completed successfully
off rs.exe -i "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.rss" -s http://server/ReportServer_TEST -l 60 -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_1" -v DATASOURCE=""
off ECHO.
off rs.exe -i "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.rss" -s http://server/ReportServer_TEST -l 60 -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_2" -v DATASOURCE=""
off ECHO.
off rs.exe -i "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.rss" -s http://server/ReportServer_TEST -l 60 -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_3" -v DATASOURCE=""
off ECHO.
off rs.exe -i "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.rss" -s http://server/ReportServer_TEST -l 60 -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_4" -v DATASOURCE=""
off ECHO.
Finished Load at st 22. 05. 2013 10:40:53,28
Recommendations:
- Offline Development: it allows developers independent development, debugging own code and database structures and consequently commit already functional code to online database project repository
- Units (granularity – smaller units are better) for Development:
- DB (table, view, stored procedure, function etc.)
- SSIS (package etc.)
- SSAS (cube, dimension etc.)
- SSRS (data sets, report etc.)
- MSBuild.exe: only database projects
- devenv.exe: database (after installing SSDT) and business intelligence projects
Source code:
- DWH_BI__CI_JENKINS.zip
by Marian Placko
Additional references:
- CI Feature Matrix
by Jeffrey Fredrick - SQL Server 2012 Command Line Utilities
by Muthusamy Anantha Kumar - Jenkin’s Plugins
by Jenkins Group - Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 3/3
by Gary A. Stafford - Continuous Integration with Visual Studio 2010 Database Projects, Hudson & MSBuild (Part 3 of 3)
by Vishal Gamji - SQL Source Control
by RedGate - Deployment Manager
by RedGate - SQL Development Tools for Business Intelligence
by RedGate - Database delivery
by RedGate - Continuous Integration for SQL Server using Red Gate Tools, Atlassian Bamboo and GitHub
by Ben Rees - Continuous Integration for SQL Server Part II – Integration Testing
by Ben Rees - Continuous Integration for SQL Server Part III – Deployment
by Ben Rees - TeamCity
by JetBrains - Using SQL Test Database Unit Testing with TeamCity Continuous Integration
by Dave Green - Agent SVN – SCC Subversion Plug-in
by Jussi Jumppanen - SQL Server Management Studio and TortoiseSVN
by jrummell - Database Deployment: The Bits – Versioning
by Phil Factor - MSBuild Extension Pack
hosted on CodePlex - /Build (devenv.exe)
by MSDN - devenv-plugin
by zonybob - Use the sqlcmd Utility
by MSDN - SqlPackage.exe
by MSDN - Backup and Restore SQL Server with the SQL Server 2012 PowerShell cmdlets
by Allen White - Database Deployment Articles
by Alexander Karmanov - Policy Based Management in SQL Server 2008
by Keshav Singh - TOAD FOR SQL SERVER
by Toad World - List of Unified Modeling Language tools
by Wikipedia - Data Modelling Tools
by Dan Horne - DB>doc for Microsoft SQL Server
hosted on CodePlex - Create Documentation for SQL Server Tables
by Alexandros Pappas - BIDS Helper
hosted on CodePlex - Configure the Transact-SQL Debugger
by TechNet - Transact-SQL Debugger for SQL Server 2008 – Part 1
by Bill Ramos - Stored procedure debugging in SSDT and LocalDB
by Jamie Thomson - Getting Started with SQL Server Database Unit Testing in SSDT
by Bill Gibson - tSQLt
by Greg M Lucas - ssisUnit
hosted on CodePlex - SQL Load Generator
hosted on CodePlex - dtutil Utility
by MSDN - dtexec Utility (SSIS Tool)
by MSDN - Apex SQL SSIS Compare 2008.03.0007
by SOFTPEDIA - Analysis Services Deployment Methods
by MSDN - Deploy Model Solutions with the Deployment Utility
by MSDN - rs Utility (rs.exe) (SSRS)
by MSDN - Backup and Restore Operations for Reporting Services
by MSDN - Reporting Services Migration Tool
by MS Download Center - A Versioning system for SSRS
by David K Bennett
CI (general):
CI (Jenkins):
CI (RedGate):
CI (JetBrains):
SVN/Versioning:
MSBuild/devenv:
DB (general):
DB (modelling):
DB (documentation):
SSDT/BIDS:
Debugging:
Unit Testing/Testing:
SSIS:
SSAS:
SSRS:
Task: to show how simply to hack a login password in SQL Server. Comparison of elapsed/estimated time for decryption vs different password lengths in different SQL Server versions
WARNING: by hacking any part of SQL Server, e.g. a password login, you will lose the warranty from Microsoft. This blog post has the educational purpose only. For testing purposes, you should try it on the development machine only. I do not take any responsibility for any damages caused by this article.
Recommendations:
- run the cmd.exe as an administrator
- ERROR: cuModuleLoad() 209 – NV users require ForceWare 310.32 or later (NVIDIA update); AMD users require Catalyst 13.1 -exact-
- set convenient paths to hashcat utility (C:\Temp\Hashcat) and to text files (PASSWORDS and HASHES)
Prerequisites:
1. SSMS > T-SQL > get hashes of login passwords
SELECT [NAME], [PASSWORD_HASH]
FROM [SYS].[SQL_LOGINS]
or
SELECT [NAME],
LOGINPROPERTY([SYS].[SYSLOGINS].[NAME],'PasswordHash') AS PasswordHash
FROM [SYS].[SYSLOGINS]
WHERE LOGINPROPERTY([SYS].[SYSLOGINS].[NAME],'PasswordHash') IS NOT NULL
2. Windows > Explorer > download hashcat utility and create auxiliary files
- create C:\Temp\Hashcat\MSSQL05-08R2_PASSWORDS_CPU.txt
- create C:\Temp\Hashcat\MSSQL05-08R2_PASSWORDS_GPU.txt
- create C:\Temp\Hashcat\MSSQL05-08R2_HASHES.txt
- create C:\Temp\Hashcat\MSSQL12_PASSWORDS_CPU.txt
- create C:\Temp\Hashcat\MSSQL12_PASSWORDS_GPU.txt
- create C:\Temp\Hashcat\MSSQL12_HASHES.txt
- download and unzip hashcat utility from hashcat utility to C:\Temp\Hashcat\
Tests:
SW & HW > Laptop > Lenovo Thinkpad E530
- Windows 7
- Processor: Intel Core i5 3210 Ivy Bridge
- RAM: 12GB
- Graphics: NVIDIA GeForce GT630M 2GB Optimus + Intel HD Graphics 4000
1. Windows > Run > run a new CMD window – MSSQL05-08R2_CMD_CPU.bat
cd C:\Temp\Hashcat\hashcat-0.44
echo Started: %time%
hashcat-cli64.exe -a 3 --pw-min=5 --pw-max=5 -m 131 -p : -o "C:\Temp\Hashcat\MSSQL05-08R2_PASSWORDS_CPU.txt" --output-format=0 -n 4 "C:\Temp\Hashcat\MSSQL05-08R2_HASHES.txt" -1 ?l?u?d?s ?1?1?1?1?1
echo Stopped: %time%
pause
2. Windows > Run > run a new CMD window – MSSQL05-08R2_CMD_GPU.bat
cd C:\Temp\Hashcat\oclHashcat-lite-0.15
cudaHashcat-lite64.exe -m 132 -p : -o "C:\Temp\Hashcat\MSSQL05-08R2_PASSWORDS_GPU.txt" --outfile-format=3 --gpu-temp-abort=100 --pw-min=5 --pw-max=5 -1 ?l?u?d?s "C:\Temp\Hashcat\MSSQL05-08R2_HASHES.txt" ?1?1?1?1?1
pause
3. Windows > Run > run a new CMD window – MSSQL12_CMD_CPU.bat
cd C:\Temp\Hashcat\hashcat-0.44
echo Started: %time%
hashcat-cli64.exe -a 3 --pw-min=5 --pw-max=5 -m 1731 -p : -o "C:\Temp\Hashcat\MSSQL12_PASSWORDS_CPU.txt" --output-format=0 -n 4 "C:\Temp\Hashcat\MSSQL12_HASHES.txt" -1 ?l?u?d?s ?1?1?1?1?1
echo Stopped: %time%
pause
4. Windows > Run > run a new CMD window – MSSQL12_CMD_GPU.bat
cd C:\Temp\Hashcat\oclHashcat-lite-0.15
cudaHashcat-lite64.exe -m 1732 -p : -o "C:\Temp\Hashcat\MSSQL12_PASSWORDS_GPU.txt" --outfile-format=3 --gpu-temp-abort=100 --pw-min=5 --pw-max=5 -1 ?l?u?d?s "C:\Temp\Hashcat\MSSQL12_HASHES.txt" ?1?1?1?1?1
pause
NOTE: not supported at the moment
Abbreviations:
- -a 3 – the attack mode. 3 indicates using brute force
- –pw-min=5 –pw-max=5 – at least 5 characters long and not more than 5 characters long
- -m 131 – this means a SQL 2005-2008 R2 hash (CPU)
- -m 132 – this means a SQL 2005-2008 R2 hash (GPU)
- -m 1731 – this means a SQL 2012 hash (CPU)
- -m 1732 – this means a SQL 2012 hash (GPU; note: not supported at the moment)
- -p : -o “C:\Temp\Hashcat\MSSQL05-08R2_PASSWORDS_CPU.txt” – the output file name and location (CPU)
- -p : -o “C:\Temp\Hashcat\MSSQL05-08R2_PASSWORDS_GPU.txt” – the output file name and location (GPU)
- -p : -o “C:\Temp\Hashcat\MSSQL12_PASSWORDS_CPU.txt” – the output file name and location (CPU)
- -p : -o “C:\Temp\Hashcat\MSSQL12_PASSWORDS_GPU.txt” – the output file name and location (GPU)
- –output-format=0 – the format of the output file (CPU)
- –output-format=3 – the format of the output file (GPU)
- -n 4 – the number of thread counts to use
- “C:\Temp\Hashcat\MSSQL05-08R2_HASHES.txt” – the name and location of hash file for SQL 2005-2008 R2
- “C:\Temp\Hashcat\MSSQL12_HASHES.txt” – the name and location of hash file for SQL server 2012
- -1 ?l?u?d?s – the type of characters to try using brute force. l = lower case letters, u = upper case letters, d = numbers and s = special characters. (!@#, etc). Using ?a for all
- -?1?1?1?1?1 – number of position to the pw-max
- –gpu-temp-abort=100 – at 100 degrees Celsius, it will automatically stop
Notes:
- -oclHashcat-lite – decrypting single hash only
- -oclHashcat-plus – decrypting multiple hashes
Results:
Figure 1: CMD result – MSSQL05-08R2 (4-character password) – CPU
Figure 2: CMD result – MSSQL05-08R2 (5-character password) – CPU
Figure 3: CMD result – MSSQL05-08R2 (6-character password) – CPU
Figure 4: CMD result – MSSQL05-08R2 (8-character password) – CPU
Figure 5: CMD result – MSSQL05-08R2 (4-character password) – GPU
Figure 6: CMD result – MSSQL05-08R2 (5-character password) – GPU
Figure 7: CMD result – MSSQL05-08R2 (6-character password) – GPU
Figure 8: CMD result – MSSQL05-08R2 (8-character password) – GPU
Figure 9: CMD result – MSSQL12 (4-character password) – CPU
Figure 10: CMD result – MSSQL12 (5-character password) – CPU
Figure 11: CMD result – MSSQL12 (6-character password) – CPU
Figure 12: CMD result – MSSQL12 (8-character password) – CPU
Table results:
| SQL Server | CPU | GPU | |||||||
| password [chars] | _1Tc [4] | _1Tc& [5] | _1Tc5& [6] | _1Tc5&dI [8] | _1Tc [4] | _1Tc& [5] | _1Tc5& [6] | _1Tc5&dI [8] | |
| 2005-08R2 | decryption time | <1s | 7m | 12h 30m | >113h | 2s | 7s | 2h 19m | 2y 143d |
| 2012 | decryption time | <3s | >31m | >2h | >63h | – | – | – | – |
Notes:
- -light green – elapsed time
- -yellow – estimated time
Conclusion: the hashcat – advanced password recovery utility is very useful tool not only for decrypting SQL Server login passwords. The decryption time differences between particular SQL Server versions are caused by using of different hash algorithms. The version 2005-08R2 uses only SHA-1 hash whereas the version 2012 already uses SHA-2 (SHA-512 concretely) hash. To increase computing power and decrease decrypting time, it would be possible to connect more computers into grid etc.
Source Code:
- SQL_Server__a_simple_hack_of_a_login_password.zip
by Marian Placko
Additional references:
- DBCS – double-byte character set
by Wikipedia - hashcat – advanced password recovery
by hashcat - LOGINPROPERTY (Transact-SQL)
by MSDN - MD5
by Wikipedia - Pen test and hack microsoft sql server (mssql)
by Travis Altman - SHA-1
by Wikipedia - SHA-2 (SHA512)
by Wikipedia - sys.sql_logins (Transact-SQL)
by MSDN - sys.syslogins (Transact-SQL)
by MSDN
Task: to show how to horizontal partition a database table and how to apply different data compression to this partitions
SQL Server supports only one type of horizontal partitioning, the range partitions. It is the partitioning strategy in which data is partitioned based on the range that the value of a particular field falls in.
Figure 1: UML – Use Case
The process of horizontal partitioning of a database table and data compression consists of the following steps:
1. Create a new test database with two different filegroups (NOTE: the path C:\Temp\SQL Server\Partitioning\Primary and Secondary must already exist!)
IF EXISTS(
SELECT name
FROM sys.databases
WHERE name = N'Test_Partitioning')
DROP DATABASE Test_Partitioning
GO
CREATE DATABASE Test_Partitioning
ON PRIMARY
(NAME = 'TestPartitioning_Part2010',
FILENAME =
'C:\Temp\SQL Server\Partitioning\Primary\TestPartitioning_Part2010.mdf',
SIZE = 5, -- ISSUE: The CREATE DATABASE statement failed. The primary file
-- must be at least 5 MB to accommodate a copy of the model database.
MAXSIZE = 100,
FILEGROWTH = 1),
FILEGROUP TestPartitioning_Part2011
(NAME = 'TestPartitioning_Part2011',
FILENAME =
'C:\Temp\SQL Server\Partitioning\Secondary\TestPartitioning_Part2011.ndf',
SIZE = 2,
MAXSIZE = 100,
FILEGROWTH = 1),
FILEGROUP TestPartitioning_Part2012
(NAME = 'TestPartitioning_Part2012',
FILENAME =
'C:\Temp\SQL Server\Partitioning\Secondary\TestPartitioning_Part2012.ndf',
SIZE = 2,
MAXSIZE = 100,
FILEGROWTH = 1)
GO
2. Create the partition range function
USE Test_Partitioning
GO
CREATE PARTITION FUNCTION TestPartitioning_PartitionFunction(DATE)
AS RANGE LEFT FOR
VALUES('2010-12-31', '2012-01-01')
-- NOTE: interval: (- infinity, 2010-12-31],
-- [2011-01-01, 2011-12-31] and
-- [2012-01-01, infinity +)
GO
3. Create the partition scheme and attach the partition scheme to filegroups
USE Test_Partitioning
GO
CREATE PARTITION SCHEME TestPartitioning_PartitionScheme
AS PARTITION TestPartitioning_PartitionFunction
TO([PRIMARY], TestPartitioning_Part2011, TestPartitioning_Part2012)
GO
4. Create a table with the partition key and the partition scheme
USE Test_Partitioning
GO
CREATE TABLE dbo.Test_PartitionedTable
(ID INT NOT NULL,
[Date] DATE)
ON TestPartitioning_PartitionScheme([Date])
GO
5. Create the index on the partitioned table (optional and recommended)
USE Test_Partitioning
GO
CREATE UNIQUE CLUSTERED INDEX IX_TestPartition_Table
ON dbo.Test_PartitionedTable([Date])
ON TestPartitioning_PartitionScheme([Date])
GO
6. Insert data into the partitioned table
USE Test_Partitioning
GO
INSERT INTO dbo.Test_PartitionedTable(ID, [Date])
VALUES
(1, DATEADD(YEAR, -3, GETDATE())), -- inserted in the partition 2009
(2, DATEADD(YEAR, -2, GETDATE())), -- inserted in the partition 2010
(3, DATEADD(YEAR, -1, GETDATE())), -- inserted in the partition 2011
(4, GETDATE()) -- inserted in the partition 2012
GO
7. Test data from the dbo.Test_PartitionedTable
USE Test_Partitioning
GO
SELECT *
FROM dbo.Test_PartitionedTable
/*
SSMS RESULTS:
ID Date
1 2009-10-19
2 2010-10-19
3 2011-10-19
4 2012-10-19
*/
GO
8. Verify rows inserted in partitions
USE Test_Partitioning
GO
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) = 'Test_PartitionedTable'
ORDER BY partition_number
/*
SSMS RESULTS:
partition_id object_id index_id partition_number hobt_id rows filestream_filegroup_id data_compression data_compression_desc
72057594039238656 245575913 1 1 72057594039238656 2 0 0 NONE
72057594039304192 245575913 1 2 72057594039304192 1 0 0 NONE
72057594039369728 245575913 1 3 72057594039369728 1 0 0 NONE
*/
GO
9. SPLIT – add the new partition P4 for the next year 2013
USE Test_Partitioning
GO
ALTER DATABASE Test_Partitioning
ADD FILEGROUP TestPartitioning_Part2013
GO
ALTER DATABASE Test_Partitioning
ADD FILE
(NAME = 'TestPartitioning_Part2013',
FILENAME =
'C:\Temp\SQL Server\Partitioning\Secondary\TestPartitioning_Part2013.ndf',
SIZE = 2,
MAXSIZE = 100,
FILEGROWTH = 1)
TO FILEGROUP TestPartitioning_Part2013
GO
ALTER PARTITION SCHEME TestPartitioning_PartitionScheme
NEXT USED TestPartitioning_Part2013
GO
ALTER PARTITION FUNCTION TestPartitioning_PartitionFunction()
SPLIT RANGE('2013-01-01')
-- NOTE: interval: (- infinity, 2010-12-31],
-- [2011-01-01, 2011-12-31],
-- [2012-01-01, 2012-12-31] and
-- [2013-01-01, infinity +)
GO
10. SWITCH IN – move data from the staging table to the empty newly added partition P4
USE Test_Partitioning
GO
--INSERT INTO dbo.Test_PartitionedTable(ID, [Date])
--VALUES
--(5, DATEADD(YEAR, 1, GETDATE())), -- inserted in the partition 2013
--(6, DATEADD(YEAR, 2, GETDATE())) -- inserted in the partition 2014
--GO
IF EXISTS(
SELECT name
FROM sys.databases
WHERE name = N'Test_Partitioning_Staging')
DROP DATABASE Test_Partitioning_Staging
GO
CREATE DATABASE Test_Partitioning_Staging
ON PRIMARY
(NAME = 'Test_Partitioning_Staging',
FILENAME =
'C:\Temp\SQL Server\Partitioning\Primary\Test_Partitioning_Staging.mdf',
SIZE = 5, -- ISSUE: The CREATE DATABASE statement failed. The primary file
-- must be at least 5 MB to accommodate a copy of the model database.
MAXSIZE = 100,
FILEGROWTH = 1)
GO
USE Test_Partitioning_Staging
GO
CREATE TABLE dbo.Test_NonPartitionedStagingTable
(ID INT NOT NULL,
[Date] DATE)
GO
CREATE UNIQUE CLUSTERED INDEX IX_Test_NonPartitionedStagingTable
ON dbo.Test_NonPartitionedStagingTable([Date])
GO
INSERT INTO dbo.Test_NonPartitionedStagingTable(ID, [Date])
VALUES
(5, DATEADD(YEAR, 1, GETDATE())), -- inserted in the partition 2013
(6, DATEADD(YEAR, 2, GETDATE())) -- inserted in the partition 2014
/*
SSMS RESULTS:
ID Date
5 2013-10-31
6 2014-10-31
*/
GO
USE Test_Partitioning
GO
ALTER DATABASE Test_Partitioning
MODIFY FILEGROUP TestPartitioning_Part2012 DEFAULT
GO
CREATE TABLE dbo.Test_NonPartitionedStagingTemp
(ID INT NOT NULL,
[Date] DATE)
GO
CREATE UNIQUE CLUSTERED INDEX IX_Test_NonPartitionedStagingTemp
ON dbo.Test_NonPartitionedStagingTemp([Date])
GO
ALTER TABLE dbo.Test_NonPartitionedStagingTemp
ADD CONSTRAINT PartitionFunction CHECK ([Date] > '2013-01-01'
AND [Date] IS NOT NULL)
GO
INSERT INTO dbo.Test_NonPartitionedStagingTemp(ID, [Date])
SELECT ID, [Date]
FROM Test_Partitioning_Staging.dbo.Test_NonPartitionedStagingTable
GO
SELECT * FROM dbo.Test_NonPartitionedStagingTemp
GO
/*
SSMS RESULTS:
ID Date
5 2013-10-31
6 2014-10-31
*/
ALTER TABLE dbo.Test_NonPartitionedStagingTemp
SWITCH TO dbo.Test_PartitionedTable PARTITION 4
GO
DROP TABLE dbo.Test_NonPartitionedStagingTemp
GO
ALTER DATABASE Test_Partitioning
MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
SELECT *
FROM dbo.Test_PartitionedTable
/*
SSMS RESULTS:
ID Date
1 2009-10-19
2 2010-10-19
3 2011-10-19
4 2012-10-19
5 2013-10-19
6 2014-10-19
*/
GO
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) = 'Test_PartitionedTable'
ORDER BY partition_number
/*
SSMS RESULTS:
partition_id object_id index_id partition_number hobt_id rows filestream_filegroup_id data_compression data_compression_desc
72057594039238656 245575913 1 1 72057594039238656 2 0 0 NONE
72057594039304192 245575913 1 2 72057594039304192 1 0 0 NONE
72057594039435264 245575913 1 3 72057594039435264 1 0 0 NONE
72057594039369728 245575913 1 4 72057594039369728 2 0 0 NONE
*/
GO
11. SWITCH OUT – move data from the first partition P1 to the archive table dbo.Test_NonPartitionedArchiveTable
USE Test_Partitioning
GO
CREATE TABLE dbo.Test_NonPartitionedArchiveTable
(ID INT NOT NULL,
[Date] DATE)
ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX IX_NonPartitioned_Archive
ON dbo.Test_NonPartitionedArchiveTable([Date])
GO
ALTER TABLE dbo.Test_PartitionedTable SWITCH PARTITION 1
TO dbo.Test_NonPartitionedArchiveTable
GO
SELECT *
FROM dbo.Test_NonPartitionedArchiveTable
/*
SSMS RESULTS:
ID Date
1 2009-10-19
2 2010-10-19
*/
GO
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) = 'Test_PartitionedTable'
ORDER BY partition_number
/*
SSMS RESULTS:
partition_id object_id index_id partition_number hobt_id rows filestream_filegroup_id data_compression data_compression_desc
72057594039238656 245575913 1 1 72057594039238656 0 0 0 NONE
72057594039304192 245575913 1 2 72057594039304192 1 0 0 NONE
72057594039435264 245575913 1 3 72057594039435264 1 0 0 NONE
72057594039369728 245575913 1 4 72057594039369728 2 0 0 NONE
*/
GO
12. MERGE – based on ‘2012-01-01’, i.e. P1 = PRIMARY FG (2010) + SECONDARY FG (2011)
USE Test_Partitioning
GO
ALTER PARTITION FUNCTION TestPartitioning_PartitionFunction()
MERGE RANGE('2012-01-01')
-- NOTE: interval: (- infinity, 2010-12-31]
-- [2011-01-01, 2011-12-31],
-- [2012-01-01, 2012-12-31] and
-- [2013-01-01, infinity +)
GO
SELECT *
FROM dbo.Test_PartitionedTable
/*
SSMS RESULTS:
ID Date
3 2011-10-19
4 2012-10-19
5 2013-10-19
6 2014-10-19
*/
GO
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) = 'Test_PartitionedTable'
ORDER BY partition_number
/*
SSMS RESULTS:
partition_id object_id index_id partition_number hobt_id rows filestream_filegroup_id data_compression data_compression_desc
72057594039238656 245575913 1 1 72057594039238656 0 0 0 NONE
72057594039304192 245575913 1 2 72057594039304192 2 0 0 NONE
72057594039369728 245575913 1 3 72057594039369728 2 0 0 NONE
*/
GO
INSERT INTO dbo.Test_PartitionedTable(ID, [Date])
VALUES
(1, DATEADD(YEAR, -3, GETDATE())), -- inserted in the partition 2009
(2, DATEADD(YEAR, -2, GETDATE())) -- inserted in the partition 2010
GO
SELECT *
FROM dbo.Test_PartitionedTable
/*
SSMS RESULTS:
ID Date
1 2009-10-19
2 2010-10-19
3 2011-10-19
4 2012-10-19
5 2013-10-19
6 2014-10-19
*/
GO
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) = 'Test_PartitionedTable'
ORDER BY partition_number
/*
SSMS RESULTS:
partition_id object_id index_id partition_number hobt_id rows filestream_filegroup_id data_compression data_compression_desc
72057594039238656 245575913 1 1 72057594039238656 2 0 0 NONE
72057594039304192 245575913 1 2 72057594039304192 2 0 0 NONE
72057594039369728 245575913 1 3 72057594039369728 2 0 0 NONE
*/
GO
13. Apply compression to your partitioned table
USE Test_Partitioning
GO
ALTER TABLE Test_PartitionedTable
REBUILD PARTITION = All
WITH
(
DATA_COMPRESSION = PAGE ON Partitions(1), -- NOTE: (1 TO 3)
DATA_COMPRESSION = ROW ON Partitions(2) ,
DATA_COMPRESSION = NONE ON Partitions(3)
);
GO
14. Apply compression to your partitioned index
USE Test_Partitioning
GO
ALTER INDEX IX_TestPartition_Table
ON dbo.Test_PartitionedTable
REBUILD PARTITION = All
WITH
(
DATA_COMPRESSION = PAGE ON Partitions(1),
DATA_COMPRESSION = ROW ON Partitions(2),
DATA_COMPRESSION = NONE ON Partitions(3)
);
GO
15. Apply compression to your unpartitioned index
--USE Test_Partitioning
--GO
--ALTER INDEX YourUnpartitionedIndex
--ON dbo.Test_PartitionedTable
--REBUILD WITH (DATA_COMPRESSION = ROW);
--GO
16. Testing of application three types of data compression
USE Test_Partitioning
GO
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) = 'Test_PartitionedTable'
ORDER BY partition_number
/*
SSMS RESULTS:
partition_id object_id index_id partition_number hobt_id rows filestream_filegroup_id data_compression data_compression_desc
72057594039238656 245575913 1 1 72057594039238656 2 0 2 PAGE
72057594039304192 245575913 1 2 72057594039304192 2 0 1 ROW
72057594039369728 245575913 1 3 72057594039369728 2 0 0 NONE
*/
GO
Recommendations:
- filegroups can be setup as read-only. It will increase the performance
- relational and cube partitions should be based on the same column
- SQL Server 7.0 introduced partitioning through partitioned views
- partitioning: when the number of rows is higher than 1M
- roughly 25GB per partition is well manageable (100GB would be poorly manageable)
- operations: SPLIT, MERGE and SWITCH are meta data operations only and do not involve any movement of data
- usage: to migrate older data from more expensive disk to less expensive disk
Quotes from references:
- “Though it is possible to create all partitions on PRIMARY but it would be best if these different partitions are stored in a separate file groups. This gives some performance improvement even in the case of single core computers. It would be best if these file groups are on different discs on a multi core processing machine.”
- “A partitioned table may have a partitioned index. Partition aligned index views may also be created for this table.
There may be question in your mind if it is possible to partition your table using multiple columns. The answer may be YES or NO. Why? No, because there is no such direct support for this in SQL Server. Yes, because you can still do that by using persisted computed column based on any number of columns you want.” - “For partitioning your existing table just drop the clustered index on your table and recreate it on the required partition scheme.”
- “Though SQL Server does not directly support List Partitioning, you can create list partitions by tricking the partition function to specify the values with the LEFT clause. After that, put a CHECK constraint on the table, so that no other values are allowed to be inserted in the table specifying Partition Key column any value other than the ‘list’ of values.”
- “It must be remembered that indexes can be partitioned using a different partition key than the table. The index can also have different numbers of partitions than the table. We cannot, however, partition the clustered index differently from the table. To partition an index, ON clause is used, specifying the partition scheme along with the column when creating the index:
If your table and index use the same Partition function then they are called Aligned. If they go further and also use the same partition scheme as well, then they are called Storage Aligned (note this in the figure below). If you use the same partition function for partitioning index as used by the table, then generally performance is improved.”
Additional references:
- Archiving SQL Server data using partitioning
by Ben Snaidero - Partitioning & Archiving tables in SQL Server (Part 2: Split, Merge and Switch partitions)
by Félix Martínez Retama - Partitioning in the AdventureWorks2008R2 Sample Database
by MSDN - Partitioning in SQL Server 2008
by Muhammad Shujaat Siddiqi - Partitioned Tables and Indexes
by MSDN - Partitioned Tables and Indexes in SQL Server 2005
by MSDN - Project REAL: Data Lifecycle Partitioning
by Microsoft Download Center - SQL Server Database Partitioning Myths and Truths
by Alan Cranfield - SQL Server Partitioning without Enterprise Edition
by Barry King - Switching data in and out of a SQL Server 2005 data partition
by Greg Robidoux - We Loaded 1TB in 30 Minutes with SSIS, and So Can You
by MSDN
Task: to make performance tests to show a performance impact of the columnstore index (SQL Server 2012)
Recommendations:
- to use on very large tables only (77 628 600 rows in my example; fact tables are good candidates)
- when the columnstore index is created, the table becomes read-only (despite of this fact, update/insert/delete operations are possible, e.g. by means of disabling the columnstore index)
The process of testing a performance impact of the columnstore index consists of the following steps:
1. Create the testing table
USE [AdventureWorksDW2012]
GO
CREATE TABLE [dbo].[MyFactProductInventory](
[ProductKey] [int] NOT NULL,
[DateKey] [int] NOT NULL,
[MovementDate] [date] NOT NULL,
[UnitCost] [money] NOT NULL,
[UnitsIn] [int] NOT NULL,
[UnitsOut] [int] NOT NULL,
[UnitsBalance] [int] NOT NULL,
) ON [PRIMARY]
GO
2. Create the clustered regular index
CREATE CLUSTERED INDEX [IX_MyFactProductInventory_Clustered]
ON [dbo].[MyFactProductInventory]([ProductKey])
GO
3. Create the sample data table (WARNING: this query may run up to 2-10 minutes based on your systems resources)
INSERT INTO [dbo].[MyFactProductInventory]
SELECT [Fact].* FROM [dbo].[FactProductInventory] AS [Fact]
GO 100
4. Performance tests (NOTE: comparing the regular (clustered / non-clustered) index with the columnstore index)
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
5. Select the table with the clustered regular index
SELECT ProductKey, SUM(UnitCost) AS 'SumUnitCost',
AVG(UnitsBalance) AS 'AvgUnitsBalance'
FROM [dbo].[MyFactProductInventory]
GROUP BY ProductKey
ORDER BY ProductKey
GO
/* First testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 5, logical reads 602219,
-- physical reads 81, read-ahead reads 379178, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 23665 ms, elapsed time = 18398 ms.
/* Second testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 5, logical reads 601939,
-- physical reads 0, read-ahead reads 602119, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 20670 ms, elapsed time = 22654 ms.
/* Third testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 5, logical reads 602244,
-- physical reads 0, read-ahead reads 598015, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 21123 ms, elapsed time = 20832 ms.
6.1. Create the non-clustered regular index
CREATE NONCLUSTERED INDEX [IX_MyFactProductInventory_Nonclustered]
ON [MyFactProductInventory](UnitCost, UnitsBalance, ProductKey)
GO
7.1. Select the table with the non-clustered regular index
SELECT ProductKey, SUM(UnitCost) AS 'SumUnitCost',
AVG(UnitsBalance) AS 'AvgUnitsBalance'
FROM [dbo].[MyFactProductInventory]
GROUP BY ProductKey
ORDER BY ProductKey
GO
/* First testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 5, logical reads 290552,
-- physical reads 0, read-ahead reads 32391, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 31323 ms, elapsed time = 8665 ms.
/* Second testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 5, logical reads 290652,
-- physical reads 0, read-ahead reads 0, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 32448 ms, elapsed time = 8428 ms.
/* Third testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 5, logical reads 290597,
-- physical reads 0, read-ahead reads 0, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 31138 ms, elapsed time = 8351 ms.
ALTER INDEX [IX_MyFactProductInventory_Nonclustered]
ON [MyFactProductInventory] DISABLE
GO
6.2. Create the columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MyFactProductInventory_ColumnStore]
ON [MyFactProductInventory](UnitCost, UnitsBalance, ProductKey)
GO
7.2 Select the table with the columnstore index
SELECT ProductKey, SUM(UnitCost) AS 'SumUnitCost',
AVG(UnitsBalance) AS 'AvgUnitsBalance'
FROM [dbo].[MyFactProductInventory]
GROUP BY ProductKey
ORDER BY ProductKey
GO
/* First testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 4, logical reads 12497,
-- physical reads 4, read-ahead reads 19973, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 874 ms, elapsed time = 559 ms.
/* Second testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 4, logical reads 12320,
-- physical reads 0, read-ahead reads 4853, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 1043 ms, elapsed time = 363 ms.
/* Third testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 4, logical reads 12370,
-- physical reads 0, read-ahead reads 9278, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 874 ms, elapsed time = 435 ms.
8. Cleanup
DROP INDEX [IX_MyFactProductInventory_ColumnStore]
ON [dbo].[MyFactProductInventory]
GO
TRUNCATE TABLE [dbo].[MyFactProductInventory]
GO
DROP TABLE [dbo].[MyFactProductInventory]
GO
SET STATISTICS TIME OFF
GO
SET STATISTICS IO OFF
GO
Conclusion: the above results clearly show that the performance of the query is extremely fast after creating the columnstore index
- for logical reads: approximately 48 times faster than the clustered and 23 times faster than the non-clustered index
- for the elapsed time: approximately 33 times faster than the clustered and 16 times faster than the non-clustered index
Additional references:
- Introducing xVelocity in-memory technologies in SQL Server 2012 for 10-100X performance
by TechNet - SQL SERVER – ColumnStore Index – Batch Mode vs Row Mode
by Pinal Dave - SQL SERVER – Fundamentals of Columnstore Index
by Pinal Dave - SQL SERVER – How to Ignore Columnstore Index Usage in Query
by Pinal Dave - SQL SERVER – Updating Data in A Columnstore Index
by Pinal Dave - SQL Server 2012: ColumnStore Characteristics
by Vinod Kumar
Task: to show how simply to hack a system stored procedure in SQL Server 2005 and higher by means of DAC (Dedicated Administrator Connection) and resource database (the read-only mssqlsystemresource database)
WARNING: by hacking any part of SQL Server, e.g. a system stored procedure, you will lose the warranty from Microsoft. This blog post has the educational purpose only. For testing purposes, you should try it on the development machine only. I do not take any responsibility for any damages caused by this article.
Recommendations:
- make a backup of the mssqlsystemresource database
- if necessary, add “Full control” permissions for the user of “NT Service\MSSLQSERVER” (the account of SQL Server (MSSQLSERVER) service) on “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.ldf and mssqlsystemresource.mdf”
- run the cmd.exe as an administrator
The process of altering, i.e. hacking, e.g. of the system stored procedure sys.sp_who2 consists of the following steps:
1. Windows > Run => RUN A NEW CMD WINDOW
net stop mssqlserver
net start mssqlserver
cd C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
sqlcmd -S .\
sys.sp_who2
go
exit
exit @echo CLOSE THE CMD WINDOW
2. Windows > Run => RUN A NEW CMD WINDOW
net stop mssqlserver
cd C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
sqlservr -s mssqlserver -m
@echo DO NOT CLOSE THE CMD WINDOW
3. Windows > Run => RUN A NEW CMD WINDOW
cd C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
sqlcmd -S .\ -A
use mssqlsystemresource
go
alter database mssqlsystemresource set read_write
go
sp_helptext 'sys.sp_who2'
go
alter procedure sys.sp_who2 as select 'hacked procedure'
go
sp_helptext 'sys.sp_who2'
go
alter database mssqlsystemresource set read_only
go
exit
exit @echo CLOSE ALL RUNNING CMD WINDOWS
4. Windows > Run => RUN A NEW CMD WINDOW
net start mssqlserver
cd C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
sqlcmd -S .\
sys.sp_who2
go
exit
exit @echo CLOSE THE CMD WINDOW
Abbreviations:
- -A > dedicated admin connection
- -S > server
- -m > single user admin mode
- -s > name (alternate registry key name)
- .\ > localhost and default instance
Figure 1: SQLCMD result – the procedure sys.sp_who2 before hacking
Figure 2: SQLCMD result – the procedure sys.sp_who2 after hacking
Additional references:
- DAC (Dedicated Administrator Connection) in SQL Server 2008/2005 Express Edition
by Marian Placko - Resource Database
by MSDN
Task: to create a SSIS package to incremental data loading by means of Change Tracking
The process of creating a SSIS solution consists of the following steps:
1. creating of a SSDT (SQL Server Data Tools) SSIS project
2. enabling of the ‘CHANGE TRACKING’ feature on a database and table level
3. creating of the ‘MySettings’ table and inserting a default value into the ‘LastVersion’ column
4. creating of the ‘Version_Get’ stored procedure
5. getting and setting of the ‘@LastVersion’ and ‘@CurrentVersion’ variables
6. creating of the FAKE DWH ‘BusinessEntity_DWH’ table
7. FAKE inserting into the ‘BusinessEntity’ table
8. incremental data loading into the fake DWH ‘BusinessEntity_DWH’ table (the core of ‘CHANGE TRACKING’)
9. saving of the ‘@CurrentVersion’ variable into the ‘LastVersion’ column
10. disabling of the ‘CHANGE TRACKING’ feature on a table and database level; deleting of created objects
Figure 1: Change Tracking – Control Flow
Figure 2: Change Tracking – Data Flow
Source code: SSIS Package (SQL Server 2012 – SSDT)
Note: do not forget to customize the ConnectionString in the Project.params setting
Additional references:
- Change data capture
by Wikipedia - Change Data Capture
by MSDN - Change Tracking
by MSDN - Configuring Change Tracking
by MCTS EXAM 70-433 - Features Supported by the Editions of SQL Server 2012
by MSDN - Incremental Data Loading Using CDC
by Mark Murphy - Introduction to Change Data Capture (CDC) in SQL Server 2008
by Pinal Dave - Tuning the Performance of Change Data Capture in SQL Server 2008
by TechNet
Placko's database KB 