Featured Script

The Voice of the DBA

The Human Cost of Managing Data

If you've ever worked in a large environment, where the numbers of nodes number in the thousands, or tens of thousands, then you know many software packages don't scale to that size very well. I've seen quite a few pieces of software that were well written, and ran well with hundreds of items, but choked miserably when the count crept past a few thousand. Typically those are edge-case sized installations, and most applications aren't designed for, or tested at, those scales. I haven't registered 1,000 instances in SSMS, but I could guess that it would not necessarily run smoothly with that many instances being examined.

Software designed at scale tends to take the "install another central node" and manage multiple central nodes individually. That quickly becomes a pain point for the humans that administer the systems as they must learn and remember where individual systems are being managed. It's not much different for hardware as well. When a system exceeds the capacity of a single node, we often find that we are adding multiple modes and managing them individually. Many backup systems work this way, adding new units to handle the additional backup space requirements.

However there's a staffing cost as well, and it's one that can be overlooked as a company grows. More systems mean more backups, more maintenance, and more failures. Even with automation and standardization, there's a physical workload increase on your staff, and also a stress level increase from managing more systems. It can be easy to overwhelm your staff, assuming they can easily handle the additional load because they have plenty of tools to do so.

Tools are required, and whether you build or buy them, you'll make use of them if you want to minimize your salary costs. However you still need to make sure you grow your staff, train them, and give them time off. The last thing you want is to overload and burn out a small staff of one or two. I'd bet that the week your staff quits or takes ill, your systems will choose to fail.

Most maintenance for SSRS is performed either in Report Manager online or within the Report Services Configuration Manager from the desktop. The SSRS Management Studio connection is certainly no replacement for either of these tools, however, several benefits can be obtained using SSMS. First, you are able to connect to the SSRS even if you are having browser issues; second, it is often quicker to just switch from using Management Studio for the Database Engine. More »

The SQL developer needs to be able to create processes for the working database by using one of the many programming objects (like functions, stored procedures, constrains, or triggers). By creating objects that talk with SQL you simply the way other programs (like applications or web pages) can interconnect. These eternal programs only need to call on the names of your programming objects by name rather than needing to submit large pieces of advance code. With SQL Queries 2012 Joes 2 Pros® Volume 4, you learn how programming objects work in SQL Server. For those of you who have read the 2008 series for the 70-433 Exam you will find a lot of the same material from the SQL 2008 book in this SQL 2012 book. This is because much of the 70-461 test covers the same material as the 70-433. I have added material that is new to the test and removed material that is no longer relevant. If you have already read this series or have already passed the 70-433 exam you may choose to read my book which covers only the changes from 70-433 to 70-461 entitled "Joes 2 Pros SQL 2012 Queries 70-461 Exam for SQL 2008 Pros".

Yesterday's Question of the Day

Yesterday's Question
(by Raul Gonzalez):

Is it a good idea to disable the [guest] database user in the [msdb] database?

Answer: No, as for some SQL Server features to work, the guest user must be enabled in the msdb database

Explanation: SQL Server Books Online recommends that you disable the guest user in every database as a best practice for securing the database server. But this recommendation does not apply to master, msdb, and tempb system databases. In order for some Microsoft SQL Server features to work, the guest user must be enabled in the msdb database.

Featured Script

EXEC master.dbo.sp_uforeach @table_name='table name or select statement',
@column_name='existing column name',
@where_clause='where clause only when tabla name is used',
@command='command with ? replace character',
@replace_character='you can define a spec character which is used for replacing the 'looping object' (default '?')
@print_command_only= 1 only print the command, 0 execute it
@print_object_name= 1 print/select the object name the script working on (def 0)
@debug=1 enable debug information

There are two options how you can provide the ‘looping objects’ which the script in the @command parameter have to run on.
First you can use an existing table name in the @table_name parameter (for example ‘sys.databases’) and in this case you can also provide a where clause without the word ‘where’ in the @where_clause parameter to filter the objects (for example ‘state = 0′).
Second, you can use a select statement with a where clause, like ‘select name from sys.databases where status = 0′. Using select statement the parameter @where_clause is ignored, but the parameter @column_name have to be provided, because the records of this column will be used as ‘looping objects’.

If you only provide the @command parameter, the stored procedure works like sp_MSforeachdb:

CLUSTERED INDEX SCAN (EmpNonPrjTime)
- I have a report that calls procedure.
Procedure is simple select from VIEW:
...
[code="sql"]FROM
vwNzEmpNonPrjTime
WHERE
co_code = @L_CO_CODE
AND ('**all**' IN (@L_ORG) OR org_code...

Restore issue
- Hi,
Any method to restore the database when the disk is full?

Restoring Differential backup issue
- Hi all,
Took Full backup
created Table1
Took Tran Log backup
Created Table2
Took Diff backup
Created Table 3
Took Tran backup
Now I am creating another database...

This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.