Archive for Edwin Sarmiento
Communicating Shrinking Databases to the Accidental DBAs
Posted by: | CommentsI was on IM with my former student and asking him how he was doing. During the course of the conversation, I asked if they have a full time DBA. Unfortunately, they don't and that they rely on their ERP system's tuning tool to do the performance tuning for their databases (of course, I recommended hiring my services to do it for them). One question that popped up during the conversation was shrinking the database. Most of the time, I get tempted to refer them to SQL Server MVP Tibor Karaszi's blog about why you shouldn't be shrinking your databases which I have done a lot of times when answering questions in the technical forums. But this time, I approached it differently because I was this guy's former trainer and he definitely knows that I will try to keep things as simple as possible. I started out with the concept of internal and external fragmentation, explaining what happens to the database files when you shrink them and, as auto growth kicks in due to the additional amount of data being loaded, grow back to a large file size. Imagine a room full of boxes where each box contains a bunch of items - shoes, books, other personal items, etc. Of course, a box has a fixed volume that can only contain a certain volume of items, much so occupying a certain amount of space in the room. If you need to add more items in a box full of books, what would you do? Well, you would have to get a bigger box to replace the smaller one before you can hold the additional books. So, the process of adding more books in a full box definitely requires more effort even before you can do the only task you thought about doing - storing books in a box. I bet it would take more time and effort storing books in a full box as compared to storing them in an empty one. Think of this box as your database. While we cannot auto grow boxes due to the underlying principles of nature, we can do so with databases and any files stored in the file system. However, the process of storing books in a full box is similar to adding more data in a full database. The database engine will have to grow the database even before it can add the data in it. But isn't the goal just to add records? This means that we are making the database engine do more unnecessary work than we need it to. And you wonder why database professionals complain about transactons getting blocked when the database is already full. Which is why the best practice is to allocate more than enough space on your databases so that we minimize those auto growth events during regular business hours. And this is where Tibor explained about internal fragmentation and how shrinking your databases will fragment your indexes, further hurting your database performance. Now, what about external fragmentation? Let's get back to that box analogy. If you were to replace the small box with a bigger one, wouldn't you move the other boxes around just to accomodate the new, larger box? Imagine that the smaller box is on the third column, fourth stack of the fifth row and that you have organized the boxes in the room in such a way that you have maximized the space (by the way, I'm a bit like that when arranging my personal stuff). You would then have to take the old, smaller box out of it's original location, find a place where you can place the new, larger box and, finally, store the additional books. If the new, larger box fits in the same place as your old, smaller box, then, you would have unused space in the room and, therefore, not maximizing the room space. This, from a file system and disk perspective, is called fragmentation and from the database's perspective, external fragmentation. And, because the boxes (or files for disk subsystems) are not arranged in contiguous, close-together kind of fashion, you (or the disk) will have to work harder just to get access to different boxes stored inside the room.
So, the next time somebody asks about why database shrinking is definitely not a good idea, you can tell him this story
Continuing the SharePoint Foundation 2010 Installation
Posted by: | Comments
Setup a SharePoint Foundation 2010 test environment
Posted by: | CommentsCheck out this article on how to install SharePoint Foundation 2010. I did highlight the purpose of each component and why we need to install them. Installation is pretty straightforward for a singe-server deployment so understanding what's under the hood can prove to be insightful
Your poor man’s SQL Server log shipping-PowerShell version
Posted by: | CommentsI wrote a chapter for the book based on this blog post some three years ago and one of the recommendations I did was to convert the VBScript scripts to Windows PowerShell. So, here it is.
I've converted the VBScript that does the checking of the folders containing my latest LOG backups and restoring them in NORECOVERY mode to Windows PowerShell. What is fascinating is he fact that if the process I've outlined in the blog post is followed thru, adding another database to be configured for log shipping was as easy as creating regular LOG backups via TSQL scripts or database maintenance plans - no need to add an entry for every database that will be configured. How cool was that?
So, here's the Windows PowerShell version of the custom restore LOG backup command
$logBackupFolder="your LOG backup location here or a parameter variable"
##Variable for time duration - the amount of time you need to generate and restore LOG backups
$1HourOld = [DateTime]::Now.AddHours(-1)
##Retrieve folders and files in the specified directory
foreach ($databaseName in (Get-ChildItem $logBackupFolder Where { $_.PsIsContainer }) )
{
foreach ($logName in (Get-ChildItem $logBackupFolder\$databasename where {($_.CreationTime -ge $1HourOld) -and ($_.Extension -eq ".trn")} SELECT name))
{
$logBackupPath = [System.String]::Concat("'$logBackupFolder\",$databasename,"\",$logName.Name,"'")
$restoreSQL = "RESTORE LOG $databaseName FROM DISK=$logBackupPath WITH NORECOVERY" Out-File D:\$databaseName.sql
Invoke-Expression "osql.exe -SYourSQLServerInstanceHere -E -iD:\$databaseName.sql"
}
}
You might be wondering why I am using osql.exe instead of sqlcmd.exe. Well, it's still there even if you have SQL Server 2008. Which means this script will work even for SQL Server 2000 instances as long as you install Windows PowerShell on the machine running SQL Server.
And all of that for a mere 17-liner script. I could write it even less than that but I'm a big fan of script readability. Now, that's a good enough reason to dig deeper on what Windows PowerShell has to offer
Retrieve Default SQL Server Backup Folder using PowerShell
Posted by: | Comments
SBS 2008 Monitoring Database Fills to Capacity
Posted by: | CommentsOne of the differences between SBS 2003 and SBS 2008 is how the monitoring database is handled. In SBS 2003 the database did not have a size limitation and was able to grow as large as required. In SBS 2008, this is no longer the case. SQL Express has a 4GB limit and the other day one of my clients hit it.
So how do you know when your SBS Monitoring database is full? You’ll get this error in the Application Log:
Log Name: Application
Source: MSSQL$SBSMONITORING
Date: 3/19/2010 7:36:29 AM
Event ID: 1827
Task Category: (2)
Level: Error
Keywords: Classic
User: SYSTEM
Computer: SBS2008.domain.local
Description:
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.
It’s pretty clear from the message what the problem is. So what caused it and how do we fix it?
The cause is that the database is not configured to purge old data. There is a fix for this and it turns out to the same sql script that is used for an SBS problem where the Console displays too slowly. Run this script against your SBS Monitoring database and it will only hold data for the last 90 days. So if your database isn’t completely filled up yet you’ll want to run this now.
NOTE: The following instructions and script link from from the Official SBS Blog posting SBS 2008 Console May Take Too Long to Display Alerts and Security Statuses, Display Not Available, or Crash.
- http://cid-d5fe25afb6c3615f.skydrive.live.com/self.aspx/.Public/updateSBSMonitoring.sql
- We recommend you save the file to an easy to access path, such as c:\windows\temp.
- Open Services from Administrative Tools, Services
- Accept the UAC prompt
- Find and stop the SQL Server (SBSMONITORING) service.
- Make a copy of the files in the following folder:
- C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data
- Once the file backup is completed, start the SQL Server (SBSMONITORING) service.
- Open an Administrative Command Prompt (Run As Admin)
- Type the following command, substitute the path to the updateSBSMonitoring.sql file as needed (We recommend that you do NOT copy & paste this command directly from the blog post):
- Sqlcmd -S %computername%\SBSMonitoring -E -i c:\windows\temp\updateSBSMonitoring.sql
- Success will show an output like this:
Changed database context to ‘SBSMonitoring’
(1 rows affected)
If your database is completely filled up like mine was, then you need to replace it with a clean empty one. I consulted out resident SQL expert, Edwin Sarmiento, for this advice. You’ll have to get clean empty database through one of 3 methods: Open a ticket with Third Tier, open a ticket with Microsoft, or stand up an SBS 2008 server and grab it from there.
To install your new clean database do this:
- Open an elevated SQL Server Management Studio Express
- Connect to the <servername>\SBSMonitoring database using Windows Authentication
- Expand Databases and right-click on SBS Monitoring
- Choose Tasks, then Backup
- Make a backup of the current database
- Next Choose Tasks, then Detach to detach the database from SQL
- Open Services.msc and stop the SQL Server (SBSMonitoring) and SQL Full Text Search (SBSMonitoring) services
- Open an elevated Windows Explorer and drill down to c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data and replace the SBSMonitoring and SBSMonitoring_Log files with the blank ones you obtained.
- Start the services that you stopped earlier
- Verify that you can run a report from the console
Since you have now gone to the trouble of installing a new database, to insure that this doesn’t happen again run the above SQL script on the new database.
—
So who wrote this blog and what do they do for a living anyway?
We’re Third Tier. We provide advanced Third Tier support for IT Professionals. 
Get Support 
Blog 
Twitter 
Facebook 
LinkedIN
Understanding ASP.NET session state management from a DBA’s perspective
Posted by: | CommentsWhile I no longer do .NET development nowadays, I find it hard not to use my previous background as a reference when talking about databases. One specific discussion that came up today was how ASP.NET manages session state.
In order to configure an ASP.NET application to do session state management, you need to configure the
- OFF- this means that session state is turned off for the web application
- InProc – this means that sessions are kept in memory on the web server. This is the default behavior. You can configure the cookieless attribute if you want the sessionID value in the URL.
- StateServer – this means that you are storing session state in a separate process called the ASP.NET state service
- SQLServer – well, you guessed it, it stores session state in a SQL Server database
- Custom – you can write your own custom mechanism or any other that is readily available as a session storage mechanism
What we as DBAs are concerned about is the use of the SQLServer option as a session state storage mechanism for ASP.NET. As a DBA or a developer, you can create the session state database by using the aspnet_regsql.exe utility that comes with the .NET Framework. Running this utility, by default will create the MembershipProvider database for ASP.NET security outside of Windows authentication. But that’s not what we want. We want to create the database to store the ASP.NET session state. To do that, we need to run the utility from the command-line, passing the correct parameters.
aspnet_regsql.exe -S YourSQLServerInstance -E -ssadd
This will create the database named ASPState to store the stored procedures and functions needed for session state management. But there’s a catch to this. the default will be to create the stored procedures and functions in the ASPState database, however, the tables that will store the data will be in the tempdb database. Now, as a SQL Server DBA, we all know that anything stored in the tempdb database will eventually be flushed out when the SQL Server service restarts. If you have active sessions on your ASP.NET web application, you will lose these anytime your SQL Server service is restarted. What you need to do is explicitly pass another parameter on the aspnet_regsql.exe utility to store everything in the ASPState database.
aspnet_regsql.exe -S YourSQLServerInstance -E -ssadd -sstype p
The -sstype parameter explicitly tells the utility to create the tables in the ASPState database instead of the tempdb database (the P value stands for persisted) ill survive service restarts so you need to make use of the ASPState_Job_DeleteExpiredSessions job specifically created to do this. The default behavior of this job is to run every minute which would probably be not a good idea especially as it logs all of the job execution information in the msdb database.
So, the next time you need to work with ASP.NET applications storing session state information in a SQL Server database, check this out with your web developers
Slipstream Office SharePoint Server 2007 Service Packs for Windows Server 2008 R2
Posted by: | CommentsThis article took quite a while in the making. I’ve been seeing a lot of IT Pros deploy SharePoint in their infrastructure particularly on a Windows Server 2008 platform. However, SharePoint with Service Pack 1 is not supported on a Windows Server 2008 R2 platform.
This article outlines the steps in slipstreaming service packs for SharePoint for use with Windows Server 2008 R2. I’ll work on a series of articles on how to deploy a NLB solution for SharePoint as well as implement Kerberos delegation for a SharePoint farm
Third Thursday with Third Tier: Managing SQL for SMB
Posted by: | CommentsIf you are like most SMB IT Professionals, you’re a generalist and not a SQL expert. If that’s true this then webinar is for you. Edwin Sarmiento, SQL MVP, will show you how to manage your SQL applications. It’s not as simple as hoping your server backup will cover it and if you’re an SBS or EBS admin you’ve got a bunch of SQL apps installed by default to deal with.
So join us on the Third Thursday, March 18th at Noon eastern.
Third Tier has invited you to attend an online meeting using
Microsoft Office Live Meeting.
https://www.livemeeting.com/cc/harborcomputerservices/join?id=B4Z6S5&role=attend&pw=j%7En5%23%22rQr
Meeting time: Mar 18, 2010 12:00 PM (EST)
Add to my Outlook Calendar:
https://www.livemeeting.com/cc/harborcomputerservices/meetingICS?id=B4Z6S5&role=attend&pw=j%7En5%23%22rQr&i=i.ics
Connecting to SQL Server via Windows PowerShell with SQL Server authentication
Posted by: | CommentsHere's an article I wrote on how to use Windows PowerShell to connect to SQL Server via mixed mode authentication
