Category Archives: All Articles

Installing Teradata Client Tools to use with SQL Server Integration Services

The company where I work migrated their data warehousing environment to Teradata. The requirement is to download and install the Teradata Client utilities to access the newly built data warehouse and to extract data from Teradata to SQL Server using SQL Server Integration Services.

Teradata is one of top notch data warehouse DBMS products available in the market today and it’s built by consolidating data from different sources like any other data warehouse. The data stored can be used to drive analytics like tracking sales of an organization and measuring performance of a product or customer experience.

To access data stored in Teradata we need to install the Teradata Tools and Utilities (their client side product) and the .NET Data Provider for Teradata (an extension built on Microsoft’s ADO.NET platform).  This enables us to connect to a Teradata database and to load or retrieve data using SSIS in BIDS/Visual Studio.

Continue Reading the full article here

Configuring Protection Levels of an SSIS Package

We work with SSIS packages holding sensitive data like server credentials and customer information,it is always a  good practice to use Protection Level property to secure the credentials and prevent it from unauthorized access.The Protection can be set up either to an entire package or only to the part holding the sensitive data. Hence its very important for us to know where to go to enable the Protection Level while working with packages.

Thumb rule to be followed is the Package should have same protection level at Project and Package level.

It can be configured in any of the following ways

1. Project Level

Right Click at the Project level and open its Properties window, You can configure the protection level you want from the drop down available at Security part.

Protection Level in Package at Project Level

2. Package Level:

Click anywhere in the designer and open its Properties Window by pressing F4 or View -> Properties

Protection Level at Package Level

3. During Deployment:

If you haven’t configured the Protection Level, you can still configure the package before deploying it to server by this method if you host your solution in a repository say using TortoiseSVN.

Click anywhere in the designer and Go to File ->Save Copy of Package as 

http://sqlcast.com/wp-content/uploads/2014/09/Protection-level-of-Package.png

Hope it helps!!

#JunaithHaja

 

Download and Install Adventure Works 2014

Microsoft has launched Adventure Works 2014 database for its SQL Server 2014 version. Until 2012 Microsoft provided sample databases in format of mdf and ldf file downloads, a developer will download and attach the mdf and ldf file to install Adventure works 2012. It has changed the style completely with 2014, do watch the video to know about it.

Script:

USE [master]

RESTORE DATABASE AdventureWorks2014

FROM disk= ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak’

WITH MOVE ‘AdventureWorks2014_data’

TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.mdf’,

MOVE ‘AdventureWorks2014_Log’

TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.ldf’

,REPLACE

References and Dowloads: https://msftdbprodsamples.codeplex.com/releases/view/125550

 

UPDLOCK, HOLDLOCK AND NOLOCK in SQL Server 2014

I got this question in an interview where the interviewer asked me to Define any three locks I used in my career and their practical purpose.

I answered him, I have used UPDLOCK,HOLDLOCK AND NOLOCK in my career and started elaborating as follows,

UPDLOCKS are associated with DML statements like Update. It is a shared lock and needs to be converted to an Exclusive lock before the original operation occurs. Also the lock occurs at row level. When a user executes an Update statement it assigns a Shared lock on all the scanned rows in the table, when it finds the row to be updated it converts it to an Exclusive lock and update operation occurs.

HOLDLOCKS are used at table level it prevents the table from being updated by any other DML transactions like an insert or an update. HOLDLOCKS are preferable to use in situations where we need only COMMITTED READS.

NOLOCKS are opposite of HOLDLOCKS and when they are issued at a table level, it allows the user to access/read the data from a table whilst some update/ insert operations are running at the back. NOLOCKS may produce UNCOMMITTED READS and are preferred to use at the Production Environments so that the real time update is not hindered.

The Interviewer asked, How do you see the current locks in a Server?

I told him I will use the Stored proc SP_LOCK

Then he asked me how will you kill the locks?

I answered, I will use KILL Session_ID command.

Then he asked me, Is there a report you can see the status of the Locks?

I answered him, There is one in the SSMS at database level, when you right click the database and view Reports.

He asked me, Whats its name?

I told him, I can’t think the name on top of my head but will be able to tell it when I open SSMS.

He said, Its “Top Transactions By Lock Count”

I was like Gosh!

#JunaithHaja

Running SSIS Packages in 32 bit mode

The original error message will be like this,

[Excel Source from Direct [16]] Error: SSIS Error Code DTS_E_CANNOTACQUIRE CONNECTIONFROMCONNECTIONMANAGER.The AcquireConnection method call to the connection manager “Excel Connection” failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Solution will be to Open the Visual Studio/BIDS–>Right Click at Project level –> Open Properties–>SetRun64BitRunTime as False

Running SSIS package in 32 bit mode.

In Production mode the Error will be like,

The requested OLEDB provider Microsoft.ACE.OLEDB.12.0 is not registered

Solution is to Right Click the Job from SQL Server Agent–>Open Properties–>Go To Steps –> Click Edit–>Open Execution Options Tab –> Use 32 bit run time

Running SSIS package at 32bit from SQL Server AgentCommentary: I was told the above two error occurs when we have our development machine running a 64 bit OS and our SQL Server is 32 bit so we have to force it to run at 32 bit.I believed the same, but accidentally I found my Server was 64 bit when I was working on an other issue, So what’ causing this? This error happens only when a package uses any of Microsoft Office Product like Excel or Access database, to be more precise the Microsoft Products are installed as 32 bit by default in any machine, so the associated drivers with Microsoft products like ACE.OLEDB.12.0 doesn’t supports our 64 bit environment,hence we force them to run at 32 bit. 

Memory Consumption Report in SQL Server 2014

One day I had an weird error related to memory saying “There is insufficient system memory  to run the query“. I know  two things for sure when this error happened, First is this issue should be something related to the Systems Memory.Second is all data regarding memory should be residing in the sys tables.As a matter of fact I don’t want to dig into sys tables and write queries and want to view it as a report. Was researching across SQL Server Management Studio and came across this Standard Memory Consumption report which helped me on that day.

Its a server level report and you Right Click at the Server level–>Reports –> Standard Reports–>Memory Consumption.

Memory Consumption in Sql Server 2014

The report looks like,

Memory Consumption / Usage Report in SQL Server 2014

Had to clear one of the memory components to fix the issue.

Hope it helps!!

#JunaithHaja

Configure Delayed Transaction Durability in SQL Server 2014

After reading my previous article on Delayed Transaction Durability,one of my Twitter follower posted me  this question. How do we configure our server to work with Delayed Transaction Durability? Does it comes by default?

First thing to note, Delayed Transaction Durability can be applied only at Database level and not at Server level. Here is how you configure Delayed Transaction Durability in a SQL Server 2014.

Step 1: Open your SQL Server 2014 in SSMS and Right click the database you want to set this feature and open the Properties window.

Step 2: Go to Options and Set the Delayed Durability property as Allowed under Miscellaneous section.

Delayed Transaction Durability in SQL Server 2014

Now you are all set:)

Coming to his second question, By default Delayed Transaction Durability comes Disabled with SQL Server 2014 and we can set the property as “Allowed”, “Forced” or “Disabled”.

Hope it Helps!!

#JunaithHaja

 

Difference between Report Server, Report Server DB and Report Manager:

Report Server: Report Server defines the nature of installation of SQL Server Reporting Services in your organization. It comes in two modes namely native and share point mode. Native mode is the default and used in 80% of the industry and in SharePoint mode it is integrated with SharePoint. Report Server is made up of two processing engines and a group of extensions responsible for authentication, data processing, rendering and delivery options.

Report ServerDB:  Report Server database contains all properties, objects and metadata related to an SSRS unit. It can be accessed using SSMS. Most used tables from this database are Catalog, Execution Log Storage, Subscriptions and Users.

Report Manager: Report Manager is a web based tool built using ASP.NET application to view/access SSRS reports. It’s not available in SharePoint mode.

Commentary: I faced this question in one of my interview and confused the Report Server and Report Manager terminologies. Technically we will be deploying the .rdl file to the Report Manager and what’s the use of Report Server then? After digging into it came to know Report Server is implemented as a Windows Service and will run in the background as SQL Server Reporting Services. With regards to Report Server DB, Had an opportunity to work with it for an audit purpose and it has tons of data related to SSRS and we can generate useful insights from it.For further reading on this topic visit the MSDN site.

Hope it helps someone:)

#JunaithHaja