Telerik

Been looking for some powerful software tools that I can produce rich .NET reports with WPF and Silverlight for a codeplex project I am working on (more details to follow soon). Over the years I have leveraged Telerik for their ASP.NET components while producing a site for Avtrak and been impressed with the RAD features and results - max impact with minimum effort. They have a nice WPF and SL toolset here that is powerful. Their documentation and support are excellent as well. Will keep you posted on the visualizations I come up with in WPF. This next gen toolset is going to be fun to work with.

SQL Server Resource Governor

Here is a podcast on the SQL Server Resource Governor and some related sample code. Shows examples of classifier functions, resource pools and groups, etc..

http://channel9.msdn.com/posts/mwilmot/SQL-Server-Resource-Governor/

 

Here is an overview of the SQL Server Resource Governor in slides and some demos.
SQL Server 2008 provides Resource Governor, a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU and memory that incoming application requests can use.

Resource Governor is configurable in SQL Server Management Studio by using Transact-SQL statements or by using Object Explorer.

Resource Governor is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

use master
GO
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

sp_configure
GO

sp_configure 'affinity mask', 1;
RECONFIGURE;
GO

CREATE FUNCTION fv1()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
    DECLARE @val sysname
    if 'User1' = SUSER_SNAME()
        SET @val = 'gSlow';
    else if 'User2' = SUSER_SNAME()
        SET @val = 'gFast';
    return @val;
END
GO

-- Create pool for monitoring
CREATE RESOURCE POOL pSlow
WITH (MAX_CPU_PERCENT = 100)

CREATE RESOURCE POOL pFast
WITH (MAX_CPU_PERCENT = 100)

-- group definitions based on users
CREATE WORKLOAD GROUP gSlow
USING pSlow

CREATE WORKLOAD GROUP gFast
USING pFast

GO

CREATE LOGIN User1 WITH PASSWORD = 'u1', CHECK_POLICY = OFF
CREATE LOGIN User2 WITH PASSWORD = 'u2', CHECK_POLICY = OFF
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.fv1)
ALTER RESOURCE GOVERNOR RECONFIGURE

go

-- verify the classification
select
    sess.session_id, sess.group_id, grps.name
from
    sys.dm_exec_sessions as sess join
    sys.dm_resource_governor_workload_groups as grps on sess.group_id = grps.group_id
where
    session_id > 50

--
ALTER RESOURCE POOL pSlow
WITH (MAX_CPU_PERCENT = 20)

ALTER RESOURCE POOL pFast
WITH (MAX_CPU_PERCENT = 80)

ALTER RESOURCE GOVERNOR RECONFIGURE

Open perfmon
Counters:
    -sql server resource pool load
    -sql server workload
    -cpu usage for pfast & pslow
-open 2 query analyzers and run the LOAD below for each user to get a sense of how the RG allocates resources to each. You can view the RG resources in SQL Server Mgmt Studio under Mgmt->Resource Governor
-1 for u1 and 1 for u2
------------------------------

---CREATE THE LOAD
set nocount on
declare @i int=100000000;
declare @s varchar(100);
declare @x float(10);

while @i > 0
begin
    select @s = @@version;
    --if (@i % 5 = 0)
        select @x=VAR(s1.object_id) FROM sys.all_columns s1 INNER JOIN sys.all_columns s2 ON s1.object_id = s2.object_id WHERE s1.name LIKE '%a%'
    set @i = @i - 1;
end

----------------------

TEAR DOWN

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=null)
ALTER RESOURCE GOVERNOR RECONFIGURE
ALTER RESOURCE GOVERNOR DISABLE
ALTER RESOURCE GOVERNOR RECONFIGURE
DROP FUNCTION fv1
DROP LOGIN User1
DROP LOGIN User2
DROP WORKLOAD GROUP gSlow
DROP WORKLOAD GROUP gFast
DROP RESOURCE POOL pSlow
DROP RESOURCE POOL pFast

SQL Server Integration Services (SSIS) High Availability (some ideas I got from Steve Howard - MSFT PFE)

Recently a topic came up on SSIS High Availability. This is not a typical SSIS configuration. Steve Howard (Microsoft PFE) has done a lot of work on this topic and has come up with some interesting ideas. I am going to try to pass on what I learned. It may not be perfect (OK, it is probably a total hackjob), but hopefully it captures something you can consider in trying HA SSIS.

Here is an interesting way to make SSIS and clustering work together in a kind of HA setup. The goal is to have the SSIS package running under SQL Server Agent create a checkpoint so that if it fails on node a, it will pick it up on node b, since the SQL Agent is cluster aware. There should be a common folder on both nodes (ie C:\SSIS) that has the SSIS package. The strategy will be to have 2 SSIS packages. One will be called SkedAtStartup and one Will be Called MainPkg. The main goal of the SkedAtStartup package is to check for the checkpoint file. If it exists, it runs the MainPkg package thru an Execute Package task. If the checkpoint file does not exist, it does not run the MainPkg thru the ExecutePackage task - it just terminates. The precedence constraint below is designed to implement the logic to fire off the ExecutePackage task if the checkpoint file is found. The MainPkg will use checkpoints for all the control flow tasks and will have the config for the CheckPointPath var, the ServerName in the connection, and the InitialCatalog (not tempdb). The SkedAtStartup will be called when the cluster fails over to node b. The strategy is to leverage the fact that the SQL Agent will failover and run the SkedAtStartUp job, and the fact that you are logging the steps that were completed so that when the SkedAtStartup runs, it will pick up where the first node left off.

1. Setup a Windows and SQL Server Cluster
2. Make sure that the SQL Agent job is running
3. Setup a common SSIS package on both nodes to run under the context of a SQL Server Agent Job
4. The package should use checkpoints. It should pick up where the node a one left off if a checkpoint file exists.
5. Here are some screenshots from Steve's email:

 

 

 

Also, Steve pointed out that you can manage multiple SSIS instances from a singe SQL Server Instance by updating the MsDtsSrvr.ini.xml file per the below:

 

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name>SQL 2008 MSDB</Name>

      <ServerName>.\SQL2K8</ServerName>

    </Folder>

    <Folder xsi:type="SqlServerFolder">

      <Name>SQL 2005 MSDB</Name>

      <ServerName>.</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>..\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

 

Here are some other posts on the topic:

http://dougbert.com/blogs/dougbert/archive/2009/04/09/ssis-and-clustering-what-you-should-do-instead.aspx

http://msdn.microsoft.com/en-us/library/bb522568.aspx

http://msdn.microsoft.com/en-us/library/ms345193.aspx

SQL Server Reporting Services High Availability

http://technet.microsoft.com/en-us/library/bb522745.aspx

SQL Server 2000-2008 Security

Here are some security features from SQL 2000-2008 that I find relevant to SQL Server Security:

 

• Transparent Data Encryption & External Key Management & Signed Modules
• Auditing
• User/Schema Separation
• Change Data Capture
• Policy Management
• DDL Triggers
• Code Access Security
• SQL Login Policies
  • Strong Passwords/Match AD

• Logon Triggers
• Stop SQL Injection with SqlCommand Object in .NET
• Module Execution Context
• Fine-Grained Permission Granularity
  • Principles-Permissions-Securables

• Grant-Revoke-Deny

• Cell-level Encryption
• Surface Area Configuration
• Configuration Manager
• Non-essential services shut down
• Authentication Modes
• Application Roles
• SQL Browser moved to service, off by default
• Windows & SQL Authentication
• Domain Logons, NETWORK SERVICE, SYSTEM Logins
• Secure by Design
• Secure by Default
• Server or Database Roles
• Principles-Permissions-Securables
• Cross Database Ownership Chaining

1. 2008 http://www.microsoft.com/sqlserver/2008/en/us/Security.aspx
2. 2005 http://www.microsoft.com/sqlserver/2005/en/us/Security.aspx
3. 2000 http://technet.microsoft.com/en-us/library/cc966456.aspx

SQL Payment Card Industry Security

http://www.parentenet.com/news/0904_whitepaper.pdf

 

SQL Server 2008 Security

http://www.microsoft.com/sqlserver/2008/en/us/security.aspx

SQL Server Security Best Practices

• 2008

http://www.microsoft.com/sqlserver/2008/en/us/Security.aspx

• 2005

http://www.microsoft.com/sqlserver/2005/en/us/Security.aspx

1. 2000

http://technet.microsoft.com/en-us/library/cc966456.aspx

http://technet.microsoft.com/en-us/cc984178.aspx

http://vyaskn.tripod.com/sql_server_security_best_practices.htm

Security Overview for DBAs

http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-security.aspx

SQL Server Common Criteria & SQL 2008

http://www.microsoft.com/sql/commoncriteria/certifications.mspx

http://www.cisecurity.org/bench_sqlserver.html

http://www.commoncriteriaportal.org/files/epfiles/0520a.pdf

SQL Server Compliance

http://www.microsoft.com/sql/compliance

http://sqlcat.com/msdnmirror/archive/2009/04/15/sql-resources-for-compliance.aspx

SQL Server Security Blog

http://blogs.msdn.com/sqlsecurity/default.aspx

Center for Security Benchmarks for SQL Server

http://www.cisecurity.org/bench_sqlserver.html

Dept. of Defense & SQL 2008 Security

http://iase.disa.mil/stigs/checklist/db_srr_checklist_sql_server_v8r1-2.pdf

Microsoft vs. Oracle DB Security

http://www.ngssoftware.com/research/papers/comparison.pdf

Implementing Row- and Cell-Level Security in SQL Server 2005

http://technet.microsoft.com/en-us/library/cc966395.aspx

Payment Industry Security Practices

http://www.parentenet.com/news/0904_whitepaper.pdf

Courseware

2787A        Designing Security for Microsoft SQL Server 2005

4614A        Designing Security for Microsoft® SQL Server 2005 Designing Security for Microsoft® SQL Server 2005

4615A        Designing Security for Microsoft SQL Server 2005

SQL Security NewsGroup

microsoft.public.sqlserver.security

Partitioned Tables and Indexes

Here is a good article on SQL Server 2005 Partitioned Tables and Indexes.

Virtual PC 2007 Console System Tray Problem Options.xml doesn't exist

I have a common Virtual PC 2007 problem with dual monitors for my SQL VMs... when I most need to use my Virtual PC software, often the console goes insane when using a dual monitor, and though I can limp along by using Virtual PC from the system tray, i can not run it from the console correctly.  I searched for an options.xml file, but it didn't seem to exist. Turns out, the options.xml file is nicely HIDDEN in the location:

C:\Documents and Settings\(YOURLOGONNAMEHERE)\Application Data\Microsoft\Virtual PC

so I have spent hours trying to find an answer to this.

I found this entry in Options.xml:

Terminal Services Configuration Settings for SQL Server Virtual Machine

Though this article is a bit outdated, it is helpful in setting up the Terminal Services settings for a SQL Server VM:

http://www.microsoft.com/technet/prodtechnol/win2kts/maintain/optimize/secw2kts.mspx

 

SharePoint and SSRS (Reporting Services) IIS Port Conflict

I have been having trouble installing SharePoint on the same Server as SQL 2005 due to port 80 conflicts. 

If you search on "SharePoint" in SQL BOL (Books On Line) there are some good links to configure both on the same machine. 

Here is some initial reasearch I have done. 

Great doc! http://blogs.msdn.com/sharepoint/attachment/4194088.ashx

http://blogs.msdn.com/sharepoint/attachment/4194088.ashx

 http://blogs.ameriteach.com/chris-randall/2007/8/17/sql-server-reporting-services-ssrs-installationconfiguration.html

http://blogs.msdn.com/sharepoint/attachment/4194088.ashx 

http://technet.microsoft.com/en-us/library/bb326358.aspx

http://technet.microsoft.com/en-us/library/bb677368.aspx

 There is also a reporting services add-in for SharePoint:

http://technet.microsoft.com/en-us/library/aa905871.aspx

http://technet.microsoft.com/en-us/library/bb677368.aspx

http://technet.microsoft.com/en-us/library/bb326358.aspx

It also may be possible to host SSRS reports in the context of a SharePoint PageViewer control, but I haven't gotten that far yet. 

 Here is the SQL BOL Content:

You can run a report server and Microsoft Windows SharePoint Services together on a single Microsoft Internet Information Services (IIS) 6.0 server as long as each service runs in a different application pool and you add the Reporting Services virtual directories to the Windows SharePoint Services list of exclusions.

Installation Recommendations

Configuration Steps for Side-by-Side Deployment

See Also

 

 

 

 

Determine SQL Server Version

Here is a nice way to determine SQL Server Version Information aside from @@version:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

AdventureWorks Sample Databases for SQL 2005

Here are the AdventureWorks Samples for SQL 2005 and 2008 .

Here is a good pointer to the AdventureWorks Sample Databases overview for SQL 2005.

 

I also found that there is a SqlRun_Tools.msi file under the setup disk  ..\Tools\Setup that willl help with the sample db install as well.

There are also .sql scripts you can run to manually install under \90\Tools\Samples

Determine Space Used By a SQL Server Database with sp_SpaceUsed

Here is a nice link about how to determine space consumed by a SQL Server database for each table by Nigel Rivett:

 

and some other useful scripts:

 

 

VHD Resizer for Virtual Server or Virtual PC

Found this tool that is good for VHD / Virtual Hard drive / Virtual Machine resizing.  I have been running SQL Server on a VM and the performace thus far is good.  The December 2007 edition of SQL Server Magazine had some good articles about virtualizing SQL Server... said a deprication of performance of 25%.  Thus far, for my test purposes, I see no major difference in performace from a standard windows server.

SQL Server 2008 (Katmai) New Features Overview

C:\Program Files\ProClarity\ProClarity Dashboard Server\Nice article on SQL 2008 (Katmai) New Features:

http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/10/15/katmai-sql-2008-the-list-of-new-features.aspx

 

I have attached a PowerPoint file that has a listing of all the new features from the public SQL Server Connect site.  You can download it from there.

 

Searching for a specific table name

Here is a way to search the Information_Schema to find all tables that have the letter 't' in them, can be helpful to search the metadata of your database schema

 

select *

from INFORMATION_SCHEMA.columns

where table_name like '%t%'

order by column_name