I just started working on a LiveLesson DVD for Addison-Wesley tentatively titled "SQL Server Fundamentals for the Accidental DBA". The DVD will focus on the essential skills required to work with SQL Server for people that are not full-time DBAs. I will keep you posted as I have more details. For more information about the LiveLesson videos, check out http://www.informit.com/promotions/promotion.aspx?promo=135366. Eric
Co-Host CS Techcast
http://www.cstechcast.com
 Digg It
The new SQL Server installation is so different, I thought I would take a second to write about it. For starters, the whole thing is now called the SQL Server Installation Center. 
This tool is broken down into seven sections which include the following: - Planning - This section gives you access to documentation and allows you to run the upgrade advisor.
- Installation - This is where the rubber meets the road. You can install stand-alone instances, clusters, add-nodes to existing clusters, and even start upgrades from 2000 or 2005.
- Maintenance - Here you can change your edition of SQL Server (that's right - change - did you install developer edition on production by accident, here you can change to to enterprise), repair an installation, or remove a node from a cluster.
- Tools - Access to a few handy tools such as the System Configuration Checker, a report to tell you what SQL Server features are installed, and an SSIS upgrade wizard.
- Resources - Links to helpful documentation on SQL Server
- Advanced - This section lets you install SQL Server based on a configuration file, perform advanced cluster prep, or complete a cluster from already cluster-prepared SQL Servers.
- Options - Allows you to specify the architecture (x86, x64, or ia64) and the location of the install media.
Enough about the Installation Center, let's take a look at the stand alone installation. I am not going to walk through step-by-step, I just want to highlight some of the new features that I think are pretty cool. This is the face of the new instillation wizard. 
The first few screens are just some checks and then the installation of the prerequisites. After that's done we get to the meat of the installation. Even here, the first few screens are pretty straight forward asking if you are installing a new instance or changing an existing instance and another screen allowing you to enter you product key. The first screen that grabbed my attention was the Instance Configuration. Here you not only specify and instance name, but you an also specify the instance ID. The instance ID is used in folder naming instead of random numbers. By default the ID will be the name of your instance. 
The next few screens are your standard drive space overview and service account set up, nothing really new here. Ah, but then we get to the Database Engine Configuration screen which abounds with new options for security, file locations, and the new FILESTREAM type. The first tab, shown below, lets you set the authentication mode and set up accounts that will be administrators of SQL Server. Gone are the days of BUILTIN\Administrators and here are the days of telling SQL Server who you want to be administrators. 
The second tab, Data Directories, allows you to set up the default locations of some common files used by SQL Server. This really gives you a level of control that was previous a registry hack, done manually, or done after the installation was complete. You can set up individual locations for the following: - Data Root
- User Database Files
- User Database Log Files
- Temp DB Data
- Temp DB Log
- Backup Files
Last but not least, the third tab allows you to enabled FILESTREAM for T-SQL access. I won't get into FILESTREAM here, but it is a feature of SQL Server 2008 worth your further investigation. That's the bulk of the installation....one more check to look for problems that might cause the installation to fail and your off and running. I know the installation wizard isn't the sexiest new thing in SQL Server 2008, but there are some neat new things that made me happy so I wanted to share my rather geeky bliss.
Digg It
Andy Warren wrote an article on SQLServerCentral.com the other day about the maximum row size in SQL Server 2005. I don't want to re-hash what Andy already said so you can see the article here: http://www.sqlservercentral.com/articles/Development/2862/. The gist is that Microsoft built SQL Server to be able to handle rows of data that grow beyond 8060-bytes. It does this by moving certain data off to other pages and leaving behind a 24-byte pointer. Well, after reading Andy's article, I got to thinking, just how many varchar(8000) columns can you stick in a table and fill up before SQL Server fills the page with pointers. I conducted a test.....first off I made a table that contained 400 varchar(8000) columns using the following, now slightly truncated, script: CREATE TABLE [dbo].[Test](
[BigColumn1] [varchar](8000),
[BigColumn2] [varchar](8000),
[BigColumn3] [varchar](8000),
....
[BigColumn398] [varchar](8000),
[BigColumn399] [varchar](8000),
[BigColumn400] [varchar](8000)
)
GO Use your imagination, or if you want, a little fill magic in Excel to generate the rest of that create table script. Running this script was not all good, SQL Server did warn me of the impending doom, much like SQL Server 2000 would do if you tried to add more than 1 varchar(8000). Warning: The table "Test" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit. Now on to the fun part, a quick insert statement to get us going: INSERT INTO Test(
BigColumn1,
BigColumn2,
BigColumn3, ...
BigColumn398,
BigColumn399,
BigColumn400)
VALUES
(
'TestD',
'TestD',
'TestD',
... 'TestD',
'TestD',
'TestD'
) And finally the update statement to really mess things up: UPDATE test SET BigColumn1 = REPLICATE ('0123456789', 800)
UPDATE test SET BigColumn2 = REPLICATE ('0123456789', 800)
UPDATE test SET BigColumn3 = REPLICATE ('0123456789', 800) ...
UPDATE test SET BigColumn272 = REPLICATE ('0123456789', 800)
UPDATE test SET BigColumn273 = REPLICATE ('0123456789', 800)
UPDATE test SET BigColumn274 = REPLICATE ('0123456789', 800) Well, you may now be asking, why didn't you run all 400 updates? Well, after 273 ran without an error, row number 274 finally died with the old familiar error: Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8064 which is greater than the allowable maximum of 8060.
The statement has been terminated. So there you have it, in case you ever had the same thought that I did about how big a row can really be.......now you know, 273 great big columns! P.S. If you are thinking of designing a 273 column table full of nothing but varchar(8000) columns, please don't. For the love of all that is tech, just stop touching SQL Server and find something else to do with your time. Eric J - www.cstechcast.com
Digg It
Josh Jones and I got to interview Kalen Delaney earlier today for our podcast. It was really a lot of fun and she provided us with a lot of great information. Come hear what she had to say on CS Techcast by visiting http://www.cstechcast.com. The interview will be released in the next few weeks, probably right after our live show from PASSCamp in Denver.
Digg It
I am easily excited, a nice calculator watch can keep me occupied for hours on end, but that aside, the new MERGE statement in SQL Server 2008 makes me a little giddy. Gone are the days of IF....THEN logic to decide whether a row needs to be inserted, updated, or deleted. The MERGE allows you to take care of the logic and the insert all in one shot. What's more, you can compare a entire record set all at once instead of going row by row. Here's is a quick example of using MERGE. MERGE tbl_address AS current_addresses USING ( SELECT customer_objid = address_label, addressline1, addressline2, city, region, country, zipcode, is_deleted FROM @addresses ) AS source_addresses(address_label, addressline1, addressline2, city, region, country, zipcode, is_deleted) ON ( current_addresses.address_label = source_addresses.address_label ) WHEN NOT MATCHED THEN INSERT (address_label, addressline1, addressline2, city, region, country, zipcode) VALUES (source_addresses.address_label, source_addresses.addressline1, source_addresses.addressline2, source_addresses.city, source_addresses.region, source_addresses.country, source_addresses.zipcode) WHEN MATCHED AND source_addresses.is_deleted = 1 THEN DELETE WHEN MATCHED THEN UPDATE SET address_label=source_addresses.address_label, addressline1=source_addresses.addressline1, addressline2=source_addresses.addressline2, city=source_addresses.city, region=source_addresses.region, country=source_addresses.country, zipcode=source_addresses.zipcode; The USING section defines the "new" data, in this case a table variable. The ON section defines the join between the new and the existing data. Finally you can have a series of MATCHED statements that do things like insert WHEN NOT MATCHED, update WHEN MATCHED, or delete WHEN MATCHED and some other values indicates delete. The possibilities are endless and the syntax is pretty clean. Have fun.
Digg It
I am blogging today from a little bar in Denver International Airport on my way to Redmond, WA, the mothership for users of Microsoft technology. I have been asked to help develop the objectives for the SQL Server 2008 MCTS Exam. I will post more when I know more, and when I know what I can share. At this point, just know that you will be able to blame me, at least in part if you don't like the new exam. I hope that won't happen, but I am just covering my bases :)
Eric
Digg It
We are starting a weekly podcast series called CS TechCast. Check back here regularly for show notes and to leave your comments on each episode. Tune in each Wednesday to http://www.cstechcast.com for new episodes.
Digg It
by Eric Johnson
New to SQL Server 2005, SQL Server Integration Services (SSIS) adds an enterprise level extract, transform, and load (ETL) tool to the ever growing suite of SQL Server tools. SSIS replaces DTS, which was only around for two releases of SQL Server and grabbed limited following. Besides being slow and a little tricky to work with, DTS didn’t offer any easy ways of debugging packages when you had problems. With SSIS comes a whole host of methods for debugging your packages; helping you to get to the root cause of your problems.
The environment in which you build SSIS packages is called the SQL Server Business Intelligence Development Studio (BIDS), which comes with the SQL Server 2005 client tools. In reality, BIDS is just a stripped down version of Visual Studio. In fact, if you install Visual Studio on the same box as BIDS, you will, from that point forward, notice that you are running Visual Studio in lieu of BIDS. Okay. So what does this mean for debugging? It means that you have the power of Visual Studio at your fingertips when you are developing your SSIS packages.
Breakpoints
One of the staples of debugging is the ability to setup breakpoints. SSIS let’s you setup two different kinds of breakpoints; on packages, tasks and containers, or inside of script objects. Once set, the execution of your SSIS package will stop at breakpoints and allow you to view the package in a paused state. When working with breakpoints, keep in mind that they can only be set on items in your control flow; you cannot setup breakpoints on data flow tasks. Let’s first look at setting up breakpoints on packages, tasks or containers.
To setup a breakpoint on a package, you must go to the Control Flow tab, right-click on the background of the package, and select Edit Breakpoints. If you want to setup a breakpoint for a task or container, right-click the task or container you are interested in and select Edit Breakpoints. In either case, you will be presented with Set Breakpoints dialog.With this dialog, you can pick the Break Condition and the Hit Count for that condition. In this example, I have a data flow task set to break when it receives the OnPreExecute event. You notice there are options for hit count also available. In this example, I am telling the package to break after the task in question receives the OnPreExecute event, which is triggered just before the task runs, 5 or more times. When you run the package, it stops and highlights the task on which the breakpoint was triggered. The breakpoint we set earlier has been triggered and you are given the yellow arrow over the active break point. At this point you have access to the all the local variables, any watch variables you have configured, as well as a view of the package’s status as of the break.
In addition to being able to setup breakpoints on packages, containers, and tasks, you can configure them inside of script tasks. To do this, you open the script in the script designer and either right-click the line you are interested in and select Breakpoint > Insert Breakpoint, or left click once in the left hand margin of the editor next to the line on which you want to break. Like before, the SSIS package will pause when your breakpoint is encountered.
The sky is the limit on using your breakpoints. You can set them up to trigger if your “error logic” is called more then a few times, or just to be able to see what the package is doing during a problem spot. These breakpoints are stripped off when you compile the package to run from Integration Services, so don’t worry about affecting the production package when it comes time to elevate.
Data Viewers
So you can setup breakpoints on your Control Flow, but what if you want to troubleshoot problems inside of a data flow? That is where Data Viewers come in handy. A Data Viewer, of which there are four flavors, can be configured on any of the Data Flow tasks. By right-clicking a path (one of the arrows between tasks) and selecting Data Viewers, you are presented with the Data Viewers section of the Data Flow Path Editor. From here, you can click Add and then select a Grid, Histogram, Scatter Plot, or Column chart to be displayed. The type of Data Viewer you pick will be largely dependent on the type of data you are viewing and what you need to know. In this case, I just want to see the data as it exists after the conditional split task. When you run the package, it will actually break at the data viewer and show you the data in the format you selected. At this point I can examine my data to ensure that things are running as expected or I can look for bad data that may be causing additional problems. In order to get the package going again, I can click the “play” button or click Detach. If I click play, the package will continue to execute, and the next time this same task is run, during a loop for example, I will get another data viewer with the current data. If I click Detach, the package will continue to run leaving my data viewer untouched. In this case, if the same task runs again in a loop, I will NOT get a new data viewer.
SSIS offers several new methods of debugging. Two of the easiest methods to use are breakpoints and data viewers. Both of these methods will offer you a lot of information about the inner workings of your packages. Data viewers and breakpoints just start to scrape the surface of debugging inside SSIS packages, but hopefully this short overview has given you enough insight to be able to get under the hood and fix some of the problems you may encounter in your packages.
Copyright 2000 by TechTarget.com, 117 Kendrick Street, Suite 800, Needham, MA 02494. Reprinted by permission of TechTarget.com.
Digg It
by Eric Johnson
SQL Server Reporting Services (SSRS) is an extremely handy tool in the SQL Server 2005 suite. You can use it to easily create, deploy, and manage reports for your entire organization. Its simplicity, however, can easily lead to a less then optimal configuration. You set it up, toss some reports on it and walk away. The problem is that you really don’t know who can see your reports. Data is extremely important to companies these days and much of that data is sensitive and should not be seen by everyone. So how can you implement SSRS in your environment and make sure that sensitive reports are only seen by the people authorized to view them? In this tip, we will cover the SSRS security model and talk about how you can leverage it to lock down your SSRS environment.
Overview
SSRS security is managed on two levels; the SSRS site and on items within the site. At each level, the tasks that users can perform are managed via roles. Roles are just groups with certain tasks assigned to that group and members of the role can perform the assigned tasks. It’s that simple. Taking some time to create appropriate roles and assign your user accordingly will ensure that your SSRS site, and the reports it houses, are secure.
Site Level Security
The first level of security is Site Level security. On the SSRS site as a whole you can manage the tasks that your users are allowed to perform. The tasks are fixed, and you need to create your roles with these fixed tasks in mind. At this level, you will be assigning “administrative” tasks to users. Standard users that just need to view reports will probably not require any permission at this level, beyond the ability to view properties and schedules and execute report definitions. Here are the tasks you can assign to roles at the site level.
Execute Report Definitions This task allows a user to run a report definition without first loading the definition on to the SSRS server. This is required if you want your users to run report definitions from applications separate from SSRS, such as the Report Builder.
Generate events Allows applications to generate events in the SSRS namespace.
Manage jobs Allows users to view the jobs that are running on your SSRS server and cancel them if need be.
Manage report server properties This task allows users to manage properties of the report server, as well as the items managed by the report server.
Manage report server security This task allows your users to view and modify the members of your system level roles.
Manage roles Users with the manage roles task are allowed to create, view, and modify the role definitions. These users can change the tasks that are assigned to your roles.
Manage shared schedules SSRS contains shared schedules that can be tied to report execution; this task will allow users to manage these schedules.
View report server properties This task lets your users view, be not change, the properties of the report server. This task is implied if the role has the Manage report server properties task assigned.
View shared schedules This task lets your users view, be not change, the shared schedules on the report server. This task is implied if the role has the Manage shared schedules task assigned
To control which users are allowed to perform each of these tasks, you first create a system level role. There are two built in system level roles when you install SSRS.
System Administrator Can manage all aspects of the SSRS site. The only tasks that cannot be performed by default is the Generate Events task. If you want administrators, or any user for that matter, to be able to perform this task, you must explicitly assign it to the appropriate role.
System User These users are allowed to view report server properties and shared schedules and execute report definitions. These tasks are assigned so that users can run reports.
To create new roles, click Site Settings in the top left corner of the Report Browser and then select Configure system-level role definitions under Security. This will open the System Roles page: to create a new role click the New Role button. This will open the New System Role page, shown below. All you have to do now is name the role, give it a description, and select all the tasks you want this role to be able to perform. When you’re done, click OK.
To assign users to your newly created role, go back to the site settings screen and select Configure site-wide security. This will open the System Role Assignments screen and you can simply click New Role Assignment to add new Windows users or groups to one of your SSRS System Level Roles.
Item Level Security
Item Level Security is managed in much the same way as site level security. You still work with roles and tasks, but the role assignment is done on a per item basis. In other words, a user in the Browser role for one folder may be in the Content Manager role on another folder. Here are the tasks that can be assigned to item level roles.
Consume reports Allows users to read report definitions. This is a fancy way to say “these users can run reports”.
Create linked reports Allows user to create links between columns in a report and another report. Users are also allowed to publish these reports to a folder.
Manage all subscriptions This task allows the user to view and manage other user’s subscriptions to an item.
Manage data sources Allows the user to create and delete shared data sources in SSRS.
Manage folders Allows the user to create and delete folders in SSRS. They can also modify the properties of existing folders.
Manage individual subscriptions This allows the user to create, view, and modify subscriptions that that user owns.
Manage models This task gives the user the rights to create, view, and modify models.
Manage reports Allows the user to create and delete reports.
Manage resources Allows users to create, modify, and delete resources in a folder. Resources are items such as shared schedules.
Set security for individual items This allows the user to manage security for reports, folders, resources, and shared data sources.
View data sources Allows user to view the properties of shared data sources.
View folders Allows user to view folders and folder properties
View models Allows users to view models and model properties
View reports This allows users to view reports in the folder hierarchy. This does not, however, allows users to run reports, for that they require the Consume Reports task.
View resources This task allows user to view resources and resources’ properties in folders.
As with system level roles, there are some built-in item level roles you can use when assigning permissions. If these roles aren’t enough, you can build additional roles, and assign users any combination of the item level tasks we just looked at. The built-in item level roles are as follows.
Browser This role is configured to allow users to view folders and reports and allows them to subscribe to reports.
Content Manager These users can manage the content of the SSRS site. This includes managing folders, reports, and resources.
My Reports This role allows users to publish reports and manage reports, folders, and resources in their My Reports folder.
Publisher This user can publish reports and manage reports, folders, and resources on the report server.
Report Builder Report Builders have access to view report definitions.
Okay, now down to the details. To manage these roles, you have several options. Item level security can be applied to a folder, report, data source, or resource. To give users permission to an item, you need to open that item and view its security properties. When you add a user, you will also need to assign a user to a role for that item. In the case of folders, the role a user is assigned at the top level folder will, by default, be inherited by other items inside that folder. You do have the ability to override security on a lower level folder of item.
At this point, the security of your SSRS server is entirely up to you. You can create different folders for each department and assign only employees in that department with access to that folder. Within each department folder, I like to create an additional folder for sensitive reports and further lock that folder down to the appropriate users. Take some time and really plan out how your reports will be placed on the server and how you want the security to look. Using SSRS there is no reason that all your reports, regardless of sensitivity can’t be stored in a single report server.
Copyright 2000 by TechTarget.com, 117 Kendrick Street, Suite 800, Needham, MA 02494. Reprinted by permission of TechTarget.com.
Digg It
Just want to let everyone know that I have received my SQL Server MVP award. Over the past year I have done a fair bit of writing and speaking as well as running the Colorado Springs SQL Server Users Group. I look forward to continuing my work with the SQL Server community in the coming years.
Eric
Digg It
Josh and I have been hard at work on Architecting Database Models for SQL Server (Addison-Wesley). With the release of this title coming so close to SQL Server 2008, we have deceided to update the book to contain all the new peices that will be relavent to 2008 and data modeling. Look for coverage of the following features:
- New Datatypes
- Spatial Databases
- Entity Data Platform
- New T-SQL Syntax
- and more....
We want to give you a complete look at database modeling in SQL Server, so that means waiting just a little while to get the book into your hands. Look for it in the Summer of 2008.
Eric
Digg It
|