SQL 2000 TIPS AND TRICKS DECK AND DEMOS

Hello Dear Reader today I'm going to be presenting on SQL 2000 Upgrade tips and tricks for Pragmatic Works Training on the T's.  The slide deck and Demo's are live on the Resource Page so please head over there to grab them and let me know what you thought!

Thanks,

Brad

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

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