Category Archives: SQL SERVER 2014

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

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

 

Why you should upgrade to SQL Server 2014:

I shared my blog with my brother Zulfi Haja, he is a SQL Server Consultant too and works for an investment bank in London,UK. Having reviewed the top 5 features of SQL Server 2014, He shot me an email saying, Bro the effort you put on summarizing the top 5 features was worth reading and it’s a neat approach, would definitely recommend it to my colleagues, but I am still not convinced for an upgrade to SQL Server 2014. Can you help?

I thought only a Tech Evangelist from Microsoft will be able to answer him. I took the question from him with a little hesitation and replied back saying, “I don’t know how long will it take but surely will get back to you once I find a convincing answer”. He said, All the best!!

Was looking out for users experience with Enterprise 2014 version across my network circle and over the blogs, it was of no use. In the beginning of May I joined a local SQL Server User group and they invited me for their monthly meeting. To my surprise there was a presentation about In Memory OLTP and SQL Server 2014 by a Product Specialist from Microsoft. I said to myself, I found the guy and this Product Specialist and Tech Evangelist should be the one to answer my question. His presentation was very convincing enough and I found the answer from his talk. After the presentation the Q&A session was open and I asked him the same question and he gave me the expected answer from presentation. It is SQL Server 2014 can now support Tier1 application.

Let me elaborate this, if you have hanged out with a developer crowd from different domains they have this pre destined notion with them where they will give you an analogy, Oracle is like a business class of a flight and major critical applications are built using it and SQL Server is like an economy class where middle tiered companies uses it. In fact, of all my projects 60-70% of them will be the front end applications supported by Oracle and we had the data replicated to us in SQL Server environments for our reporting and analysis purposes. With SQL Server 2014 that pre destined notion is no more and as per my conversation with the Tech Evangelist, SQL Server 2014 will be good enough to support Tier1 applications and compete with Oracle supported systems because of its new DB engine design. Also he hinted out saying, if NASDAQ uses SQL Server why not your applications?

So Oracle folks better be aware of it :)

#JunaithHaja

Connect Any Database Permission in SQL Server 2014:

Microsoft has introduced three new permissions namely Connect Any Database, Impersonate Any Login and Select All User Securables in its SQL Server 2014 version. The CONNECT ANY DATABASE permission seemed more interesting to me from a developer perspective as we all have come across situation if we had this permission we could have avoided a sad face:-(. In our daily life we work across different servers and databases but sad reality is that the developer doesn’t has permission to access all databases across every server he works. Classic example is a developer working for marketing team having access only for a Marketing database and may not have access to Finance database. So what happens if he accidentally hits the Finance database? A dialog box opens with a message saying “The database is not accessible” this caused a sad face in me many times. The CONNECT ANY DATABASE permission is the solution for it by which it will allow the developer to connect any database in the server and will not have him access to the underlying DB objects thereby preventing the dialog box.

Let’s try demonstrating it.

I created a SQL Server Authentication login for this demonstration and name it as SqlCastTest. Its default permissions can be viewed by Right Clicking the Login and opening its Properties window under tab Securables. The Effective permissions could be seen like below.

Default Permission

Let’s try opening the database SQLCAST2014 through this login in object explorer and it will pop an error message as follows.

Error Message in SQL Server 2014

 

Let’s give CONNECT TO ANY DATABASE permission to our SqlCastTest login by executing the below script

USE [MASTER]

GO

GRANT CONNECT ANY DATABASE TO [SqlCastTest]

GO

Now let’s try connecting to the same database and it will let us connect to it as follows.

Connection Established through Connect Any Database Permission

 

To verify our drill let’s check the new permission available at Securables tab in Login,

Connect Any Database Permission Check at Login Level

See the CONNECT ANY DATABASE is effective and associated with our test login like below.Keep in mind, CONNECT ANY DATABASE will not allow access to the DB objects and it’s only a connect permission to the existing and future databases in the server.

Hope it helps:-)

#JunaithHaja

Select * into using Parallelism in SQL Server 2014

Microsoft has improved the way Select * into works in SQL Server 2014 by enabling the Select * into to run in Parallel mode.In this mode the query will run parallel across available processors providing a significant improvement in performance. I have used the Production.TransactionHistory  table from AdventureWorks database and altered the table to hold 27 million records for this illustration and named it Production.TransactionHistorySample.The database compatibility level should be set to a minimum of 110 for this feature to work.

The below code took only 4:53 minutes to execute in SQL Server 2014

Select * into using Parallelism SQL Server 2014

The same code took 11:53 minutes to run in SQL Server 2008R2. So awesome is the parallel  mode execution in SQL Server 2014. On a quicker note, a developer can make use of this feature in creating staging tables which will be very fast. Do try this feature when you get a chance and share with me your findings:-)

#JunaithHaja