Thursday, May 31, 2012

Cross Database Views and Schema Binding


One of the most interesting things I've found as a DBA is learning about features in other RDBMS platforms.  Sure we all work with and love SQL Server, but quite often there are features that you hear about that you learn about that make you go, "Wow, wouldn't that be cool!".  Sybase's ability to have multiple tempdb's, create a tempdb for a particular database to use, or create a tempdb  for a specific user login come to mind.  Oracles ability to make a metadata copy of a base table without causing locking or blocking on the original is another.  But as cool as these features are sometimes they create confusion when working with the business.  Often times you need to understand the internals of how SQL Server works to explain why a particular feature for another RDBMS platform doesn't work in SQL.


CROSS DATABASE VIEWS & SCHEMA BINDING
http://www.flickr.com/photos/incrediblehow/5714219510/

One I ran into recently was Cross Database views that allow update's and inserts on the base tables, (I'm not a Sybase guy so to my Sybase friends please feel free to correct me if I'm wrong).  I was working on creating a distributed topology for two systems that are currently intertwined, that for performance reasons we are tying to separate.  Part of the plan that was proposed was to have a number of cross database views that would allow us to avoid code changes in an application for this first phase of the project.


 So when we started to discuss the actions that would take place against these views, very quickly it was discovered that we wanted to have inserts and updates used against these views.  In SQL Server in order to update a base table from a View that View must be created specifying WITH SCHEMABINDING.  The problem with the request is that the base table and the View are in two different databases.  In SQL this doesn't work, in Sybase (which we are transitioning off of) it does.

I worked up this demo to show my friends this limitation for Views and just wanted to pass it along to you Dear Reader.

/*
First Let's Create our Database
that will hold our base table
*/
IF EXISTS(SELECT name FROM sys.databases WHERE name='test1')
BEGIN
    DROP DATABASE test1
END

CREATE DATABASE test1


/*
Now let's create our base table
*/
USE test1
GO
IF EXISTS(SELECT name FROM sys.tables WHERE name='myTable1')
BEGIN
    DROP TABLE dbo.myTable1
END
CREATE TABLE myTable1(
             myID INT IDENTITY(1,1)
             ,mychar CHAR(500) NOT NULL DEFAULT 'a'
             ,CONSTRAINT pk_myid_1 PRIMARY KEY CLUSTERED(myID)
             )

/*
Let's insert some rows
into our base table
*/
DECLARE @i INT

SET @i=0

WHILE (@i<15000)
BEGIN
    INSERT INTO dbo.mytable1
    DEFAULT VALUES
   
    SET @i=@i+1
END


/*
Now let's create our second database
that will hold our view pointing to
the base table, dbo.myTable1, in our
test1 Database
*/

IF EXISTS(SELECT name FROM sys.databases WHERE name='test2')
BEGIN
    DROP DATABASE test2
END

CREATE DATABASE test2

/*
Now let's create our view
*/
USE test2
GO
IF EXISTS(SELECT name FROM sys.objects WHERE name='v_myTable1')
BEGIN
    DROP VIEW dbo.v_myTable1
END
GO
CREATE VIEW v_myTable1
AS
SELECT
    myid
    ,mychar
FROM
    test1.dbo.myTable1
GO
  
/*
Our Regular View is created successfully
and we can do a select from it and see
that data is returned successfully
*/
SELECT
    *
FROM
    dbo.v_myTable1

 Our view returns just fine.  And if the business only wanted to perform read operations against the view, this would have met our requirements just fine.  However we need to create a view that allows updates and inserts.  
   
/*
In order to make a view that can
recieve inserts and updates we
need to re-create our view
and specify WITH SCHEMABINDING
(This will fail in a cross database view)
*/
USE test2
GO
IF EXISTS(SELECT name FROM sys.objects WHERE name='v_myTable1')
BEGIN
    DROP VIEW dbo.v_myTable1
END
GO
CREATE VIEW v_myTable1
WITH SCHEMABINDING
AS
SELECT
    myid
    ,mychar
FROM
    test1.dbo.myTable1
GO

When you run this statement it fails with the following error.

Msg 4512, Level 16, State 3, Procedure v_myTable1, Line 4
Cannot schema bind view 'v_myTable1' because name 'test1.dbo.myTable1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.


Schema Binding only allows for two part names.  This means that we can only schema bind within our database.  This also means that if we wanted to use an Indexed View on the Cross Database View, we could not do that either. 


IT ALL MAKES SENSE


http://www.flickr.com/photos/dach_art/7126010381/
If you think about it, a View is just a select statement standing by waiting to be executed.  When you allow Schema Binding you allow that view to be a pass through to the base table.  This means you would need to give one database ownership of objects within another database.  This is not how SQL Server works currently.  Objects are allocated within a database, in SQL 2012 this is taken a step further with Contained Database.

When looking at Indexed Views it becomes even clearer.  An Indexed View is essentially a Materialized View.  All of the data in the view instead of existing as a select statement is persisted to the physical disk.  The way the data is read for an index view is quicker because you are performing a seek or a scan against one object that is dependent upon its base table.

You couldn’t have a Materialized View dependent upon Base tables within another database.  If a database when offline or they entered redo and recovery at different points you could potentially have transactions that were at different states within different databases, slight chance but still the implications are head-ache inducing.

So the long and short of it, you cannot do a cross database view using Schema Binding in SQL Server.

Thanks again for stopping by.

Thanks,

Brad



Wednesday, May 16, 2012

MAGICPASS Tonight It’s All About Encryption


http://www.flickr.com/photos/shuck/3971270079/

Tonight I will be live in front of my home town crew at MagicPASS, lead by the one and only Kendal Van Dyke (@SQLDBA|Blog) sponsored by our Friends over at Idera, and located at the Disney Vacation Club in beautiful Celebration Fl.  If the fact that we are just a stone’s throw from the happiest place on earth isn’t enough of a draw for you, then add to that tonight is TACO night at MagicPASS and you’ve got a the cherry on top of the sundae. No Sundae is a Sundae without the nutty topping, and that Dear Reader is me. 

“So Balls,” you say “Nutty topping….Really… Seriously….”

Ahhh…yes, I see.  Well anyway, so tonight I will be presenting on Transparent Data Encryption Inside and Out in SQL 2012.  I’ve given this presentation a couple times before and this year I’ve given it at SQL Connections and just last week at SQL Rally.  If you are interested in this topic this is a great chance to come out and learn without having to spend big buck’s to go do it.  All we need is a little of your time.  So without further ado let’s talk shop.


TRANSPARENT DATA ENCRYPTION INSIDE AND OUT IN SQL 2012
http://www.flickr.com/photos/33398879@N00/3216153986/

Transparent Data Encryption is an important topic to learn about.  Chances are if you are a SQL Professional, at some point in time your boss is going to ask you about it.  They will ask you what the Pros and the Cons of it are and the more information you have the better.  So without further Ado here is the abstract:  (If you'd like to Download the entire contents of the presentation and the scripts Click Here for the Slides and Click Here for the Scripts)

Security is a very important part of your job and in how data is utilized.  We have many tools to make data more secure, and starting in SQL 2008 we were able to add Transparent Data Encryption to that list.  Find out What it does and What it doesn’t do, How it effects Read-Only Filegroups, Performance, Compression (Backup and Row/Page), What the X.509 Encryption Standard is and Why you should be careful of what you store and where, and other Advance Features as well as some tips on how to manage it.

I’ve been in shops where we put this on everything, and I mean EVERYTHING.  I’ve also been in shops where we decided not to go with it because of the complication of certificate management and because Physical Security on Servers, Password Management, and Database management were all divided up amongst multiple departments and not all of them would sign off on it.

There is a real world aspect to everything, technology is great but it might not be for you.  I will cover this topic under the hood, breaking out a Hex Editor to look at the un-encrypted and encrypted contents of a backup file, I will discuss and give out scripts to deal with certificate management (backups and automatic deletions), and I will talk about what TDE does and doesn’t do.  If you are interested in the topic at all this should be a lot of fun, because I love questions and this is the perfect setting to ask them.

HOPE TO SEE YOU THERE
http://www.flickr.com/photos/fisherbray/4293266407/

So if you think you might make it out, please click here toRSVP.  Going to MagicPASS is always a lot of fun, there are great people who work with SQL everyday.  

If you are a professional in North Orlando, Middle & South Orlando, Lakeland, Winter Haven, Plant City, or like my friend Dan Taylor (@DBABulldog|Blog) who drives in from Brandon FL, when you come you will see why it is so important to get involved in a local PASS SQL Server User Group.   You make local friends that can help you with your SQL problems, and maybe join one of our certification study groups, you make connections to your local SQL Community which will pay dividends down the road.

Come for the Community and the food, and stick around for the presentation because we’re going to have a fun night.

Thanks and I hope to see you there,

Brad

Thursday, May 10, 2012

SQL Rally Deck's and Demo's Up

Hello Dear Reader!  I'm coming to you live from the wonderful SQL Rally in Dallas Texas.  I have two sessions today, and the Slide Deck's and Demo's are live on the Resource Page.  Feel free to download them and see if you'd like to come join me, or download them and follow along!

"So Balls," you say "What are you presenting on?"

Glad you asked Dear Reader, and away we go!



TRANSPARENT DATA ENCRYPTION INSIDE AND OUT IN SQL 2012




The great thing about this session is even though we are using SQL 2012 99.999% of this is the same as SQL 2008 & 2008 R2.  So come and learn about TDE and leave with scripts that will help you deploy this if you are interested. I hope you'll stop by at 10:15 am I'm in room 302/303

Here's the Abstract:

Security is a very important part of your job and in how data is utilized. We have many tools to make data more secure, and starting in SQL Server 2008, we were able to add Transparent Data Encryption to that list. Find out what it does and doesn't do, how it effects read-only filegroups, performance, and compression (backup and row/page), what the X.509 encryption standard is and why you should be careful of what you store and where, and other advanced features and management tips.


SQL INTERNALS, RECOVERY MODELS, AND BACKUPS! OH MY!

This is a fun session that is all about learning.  We have demo's and we have fun, but a lot of getting better and advancing your career in SQL Server is knowing the concepts. We won't be Deep Diving but we will touch on ACID, B-Tree's, Transaction Isolation Levels, Transaction Log Internals, Recovery Models, and Backups.  I hope you'll come join me at 4:00 in room 302/303.


Here's the Abstract


The more you know about SQL Server, the more you understand how it works. SQL Server is a product we use every day, and most of us know the big concepts. At the 10,000-foot view, we know what databases, tables, and columns are. But what makes up those databases, tables, and columns? What are records, pages, extents, and allocation units? What are Full, Simple, and Bulk-Logged recovery models? What are the differences between Full, Transaction Log, Differential, and Filegroup backups? What is a piecemeal restore? This is an introduction to these concepts using SQL Server 2012. In this session, you will learn about the internal structure, recovery models, and backups and be better prepared for future learning and managing SQL Server.


WRAP IT UP


I hope you enjoy your day out here there is A LOT of SQL Learning to be had!


Thanks,


Brad

Wednesday, May 9, 2012

Thank You to the People That Get Us There



http://www.flickr.com/photos/saygoodie/4548042971/

Hello Dear Reader starting today out in Dallas Texas at the Dallas Convention Center is the Second Annual SQL Rally.  This year Sri Sridharan (@SQLRocks | Blog) and a cast of many other volunteers, click hear to read about these great volunteers, have been hard at work to put together this great event.  There are SQL MVP’s, MCM’s, the Microsoft CSS Team, and even your friendly neighborhood SQL DBA’s such as myself.

While we take a couple days to cram in as much SQL Learning and SQL Networking as we can I wanted to take a minute to say Thank You.  Chances are you have someone that you will be Thanking as well.  If we take a minute to ask the volunteers, the comities, and different SQL Community Leaders they would have someone to Thank as well.  So as we start our SQL Rally let’s start it off on the right foot by saying Thanks to the people that allow us to participate in events like these.

“So Balls,” you say, “who are you saying Thank You to?”

The most important person in the world Dear Reader, the person that gets me there so I can participate in events like these.

THE PEOPLE THAT GET YOU THERE
http://www.flickr.com/photos/shutterbri/5168715471/

When you travel you leave your home behind.  I know not an earth shattering conclusion, but when you leave your home who is there taking care of it? 

When the kids were little they didn’t want to go to sleep at night because they didn’t want to miss anything.  They wanted to stay up and play, or stay up and watch TV.  I used to tell them that while they slept the whole world stopped, and it wouldn’t start again until they woke. I wanted them to be at ease about falling asleep.  They worried what they were missing, and I didn’t want them to worry.

As adults we know that is not true.  When we sleep we know another part of the world is up and active.  In the IT world we count on this.  We expect the system that we put in place to be used while we are not looking.  The whole profit model of the internet and “Buy Now” buttons work on that philosophy, things keep running 24 hours a day.

Life is like that too.  When you are not home, who keeps the show running?  Do you have someone that you can depend on?  Someone that says to you, “Don’t worry go, I can handle this”, someone that you have absolute faith in.   Do you have someone that keeps you from worrying?


THANK YOU TO MY WIFE
http://www.flickr.com/photos/21644167@N04/4335299130/

1st and foremost I need to say a big Thank You to my wife.  We have 4 kids.  She has given me a beautiful family, worked like crazy to support my career and my crazy hours, and she does an amazing job making our house a home.  Did I mention we have 4 kids?

I’ve recently taken a new job and I’ve been traveling quite a bit.  When you do not travel for work, then events like SQL Saturday’s, SQL Rally, SQL Connections, and the PASS Summit seem like just a couple of events a year.  Just a couple times to go away.  You say things like, “This will make me better at what I do, and besides it’s not like it is forever, it’s just a week or a weekend”.  But those things start to add up.  And when you pile on traveling for work as well, it stacks up even higher.

So when I’m gone I’m not able to help if a kiddo wakes up at 2 am tossing his or her cookies all over the bed.  If there is a noise in the middle of the night, I’m not there to go looking, and it means one parent is stretched thin.  While I’m speaking I can’t kiss a scraped knee or check out a flat bicycle tire. 

There are a million little thing and big things that my wife does during the day, she has a full schedule between family, work, and school.  When you take one parent out of the picture, then that day takes on a whole new level of busy.  Make sure the boys have lunch money, let the dog out, make sure the baby’s lunch is made, let the dog back in, get the baby off to day care, let the dog out,  pay the bills, clean the house, let the dog back in, do laundry, and don’t forget to let the dog out.  And that doesn’t even include things she needs to do for herself.  

The fact that she can make it look so effortless, is a testament to just how awesome she is.  So before I head off, I need to say Thank You.  I’m very grateful, and I want to say that without you to support me I wouldn’t get to go participate in these great events that I write about, it just wouldn’t be possible.  You’ve taken such good care of me that I cannot Thank You enough.  Thank You, Thank You, Thank You.

DON’T FORGET TO SAY THANK YOU

http://www.flickr.com/photos/avardwoolaver/7137096221/
Hopefully I’ll see you when I’m are out in Dallas, but before you get there stop and think about who it is that is making your trip possible.

Chances are I’m not alone in this.  You probably have someone in your life that makes it possible for you to do the things that you do.  Someone that while you’re away, notices more than anyone else. Someone who is happy for you when you get opportunities, helps cover for you when you need it, and offers you the support you need to get the job done.  Someone that when you get excited you want to run to and share the news about <insert good news of your choice/>. 

Someone that while you’re away, notices more than anyone else.  Make sure to say Thank You.

Thanks and I'll see you out there,

Brad

Tuesday, May 8, 2012

T-SQL Tuesday #30 TOP 5 Rules for the SQL Community *Screw Ethics


Chris Shaw (@SQLShaw|Blog) is hosting T-SQL Tuesday this month.  It has been a while since I participated and this month sounded really interesting.  So time to dust off the keyboard and get back in!  So Chris What are we writing on?

For this month’s t-sql Tuesday question I wanted to highlight the need for Ethics in our industry.  Don’t consumers and business owners have to trust someone at some time with their data?  This month, take time to participate by talking about DBA ethics.  I really hope to see someone address topics such as:
·         Should we have an ethics statement?
·         Have ethics issues impacted you? What did you do about it?
·         Security Audits: how do you police what you and others are doing in the database?
·         Does a Code of Ethics mean anything to anyone? How do we as a community enforce a Code of Ethics?
·         Do you have an issue with this Code of Ethics?
·         What do you believe our Code of Ethics should say if we the SQL Server Community have one?

I think this is a good topic, and should produce a lot of interesting reading.  But Dear Reader allow me to begin with a bit of a Rant.

ETHICS ARE DEAD!  LONG LIVE ETHICS

Screw Business Ethics.

“Balls,” you say, “SCREW ETHICS?!?!?”

I understand your outrage Dear Reader, allow me to explain.

 I think Ethics may be an over-used word.  We all want something deep reaching and soul searching that will put us on the same level.  That will guide us to say this is right and this is wrong, but Ethics has begun to mean “A corporations way of covering it’s @$$ in the event of a lawsuit by telling employee’s don’t do these stupid obvious things so when you do this (or something else like it) we are covered because we have a sheet of paper that says we are ethical”. 

Ethics are supposed to be convictions, things that you hold so true that if something goes counter to them, they should sway your argument.  Ethics should go to the core of our beliefs.  Being Ethical should be a deeply individualistic kind of thing, not a group thing.  They should define YOU, they should help YOU through troubled times, and only in times of great crisis do YOU question them. 


Instead Ethics are just another tool of doing business and advertising.  Buy from this car company because we didn’t take money in the Government bail-out look at how we didn’t partake of a politically unpopular decision our Ethics.  Our Bank has been around for 130 years because we got lucky and didn’t jump when all the others did of our Ethics.  We are focused on our customers because it is really popular right now, and we are fighting tooth and nail not to lose market share of our Ethics.


Ahhh Ethics.  Ethics, Ethics, Ethics.  Ethics are important there is no denying that.  I like ethics, as a father with a whole bunch of kids I hope I’m bringing them up to be ethical and to live good lives in the future.  As a U.S. Citizen that has held positions in working with our Government that required a high level of Security Clearance I think they are essential in our work force.  As a private citizen I’ve also watched corporations use shotgun Ethics training to lighten their liability from law suits, watch as high ranking government leaders committed all kinds of unspeakable acts, and watch as religious leaders did the same or worse.

 
Ethics should be a deep part of you.  Ethics should be important to you, but they should not be something that a business can identify on a sheet of paper.  There are people out there that probably have the same ethics I do, but have a personality that would grate on my nerves.  We might not work well on a team together, even though we may vote in the same block.  Using a sheet of paper to pair me up with these folks would probably be bad.  To be honest,  I would like to see companies drop the pretend focus on Ethics. 

And as much as I would love to say that everyone involved with PASS and myself shares the same Ethics we probably do not, and I wouldn’t want to force mine on others.  I would hope that we are not so simple that a piece of paper would capture all of our Ethics either.  We probably have some core beliefs we share, but there is nothing about our job that Ethics would instill, that simply following the RULES wouldn’t put in place as well. 

It is not a great leap of faith to think that destroying data, hurting your company’s ability to do business, or risking national security is against the rules.  When I see IT individuals that burn the barn down on the way out of town I think WHY?  Why!  

I’m pretty sure they signed the Ethics form for their company.  I guess that piece of paper didn’t stop them.  Most were with their companies for years.  Striking out and getting revenge in IT is no different from doing so in real life.  If somebody makes a car accident happen because they were driving like a fool, and I get out of my car and decide to get revenge I’m probably going to jail.  There are rules against this, AHEM *assault* AHEM, just like there are Rules against malicious hacking, data theft, and Identity Theft.
 
So forgive me for the rant, but I don't like buzz words that should mean something.

 I like Rules.  Rules are simple; people can have philosophical battles on Ethics.  What is right what is wrong, what is perspective, blah blah blah.  Rules you can follow, like them or dislike them you know what they are.  “Don’t jump on the bed”-Don’t like it, but I make the kids follow it…on occasion.  “Eat your dinner before dessert” – Didn’t used to be a fan of this but 13 years and 4 kids later you don’t get dessert unless you eat your dinner. Football has rules, Baseball has rules, Heck DODGEBALL has rules.

And let us not forget Businesses are all run by Individuals.  Let the Individuals be ethical, and the business with follow, and we start by following the rules.

What we should be looking for are people that use the same Rules to govern themselves with that we use for us.  Personalities may still conflict, but as a Rule I believe in rewarding hard work and respecting intelligence.  We might not have Christmas at the family Casa together, but if we believe in the same things we can work together just fine.

And while I would love to say that there should be a common DBA or Data Personnel form of Ethics, (complete with a pledge, secret decoder ring, and Top Secret membership into our club of sorts), I would much rather have a couple rules to follow.  And I’m pretty sure anybody breaking these isn’t in the club, and gets their decoder ring taken away.

1. DON’T STEAL, COPY, OR PLAGURIZE

Blog after blog has been stolen, and the SQL people that you would have to face are legion.  You want to see a ravenous Twitter feeding, watch for the next time some IDIOT, steals a blog post, and then attempts to defend it.  You'll hear me say it again and again, the SQL Community as vast as it seems is a small one.  Blog posts take time and effort, and it is a terrible way to get your name known.

2. GIVE CREDIT WHERE CREDIT IS DUE

If you read a post and get an idea from it, make sure to give the credit where it is due.  The good folks over at SQLSkills.com are constantly doing things that I love. 

Jonathan Kehayias (@SQLPoolBoy|Blog), Joe Sack (@JosephSack|Blog), Glenn Berry (@GlennalanBerry | Blog), Paul Randal (@PaulRandal|Blog), and Kimberly Tripp (@KimberlyLTripp|Blog), in the last month I’ve used great scripts from every single one of them.  When I do I send people to their site. 

I post links to the MCM videos, MCM Videos You Should Be Watching These, I get ideas I work up a demo I put a link in the header of the script, as well as the name of the blog post, and I cite the author.  I appreciate what they do, and I use it to learn.  And I would never, ever want credit that doesn’t belong to me.  And besides see Rule 1.


3. DON’T BE A JACK@$$ RUDE

I have met so many people in the SQL Community that are just really nice people.  They make time for phone calls, they reply to emails promptly, and I’ve never met one that isn’t incredibly polite.  The SQL Community is not a large one.  There are several hundred people that you see over and over again.  And you will be amazed how polite each and every one of them are.

Maybe it is my background in sports, but being humble is something I need to work on.  My wife has been working on me for years.  I get too prideful at times, I like to compete and I like to win.  Everyone has their moment good and bad.  If you’re like me just do your best, and it will shine through.

4.  DON’T LIE

This will do nothing but hurt you and your credibility.  Remember small community, eventually we all come in contact with one another. 
Everybody starts out somewhere.  You do not need to impress anyone.  You need to be yourself.  You don’t have a certification in SQL 2003, you don’t have a MCITP in PHP, and none of us are perfect. 

I’ve locked out the production SQL Server Service account before…during the middle of the day,  as a Jr DBA I ran a profiler trace from the GUI against production (if you don’t know why this is bad, google SQL Server Side Traces and start using them), and I once crashed a cluster that….ask me and I’ll tell you in person.  The point is we’ve all done things wrong.  A lot of them taught us things that defined our careers as DBA’s.  The scratches are war wounds, and if you haven’t screwed up don’t feel like you have to invent a story to fit in.  Just remember rule #3, because one day you will screw up.

5.  HELP WHEN YOU CAN

Every rule so far has been a DON’T, I wanted one that was a DO! One of the great things about the SQL Community is that we love to help, we love to share knowledge, and we love to learn.  Depending on how long you’ve been in the game or how much you’ve been participating you will help and contribute somewhere and in some way.  You could be a community evangelist, you could be a forum moderator, you could just be the member that posts occasionally when they have time, you could be a SQL Saturday volunteer, or a conference attendee.  What you do matters, and it is what makes us a #SQLCommunity.


WRAP IT UP

Thank You for stopping by Dear Reader and putting up with my rant on Ethics.  The ones that have the most substance are the ones that you already thought were important.  Now let’s all follow the Rules and get on with the community!

Thanks Again,

Brad