Cupfighter.net

Session by: Andrew Fryer

In this interactive lab I learned how to install and configure SQL 2008 on Windows 2008 R2 Core, which has the following technical advantages and characteristics:

-          Running on R2 Core instead of normal setup, limits the need of patching and maintenance of a SQL OS to the minimum R2 core components updated, minimizing the instance downtime.

-          Running several SQL instances is possible as it is on the GUI enabled full OS setup.

-          All installations and configurations needs to be done using Powershell cmdlets, which are limited to installation of .Net framework 3.5, configuring windows firewall to allow 1433 and installing base components of SQL 2008, and of course the GUI components of SQL including management studio is not to run from the R2 core and the instance needs to be managed by remote machine.

-          It makes the life really easy to prepare an R2 image with pre-installation of SQL 2008 using Sysprep for fast deployment of an SQL Server. We have reached an OS/SQL deployment time of 5 minutes in the lab.

-          Running on R2 core is has also security advantages for SQL Server instance as major windows vulnerabilities are related to higher levels and Windows 2008 core is mostly not affected.

-          It is possible to cluster an SQL instance running on R2 core.

-          This implementation is not supported by Microsoft at this moment, but several factors shows it will be supported in the near future.

The how to guide will be shortly available on Codeplex. If not, I will add an installation guide to this blog too.

Everything. Microsoft SQL Azure is the new cloud computing online services for Microsoft SQL Server launching in November 09 in the V1 level.

It was amazing to see that you are actually able to connect to an online SQL server cloud using the normal SQL management studio connecting to the database engine using the FQDN. The SQL Gateway which is manageable by an online web interface and includes all security and firewall settings for the SQL Cloud, the redirector layer which load balance T-SQL traffic and the underlying SQL Fabric which serves the cloud, is fully transparent to the SQL management studio and system administrators can manage all futures of SQL as they do in the normal in-house setups.

The V1 version is targeted for non mission critical SQL databases for middle and large organizations, in the first quarter of 2010 the V2 release will target the mission critical SQL needs and some features like snapshot and restore to the specific point of time, and using multiple database connections using USE <db> which is not available at this moment, will be added.

It is possible to use this service based on “on demand” resource needs, meaning at an specific period of time the database load can be balanced on virtually unlimited resources where the gateway billing mechanism is aware of this short recourse peak time.

There are rumors that the SQL cloud computing technology will also be available for third party organizations in the near future.

System Center Operations Manager is monitoring DB space used by default if you have set the database to a fixed size. If you are using autogrowth with unlimited file growth, your diskspace monitoring will kick in when you are running out of disk space (don’t forget to customize these settings for large disks: you don’t want to get warned on a 500GB volume when there is only 2 GB free space).
However, if you are using autogrow options with a limited size, then SCOM 2007 doesn’t generate alerts as also documented in the product knowledge tab for “DB Space Free (%)” Monitor:
This monitor checks the database percentage free space to ensure it is above the defined thresholds. A warning or error alert will be raised if it is below one of the defined thresholds.
Databases that have autogrow set will never generate an alert regardless of the reported free space.
If your database is filling up, you get the following error: Could not allocate space for object in database because the filegroup is full.
To prevent this error and get warned in advance, I have written a custom monitor that will detect databases with the settings mentioned above and monitors database free space when autogrow AND limited file size are on.
Read more…

Ever thought you had a good backup and it turned out it was missing when you needed a restore? Although a regular restore test is the least you can do, there is more. SQL Server stores backupinformation for all databases in msdb.dbo.backupset. A simple query shows you all databases that have no backup in the last two days:
SELECT DatabaseName = d.name
,LatetstBackUpDate = ISNULL(CONVERT(VARCHAR(30),MAX(b.backup_finish_date),120),’No backup’)
,’Days’ = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) / 24
,’Hours’ = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) % 24
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE d.name NOT IN (‘tempdb’,'model’)
GROUP BY d.name
HAVING ISNULL(MIN(DATEDIFF(hh,b.backup_finish_date,getdate())),999) >= 24 * 2 — change the 2 when you want to use a different amount of days
ORDER BY d.name

Wait, there is more! You don’t want to run this query every day by hand is it? Of course you can run it as a scheduled job on your database server, but even that can be a time-consuming task if you have a lot of servers. It would be better to run this query from System Center Operations Manager.

Read more…