Task 8: Linked Servers

• Trying to do some changes on the production server I got the following error that should occur when you try to access the linked server.

TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch
(Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "MSDASQL" for linked server "Name" reported an error. The provider did not give
any information about the error. Cannot initialize the data source object of OLE DB provider "MSDASQL"
for linked server "Name". (Microsoft SQL Server, Error: 7399)

• What I found is: 

1. Restart the database
2. Toggle the value of link server time-out 

         sp_configure 'remote query timeout', 1 
         go 
         reconfigure with override 
         go
         sp_configure 'remote query timeout', 0
         go 
         reconfigure with override 
         go
3. Ask your client to provide you the linked server in order to see the expanded table list 
4. Check if the user is not blocked on that server

Task 7: Shrink statement

• On the production server the ETL job failed with the following error: Could not adjust the space allocation for file ... and then the ETL process was running again. The last step to run was step Shrink Truncate Data.
• What I found are some advises regarding shrink using: 

          1. Constant shrinking and growth is bad for performance.
          2. Automatic space allocation always causes a delay.
          3. The physical files get fragmented on the hard drive. 

• Another possibility is that another job is running against the database at the same time, eg. a backup job. You can't do a database or log backup at the same time as a shrink operation. Shrink file is not a best practice,  it cause data fragment, but sometimes after you clean the database with deleting old data, you had to shrink file and it should be fine as long as you run rebuild index after shrink file.

          DBCC SHRINKFILE(1, 1000)
          GO 

• As a suggested sequence we can try the following:

       1. Backup databases
       2. Detach the db 
       3. Attach it
       4. Put the db into single user mode
       5. Attempt the dbcc shrink file operation again
       6. If successful,  rebuild the indexes 
       7. Perform dbcc checkdb
       8. Database backup

Task 6: T-SQL Window Functions

• Based on my SQL Server 2012 exam I started looking for some information about enhancements for this topic. I found a new book by Itzik Ben-Gan regarding T-SQL Window Functions with the following chapters: 

          1. SQL Windowing  
          2. A Detailed Look at Window Functions
          3. Ordered Set Functions 
          4. Optimization of Window Functions 
          5. T-SQL Solutions Using Window Functions 

• The book has only 200 pages and it's pretty easy to scan it in order to find useful information. Good luck.

Task 5: Birthday Problem

• Itzik Ben-Gan has in his books one of the most interesting logic puzzles based on different questions like this one:  " What’s the probability that in a group of 23 randomly chosen people, at least 2 of them will have the same birthday? ".

• The answer is impressive and might seem strange. " Most people intuitively assume that the probability is very low. However, the probability that two people in a group of 23 have the same birthday happens to be greater than 50 percent (about 50.7 percent). For 60 or more people, it’s greater than 99 percent (disregarding variations in the distribution and assuming that the 365 possible birthdays are equally likely). The tricky part of the puzzle is that you need to determine the probability that any two people share the same birthday—not a specific two. "

For the exact solution and some interesting information about the birthday paradox, check
out the Wikipedia entry.

Task 4: Query for weekends extraction

• Basically, it's a query to extract the weekends in order to use only the business days. It seems a pretty easy task but after many test cases I realized that it could be difficult when not all the attention is focused on below case conditions. So, be carefully on that tightrope :)

declare @day1 datetime
declare @day2 datetime
declare @BusinessDays int

SELECT TOP 1

             @day1 = ExecStartDate ,

             @day2 = getDate()

FROM Audit

WHERE PkgName = 'MASTER PACKAGE'

ORDER BY ExecStartDate DESC

set datefirst 1  --Monday

set @BusinessDays = ( select ( datediff ( dd, @day1, @day2 )) - 

- (( datepart ( wk, @day2 ) - datepart ( wk, @day1 )) * 2 ) +

+ ( case when datepart ( dw, @day1 ) = 7 then 1 else 0 end ) -

 ( case when datepart ( dw, @day2 ) = 7 then 1 else 0 end )  as BusinessDays )

Task 3: Querying Microsoft SQL 2012 Certification

• I subscribed myself to the 70-461 exam last week. First of all, certification it's just a motivation to let you learn more and improve your skills. There are a lot of brain dumps spread in the internet with exactly some questions of the real Microsoft exams but for this one there a few. 

PART I - DATABASE ADMINISTRATION (by Ross Mistry)

1.   SQL Server 2012 Editions and Engine Enhancements
2.   High-Availability and Disaster-Recovery Enhancements
3.   Performance and Scalability
4.   Security Enhancements
5.   Programmability and Beyond-Relational Enhancements

PART II - BUSINESS INTELLIGENCE DEVELOPMENT (by Stacia Misner)

6.   Integration Services (SSIS)
7.   Data Quality Services
8.   Master Data Services
9.   Analysis Services (SSAS) and PowerPivot
10. Reporting Services (SSRS)

• I found also some interesting links with questions and some 

useful blogs: 

1. 70-461 Practice Test
2. SQL Server 2012 blog
3. T-SQL Enhancements & video
4. Preparation materials

5. Programmability Enhancements

6. Presentations
7. Developer Training Kit

Task 2: Purging database process

• One of my last task was to create a purge procedure in order to delete all data older than 1 year. The Staging Area database was 50 GB wide. Building the procedure not took me a long time, but to execute it took longer because the transaction log for database it was full caused by using the delete statement. 
• A fancy way in build this procedure is to make the delete data as a configurable parameter and update the last purge date.

CREATE TABLE [dbo].[Parameter](
[Parameter_ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Name] [varchar](100) NOT NULL,
[Value] [varchar](500) NULL,
[DataType] [varchar](100) NULL,
[Description] [varchar](500) NULL,
[Created] [datetime] NOT NULL,
[CreatedBy] [varchar](100) NOT NULL,
[Modified] [datetime] NOT NULL,
[ModifiedBy] [varchar](100) NOT NULL,
 CONSTRAINT [PK_Parameter] PRIMARY KEY CLUSTERED
([Parameter_ID] 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

• It's pretty easy to write the delete statements when you work with Audit table: The Audit table is logging all the events that are occurring in the system. It contains the auditing messages of the processes/ operation involved: What type of process is started, what is the parent, ultimate parent, execute process date.  

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_PurgeData] AS

BEGIN

SET NOCOUNT ON;

DECLARE @RetentionNumberOfDays INT  

--How many days to preserve the data in the DB before purging it.

DECLARE @LastPurgeDate DATETIME  

--Last date when the purge process was completed.

SELECT  @RetentionNumberOfDays = CAST(VALUE AS INT) 

FROM dbo.Parameter P

WHERE P.Name = 'RetentionNumberOfDays'

SELECT @LastPurgeDate = CONVERT(DATETIME, VALUE, 111) 

FROM dbo.Parameter P

WHERE P.Name = 'LastPurgeDate'

DECLARE @DeleteStagingDate DATETIME

SET  @DeleteStagingDate = DATEADD("DAY",-1 * @RetentionNumberOfDays, getdate())

DELETE Z    

FROM  dbo.[Table1] Z WITH (TABLOCKX )

INNER JOIN dbo.Audit A ON Z.AuditID = A.AuditID

WHERE (A.ExecStart < @DeleteStagingDate)

UPDATE P 

SET VALUE = CONVERT(VARCHAR, GETDATE(),111)

FROM dbo.Parameter P

WHERE P.Name = 'LastPurgeDate'

END

Task 1: Upgrade Advisor 2012

• Starting today I decided writing a BI software journal for posting interesting things from my job. Everyday we should learn something new, so it would be a "Dashboard Task" for me and maybe a resolved issue or interesting post for you. Here you can find a resume of my work. About almost a year I concentrated myself on Business Intelligence environment working with Microsoft platform.
• In the last few months I used the new SQL Server in upgrading a BI solution from SQL Server 2005. It was a hard work to convince the client that it's worth and then make the planning and estimations without knowing the risks. Searching for more reasons to SQL 2012 upgrade I discovered a tool named Upgrade Adviser 2012 which  analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after you upgrade. You need to install the kit  and then start analyze your old database from the server.                 

                           

• There is also a new feature in SQL Server 2012, a new visual timeline used to simplify the database restore process: 

About me ...

                                      

                Last year I finished the faculty of Automation and Computer Science from Technical University of Cluj- Napoca. In the last year of faculty I attended with an Erasmus scholarship for my diploma thesis in Munich,Germany. The subject was to develop a technique that enables robots to autonomously acquire models of unknown objects, thereby increasing understanding. The robots can became experts in their respective environment and share information with other robots. The main activities were to obtain the object model using OpenCV/C++ Library in Ubuntu operational system, model acquisition for textured objects based on the data collected with the Kinect sensor, image processing, objects detection and recognition. I interacted with people from all over the world working 8 hours/day in labs equipped with the most known robots like PR2. 
           There was the most interesting experience I ever had and this changed my way to see the things in a rapid technological progress. Because of independent life and limited financial resources I returned and started a Business Intelligence career in ISDC, a company specialized in software development, turn-key projects, nearshoring, outsourcing. 
           I have 1 year experience in BI working with Microsoft tools but all this passion comes from my internship experience in Tenaris,  a leading supplier of tubes and related services for the world's energy industry, where I used  LMS (Learning Management System) , a SAP (Systems,Applications,Products) platform for updating the employee database. I was also involved in a company training at Yonder, where I established the bases of SAP ERP (Enterprise Resource Planning) working in SAP Netwear IDES ECC 6.0. I have a SAP certification for ABAP workbench fundamentals and concepts. 

           As Business Intelligence developer I was involved in 3 projects: 
1. A partial development of a BI solution for the Rabobank International. In this project we used the Kimball star schema methodology to design the data warehouse. The chosen technology to implement all functionality was Microsoft SQL Server 2008 as database server, SQL Server Intergration Service as ETL tools and C# as programming language. 

The data warehouse was the central storage area in the system to store trade transaction information with complete history and store it in output database ready for exporting to the external systems. Along with the functional tables, the data warehouse had a set of utility tables to store configuration parameters, audit logs, error logs and user preferences.The Core database was designed using Data Vault ( a method of designing a database to provide historical storage of data coming in from multiple operational systems with complete tracing of where all the data in the database came from). The solution received in an input folder a set of files and whenever a file arrives it is extracted, transformed and loaded in databases. After extraction all input files are archived in an archive folder. The output files are generated when all expected files arrives and ETL process generates messages which are stored into a log table. The system is using three databases for main data storage and  two auxiliary databases: Staging: here the data suffer basic validations and transformations, Core: the central repository of the data (like a datawarehouse), Output: here the data in stored in the format expected to be in the output files. Configuration: store all parameters and  other configuration data on which the system is based. AuditAndLog: store all information regarding auditing and also all the messages which the processes generate.
The process view is  implemented by means of six interdependent processes: F2S: File to staging: Loads the data from the input files to the Staging DB, S2C: Staging to Core: Loads the data from the Staging DB to the Core DB, C2O: Core to Output: Loads the data from the Core DB to the Output DB and latter to the output files. LOG: Log messages produced to external files in a format required by the consumer systems of those file. CFA: Check File Arrival: Is checking if the expected files arrived or not, and notify if a file is not arrived in the specified time-window. PURGE: It purges old data from the  databases.

2. The second project was the development of specific modules using SQL Server 2012 for the internal BI project used to develop and monitoring the structure of the company. The source data is collected from Operational Systems (iTimeTrack, CRM, SmartOffice) and from csv files (Costs, Budgets) into a staging area. From the staging area, the data moves into a Data Vault in order to record the history of changes and moves into the Data Marts area which prepares the data for reports. The main tasks were to create Audit database structure (contains tables that are logging all the events occurring in the system), staging area database structure (SQL dynamic scripts to generate tables, primary keys, indexes and views to concatenate the table columns), Data Vault database structure (SQL scripts used to generate Hubs, Satellites and Links) and Data Mart database structure (SQL scripts to generate dimensions and facts tables), create PITs and bridge tables to improve performance of the queries, analyze the full and delta load for each table, ETL Integration: stored procedures to import the data from source to staging area, from staging area to Data Vault and from Data Vault to Data Mart, create linked servers,create SSIS packages used to import the CSV source files, package configuration and deploy, use PowerView from SharePoint to create reports. 

3. Over 5 months I was involved in a maintenance BI project of a mix of applications and technologies that are used to gather, provide access to and analyse data and information about company operations. The client was Bovemij Verzekeringen, an insurance company from Netherlands. The main tasks I had provide me with the ability to resolve database administration issues and recognize the connections between situations. There was issues like: cube deployment errors, wrong mapping of columns in cube, update links with the right values, set the protection level in order to get package access, implement business logic - create a job running if another one is correctly processed, run the job if more than 1 business day is past- add rights to users, move the project on TFS, upgrade the BI solution in order to bring performance, robustness and a better platform to improve the solution, implement a purge process to adjust the space allocation for files, code review and research in reducing SQL Server deadlocks, implement Backup and Recovery planning. 

Each project developed has an updated technical design and system architecture. 

I spent time for Querying SQL Server 2012 certification in order to be able to write complex queries. I learned how to improve the SQL performance using SQL Server Profiler and tuning advisor. 

Last update of my profile: September 2012