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