Microsoft Windows

Use caution when upgrading SQL 2008 CTP SSIS packages to RC0

A ran across a bit of a problem when upgrading my SQL 2008  CTP 5 (November 2007) SSIS Packages to SQL 2008 RC0.  Apparently Microsoft has changed the way that they handle the script tasks within the SSIS packages.  Because of this when I edit all of the script tasks within my SSIS package the scripts were all blank.

 The official fix from Microsoft is to install the older CTP version that you edited the scripts in on another machine and open the un-updated version of the SSIS package on that machine, and copy the code for the scripts into the RC0 version of the SSIS package.

The only object which I’ve had to do this on was a .NET Script Task.  I had this problem when going from CTP 5 to CTP 6 as well as from CTP 5 to RC0.  This will not effect migrating from SQL 2005 to SQL 2008 as SQL 2005 and SQL 2008 CTP 5 do not use the same scripting engine in the back end.

Denny

Back To Basics: Logins and Users, what’s the difference?

Usually Logins and Users are words which are interchangeable with each other.  However in Microsoft SQL Server they are very different things.  Because everyone assumes that they are the same thing, it can get a little confusing.

Logins are created at the database server instance level, while uses are created at the database level.  In other words logins are used to allow a user to connect to the SQL service (also called an instance).  You can have a login defined without having access to any databases on the server.  In this case you would have a login, but no users defined.  The user is created within the database and when it’s created is mapped to a login (users can be created without mapping them to a login, but we’ll talk about that at some point in the future).  This mapping is what allows the person connecting to the instance to use resources within the database.

If the login was created directly within the database, each database would have to keep track of the usernames and passwords of everyone who needed access to the database, which would cause a security nightmare.  Using the login in each database idea, lets create a login in each database called user1.  We set the password for user1 the same on all the databases on the server.  We then backup the database, change the password for that user on all the databases, then restore the database.  We now have an out of sync password for a single database on the server.

 Because of this mapping between logins and users, if you create a SQL Login on your server and grant it rights to a database via a user then backup the database, and restore the database to another server after creating a login on the second server with the same name.  You would think that the login would have access to the database.  However you would be wrong.  This is because the SID of the login and the user are different.  You have to use the sp_change_users_login procedure to sync the user with the login.

Denny

Back To Basics: The UPDATE Statement

After you’ve inserted the data into the table, it’s time to update the data.  We do this by using the UPDATE statement.  The update statment can be used in two ways.  The first is to update a record or set of records in a single table, by simply filtering the data in the table by using values in the table.

UPDATE TableName
SET Column1 = ‘Value’
WHERE AnotherColumn = ‘AnotherValue’

A more complex update uses another table as the source of the data. This makes the UPDATE statement look like a combination of the UPDATE statement and the SELECT statement.

UPDATE TableName
SET Column2 = AnotherTable.Column3
FROM AnotherTable
WHERE TableName.Column1 = TableName.Column1

We can add joins into this as well, so that we can update more than one column from different tables at the same time.

UPDATE TableName
SET Column2 = AnotherTable.Column3,
Column3 = ThirdTable.Column2
FROM AnotherTable
JOIN ThirdTable ON AnotherTable.Column5 = ThirdTable.Column4
WHERE TableName.Column1 = TableName.Column1

I hope that you find this post useful. I encourage everyone to open up Books OnLine and read through the information on the UPDATE statement. It includes more examples, and some of the other options which are available to you.

Denny

Remote Desktop, it’s better than ever

Remote desktop is something that we’ve all used.  It’s easy to find, right there under Programs > Accessories.  But there is so much more that you can do than simply connect to a remote session with it.  If you start the program manually from the run line by running “mstsc.exe” with some switches you can do some great things.

If you add the /console switch you will be connected to the actual console of the server, not the virtual session.  This is very useful if you need to interact with a service which is running with the “Allow service to interact with desktop” option enabled.  It allow may allow you to log in if both virtual sessions are already taken by someone else.  Do keep in mind that if someone is using the physical console (ie. the actual keyboard and mouse) then you will kick them off of the machine when you log in.

If you add the /span switch your remote session will span multiple monitors.  This is very useful for people who use more than one monitor at the office (I’ve got two 20″ wide screens and it’s great to have all the desktop space for my servers as well as my workstation).

If you add the /f switch your session will be started in full screen mode.  This is handy if you usually use it in a window, but want a one time full screen connection.

If you add the /v switch you can specify on the command line which server you want to connect to.  This is very handy if you have changed the port number that the Remote Desktop service listens on for security reasons as you can also specify the port to connect to.

What a connection window of a specific size that isn’t available by dragging the slider back and forth within the GUI?  Then the /w and /h switches are perfect for you.  You can setup the window to be any size that you’d like.

I know that I use Remote Desktop everyday and these switches have made things easier and faster for me.

Denny

Webcast Recover the data, the whole data and nothing but the data you need available for viewing

The webcast which I did with Quest Software a couple of weeks ago “Recover the data, the whole data and nothing but the data you need” has had the recording posted for viewing on the Quest website.  If you missed the webcast live now is your chance to view it.

 Denny

New Paper Published: Bullet-proof Change Management – Don’t just Cross your Fingers and Hope

I’ve written a technical brief on how my company, Awareness Technologies, uses the Quest Software Change Director for SQL Server product to monitor for and track unauthorised changes to our environments.

Denny

New Article: Create an upgrade plan for your move to SQL Server 2005

I’ve published a new tip over on SearchSQLServer.com entitled “Create an upgrade plan for your move to SQL Server 2005“.

While it’s based on upgrading to SQL Server 2005 it can be used to upgrade to SQL Server 2008 as well.

Denny

Webcast: Recover the Data, the Whole Data and Nothing but the Data You Need

Coming on the heals of the extremely successful webcast “Under The Hood of SQL Server - Checking Out Internals”, Quest Software has asked me to co-present another webcast.  This one is entitled “Recover the Data, the Whole Data and Nothing but the Data You Need” and will be on March 6, 2008 at 8am Pacific (11am Eastern).  I’ve been told that so far the sign up rate has been incredible so far.

If you are interested in the product or doing object level restores (restoring one table or stored procedure, etc instead of restoring the entire database) then I highly recommend the webcast; granted I am a little biased.

See you at the webcast.

Denny

Webcast recording available online

The recorded webcast which I did a couple of weeks ago now is available for viewing on Quest Software’s Web Page.  The slide deck is also available for downloading.

 The URL is http://www.quest.com/events/listdetails.aspx?contentid=6907&searchoff=true&technology=&prod=24&prodfamily=&loc=

The original webcast was on Feb 7, 2008.  More info can be found here.

Thanks,

Denny

Getting those memory setting in SQL Server just right.

When using 64bit SQL Server getting the memory settings right is pretty easy.  Simply set the maximum to what you want and you are good to go.

Getting them right in the 32bit versions of SQL Server is a bit harder.  You have to deal with the OS level of enabling the Physical Address Extensions (PAE) and the 3GB switches.  You then need to enable AWE within SQL Server and then set the max memory setting.

If you are using Windows 2003 SP1 or later PAE will enable for you automatically.  The /3GB switch however won’t.  Since I have to add the /3GB switch I like to add the /PAE switch in there as well.  My theory is why make Windows decide to do something automatically when I can simply override the logic and turn it on every time, especially when it’s something that I’m going to want enabled every time the server boots.

 Now as to the max memory setting for SQL Server… There are pretty much two prevailing schools of thought. 

1. Give Windows between .5 and 1 Gig of memory and give SQL the rest.
2. Give SQL 75% of the physical memory and leave the rest for Windows.

I’ve tried both and both seam to work fine.  If you have less memory to work with you will probably want to stick to option 1.  When you start working with huge amounts of RAM (64 Gigs plus) is when Option 2 starts to look more workable.

These rules obviously all start to change when you have more than one instance installed as you need to balance your max memory between the instances.

If you are using less then 2 Gigs of RAM for the instance don’t enable AWE on the instance.  I’ve seen it lead to SQL Server acting strangely and performing very strangely.  When setting your max memory setting for more than one instance don’t forget to add up the max memory for all the instances and make sure to leave Windows room to work with or your server will suffer.

Don’t forget about my post on setting the min server memory setting in SQL Server.

Denny