Finding Object Dependencies In SQL Server

In a sprawling database application, it can sometime be a nightmare if you need to alter the definition of a table, for fear that you will break an object that references it, that you were unaware of. (Although this will all be detailed in your documentation of course! ;-) )

But recently I found a great DMF, which is documented on MSDN, but I had never come across, that given the name of a table, will list all of the Procedures, Functions, Views and Triggers that reference it, and you can join it back to other system object, such as sys.sql_modules to pull back other useful information, such as is the object schema_bound (which will potentially stop you altering the table, and throw an error when you run the ALTER script.)

The DMF is called sys. dm_sql_referencing_entities and the example below will list the details of all objects that depend on a table called mySchema.myTable, along with if the object is schema bound, and even the object’s definition (providing it is not encrypted).

SELECT referencing_schema_name, referencing_entity_name, is_schema_bound, [definition]

FROM sys.dm_sql_referencing_entities ('mySchema.myTable', 'OBJECT') r

INNER JOIN sys.sql_modules m

                ON r.referencing_id = m.object_id

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

SSIS - Execute Process Task Hangs When Run From Server Agent

I was faced with an interesting problem recently. I had a SSIS Package that was stored in the Package Store on an ETL Server, and run from a Server Agent job on a Clustered SQL Instance.

If the package was run in Debug mode, it worked perfectly. If it was run from Windows Scheduled Tasks on the Cluster, it also ran perfectly, but when run from Server Agent on the Cluster, it failed.

Or to be more accurate, it didn’t fail! In fact it didn’t do anything! It reached an execute process task that was calling a decompression utility, and sat there, not working, not failing, not doing anything!

Well, I tried the usual things, such as:

Adding SSIS Server to Local Intranet Zone

Turning on Allow Unsafe Files

Using "God Account" to run job

Using "God Account" to Run As on the execute process

Making the owner of the .exe the same account as the execution context of the process

Disable attachment manager

I knew the package and executable worked, so I was sure that it was a OS level setting, but what? Well, I struggled with this, with the aid of an infrastructure team and also MS Technical Support, but the answer finally came from the MSDN Forums, who suggested creating an Environment Variable called SEE_MASK_NOZONECHECKS and setting the Value to 1.

This worked perfectly, but it was an interesting situation, and wanted to share the resolution with you all.


Find my book, Pro SQL Server Administration on Amazon -

America


United Kingdom

Including Current Date In a Flat File Name - SSIS

There was a thread on MSDN Forums, which paraphrased, asked "How can I include the current Year and Month in a file name when I create a Flat File in SSIS"

Although this is a relatively straight forward task, because SSIS is so visual, it can be difficult to explain how to do things in Forum threads, so I decided to produce this short walk through video. Hope you enjoy, and find it useful...

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Find a User's Effective Permissions

There was a recent post on the MSDN forums, that paraphrased, asked, how do I find out what permissions a User has to all Objects, at all levels, including those granted by their role memberships, etc.

Now, I am assuming that this is a fairly common security auditing task, so I scanned Google to dig out a script that would do it, but to my amazement, I could not find one, so I decided to write one. It is a pretty simple script, using the fn_my_permissions function and a couple of well known system tables.

In order to run it, you will need to be able to impersonate the login that you are running it for, and of course, you will also need the appropriate permissions to the system tables.

Feel free to modify the script, but please give due credit. This will work with 2005 and 2008, but I actually wrote it in Denali, so it is defiantly forward-compatible. Enjoy...

EXECUTE AS LOGIN = 'INSERT LOGIN NAME'
 SELECT t.name,  a.subentity_name, a.permission_name
 FROM sys.objects t
 CROSS APPLY fn_my_permissions(QUOTENAME(t.name), 'OBJECT') a
 UNION ALL
 SELECT d.name, a.subentity_name, a.permission_name
 FROM sys.databases d
 CROSS APPLY fn_my_permissions(QUOTENAME(d.name), 'DATABASE') a
 UNION ALL
 SELECT @@SERVERNAME, a.subentity_name, a.permission_name
 FROM fn_my_permissions(NULL, 'SERVER') a
 ORDER BY t.name
 REVERT


Find my book, Pro SQL Server Administration on Amazon -

America


United Kingdom

Head In The Clouds

In Paul Randal's recent SQLSkills Insider e-mail, he talked briefly about the SQL Azure, and how he had not seen his Clients express a large appetite for it "yet", and that inspired me to write this post, (which I have been meaning to write for a couple of weeks), to share my thoughts on Azure.

So firstly, I just want to state my position on Cloud Computing in general, and that for the most part is very positive. The opportunity to access your applications from any part of the world, with consistent performance and a massively reduced Total Cost Of Ownership has to be exciting, and there are many applications, such as Office and even Exchange, that I feel are a perfect fit for the Cloud.

SQL on the other hand is a different matter. Does it really fit in the Cloud, and if it does, what place does it hold? Will it ever be able to host Enterprise class systems, or will in fall into the category of Virtulization, which is great in the most part, but does not fit well for SQL, especially for Enterprise Solutions?

Well, because of these concerns, and because of the current immaturity of Azure, until recently, I have shyed away from playing around with this app, and kept my focus on traditional SQL, until...

I recently spent several months working on a project to plan the up-scaling of an established 2TB local system to global 32TB Enterprise class system. The obvious choice for this system was a FastTrack Data warehouse Appliance, and I had a fantastic opportunity to do some POC work at Microsoft HQ, (Which I will blog about soon). The POC was successful, and I thought it was a done-deal, but then an instruction came from a very senior level, that we should be investigating putting the application in the Cloud.

Now my gut reaction here was wow! An opportunity to build an Enterprise class database in the Cloud, but within seconds (and luckily before speaking) my professional head suppressed my geek head and I articulated the answer that it would not be practical because of the 50GB limit in Azure. This led to the suggestion that we could shard the data across multiple instances, which is true, but seemed like trying to plug a square hole with a round peg. I knew at that point that the idea was unlikely to go further, and we would probably stay with FastTrack Solution, but I felt it prudent to subscribe to Azure and have a play with it.

So when playing with Azure, the thing that surprised me was how limited the product currently is. A lot of the limitations surround Administration, and make perfect sense, for example, when your database is in the Cloud, why would you need to worry about High Availability? There is already a 4 9s SLA with MS, just by signing up. Some of the other limitations, however, are just a bit strange. For example, you can not create Heap Tables, only Tables with Clustered Indexes. Don't get me wrong, I'm all in favour of having a CI on all tables, but it just felt like a strange limitation. I also found that quite a few useful tools, such as Service Broker are not supported, and even my favourite %%PhysLoc%% query would not compile!

According to a contact of mine, there are currently only about 20 people in the Azure product team, and as I already mentioned, the offering is still fairly immature, so I fully expect that a lot of functionality will appear over the next few months and years, so from a technical perspective, I don't feel that we are quite ready to put many systems in the Cloud "yet", but we may be soon. I also suspect that the size restrictions will be relaxed as bandwidth improves.

My main question mark around Azure (thinking Medium-Term) is more political. If you are storing client data, especially if it is from a sensitive sector, such as public service data, I can not imagine those Clients, when asking the regular question "Where exactly are you storing my data", liking the answer "Oh, it's in the Cloud..."

Now I know that this stance is silly because I would be willing to bet large amounts of money that data is much more secure in Microsoft's Cloud, than it is in the vast majority of Data Centres, but what I'm not sure, is how long it will take, or how to even bring-about that cultural change that would be required, especially as the people who will need to be convinced are likely to be non-technical.

My other question mark, is a rather self-centred one, and revolves around (if I'm honest with myself), humans not liking change, because from a corporate point of view, it is actually a positive...

According to my contact, Microsoft's medium-term SQL Server policy will be to release features first to the Cloud, because they can easily be rolled back if problematic. (This is a concern in itself for the Cloud because it will always be the most up-to-date but possibly also the most unstable platform), then to release to Appliances. (FastTrack is already on v3, PDW is out and OLTP and BI appliances are on the way), and then finally to release to the traditional market.

Now this leads me to assume, that within the medium-term, large SQL projects will either be hosted in the Cloud, or on Appliances, and both of these environments are largely pre-configured and pre-optimized, so where does that leave skilled SQL professionals, who make a living out of performing those complex optimizations that will no longer be required?

Again, this is probably a silly stance, because there are bound to be a whole different set of issues that will require highly skilled professionals. For example, just because Windows 7 is a damn site easier to work with and configure than DOS 6.1, does not mean that there is no market for Desktop Support Technicians, or Network Engineers, but it does mean that more people are able to support it, which means that the market rates are dramatically lower than they were 15 years ago.

Maybe I'm an eternal pessimist, but it is certainly food for thought? If you have any thoughts on the subject, please feel free to leave comments...


Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

SQL Server Southampton User Group 13/04/2011

I was speaking at the Southampton SQL UG tonight, and as promised, here are links to the code samples that I used, plus some more in depth articles on the subjects I spoke about. Thanks to everybody for a good night...!

Shrinking Databases

Why not to shrink a database here
The world's worst maintenance plan? here

Using %%physloc%%

How to find what page your data is stored on here
Logical Order NOT Physical Order here

Allocation Units

What Are Allocation Units here
Querying Allocation Units here


Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

The Curious Case Of The Phantom Last Log Backup!

I was extremely "busy" at work the other day. So much so, I was sat reading Paul Randal's Blog, when I came across an interesting article on how to tell if your database is really in Full Recovery, see here.

The general premise is that when you switch from Simple to Full recovery model, the model does not change until the log backup chain has been established, with a backup. This is called pseudo-simple mode, and can be recognised by a database that is in Full recovery model showing a NULL value for last_log_backup_lsn in sys.database_recovery_staus

I was a little board, so I though that I would have a look and make sure that our OLTP databases were really in Full Recovery Model, and they were, but much to my surprise I found that some of our VLDs that were in Simple Recovery Model were showing a non-NULL value in the last_log_backup_lsn column in sys.database_recovery_status catalogue view.

This confused me and I ask Paul Randal if there was a pseudo-full mode, but there is not, so why was there a last_log_backup_lsn? Well, intrigued, I kept an eye on the transaction logs of the databases involved, buy using SELECT * FROM sys.fn_dblog(NULL,NULL) which shows all log records in the active VLFs.

Day 1 - 7436 entries, day 2 - 762 entries.

This was good news! It meant that the log had been truncated, so the databases were behaving as if the were in Simple recovery model. This was a relief, as my initial fear was that they were behaving as though they were in Full, and of course, we were not taking Transaction Log backups, as we thought they were in Simple!

So the next test - Was this record of a last LSN only in the sys.database_recovery_status catalogue view or was it embedded more internally? To check this, I ran DBCC PAGE on the boot page of the database. The database ID happened to be 8, so the syntax was

DBCC TRACEON(3604)
DBCC PAGE(8,1,9,3)

Here, I turned on trace flag 3604, so that the DBCC output would be to the messages window, rather than the event log. The parameters for DBCC Page represent the Database ID, The file number, the page number and then the level of detail. The database boot page is always at page 9 of file 1 see here for more info on system pages.

The results showed that the boot page also contained the lsn and time stamp of the last log backup, so the mystery continued. Where did it come from? Unfortunately, it was a production system that I was looking at, so I was not able to play around with it too much, but after a few hours of trying different things, that I new had occurred on the production system, I managed to reproduce the symptom, with the following script:


CREATE DATABASE [lsn_test] ON  PRIMARY
USE [master]
GO
ALTER DATABASE [lsn_test] SET RECOVERY FULL WITH NO_WAIT
GO

The results were as expected...

name recovery_model_desc last_log_backup_lsn
lsn_test FULL NULL

So now...

BACKUP DATABASE [lsn_test]
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER=MSSQL\Backup\lsn_test.bak'
WITH NOFORMAT, NOINIT,  NAME = N'lsn_test-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Again, results as expected...

name recovery_model_desc last_log_backup_lsn
lsn_test FULL 25000000006200064

Next, was to switch the Database to Simple...

USE [master]
GO
ALTER DATABASE [lsn_test] SET RECOVERY SIMPLE WITH NO_WAIT
GO

Once more, predictable results...

name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE NULL

So now lets backup the database again...

BACKUP DATABASE [lsn_test] TO  DISK = N'C:\Program Files\Microsoft SQL=erver\MSSQL10.MSSQLSERVER\MSSQL\Backup\lsn_test.bak'
WITH NOFORMAT, NOINIT, 
NAME = N'lsn_test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  ST=TS = 10
GO

Once more, the correct results...

name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE NULL

So finally, what happens if we restore the database...?

RESTORE DATABASE [lsn_test] FROM  DISK = N'C:\Program Files\Microsoft =QL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\lsn_test.bak'
WITH  FILE = 2,  NOUNLOAD,  STATS = 10
GO

Ah, this doesn't look so straight forward...!

name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE 25000000011900001
So basically, SQL has picked up the last log backup LSN from the restore, but it is meaningless, and essentially a bug. So in the end, it turned out not to be a major issue, but it was certainly interesting, tracking it down, and a big thanks to Paul Randal for helping me get my head around it!


Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom