Microsoft Security Bulletin http://www.microsoft.com/technet/security/bulletin/ms09-004.mspx issued in February 2009 is not an issue if you have SQL Server 2005 SP3 installed. If this is problematic to be installed, due to the fact that rollback requires the uninstall and reinstall of SQL Server, a fix to the vulnerability is
USE master
GO
DENY EXECUTE ON sys.sp_replwritetovarbin TO public
GO
NOTE: don’t use this with Transactional Replication with Updateable Subscriptions
The other fairly recent security issue from July 2008 is
http://www.microsoft.com/technet/security/bulletin/ms08-040.mspx
Chaos, complexity, curiosity and database systems. A place where research meets industry
Welcome
Passionately curious about Data, Databases and Systems Complexity. Data is ubiquitous, the database universe is dichotomous (structured and unstructured), expanding and complex. Find my Database Research at SQLToolkit.co.uk . Microsoft Data Platform MVP
"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein
"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein
Friday, 22 May 2009
Tuesday, 12 May 2009
Types of patches
Quick-Fix Engineering (QFE)
The QFE is a Microsoft term for the delivery of individual service updates to its operating systems and application programs. Formerly called a hotfix, QFE can be used to describe both the method of delivering and applying a patch or fix, and also to refer to any individual fix.
General Distribution (GDR)
A GDR is typically associated with a critical patch release the product groups feels should be provided to and installed by the whole customer base. GDRs are often associated with security releases, or other critical fixes needed to mitigate a problem the majority of customers will experience when using our product.
Cumulative update package (CU)
Multiple cummulative updates are released between Service Packs and contains all of the applicable hot fixes at the time.
Service Packs (SP)
When there are a large enough collection of changes they are gathered together to be released as a service pack. A service pack can also deliver new product features.
The QFE is a Microsoft term for the delivery of individual service updates to its operating systems and application programs. Formerly called a hotfix, QFE can be used to describe both the method of delivering and applying a patch or fix, and also to refer to any individual fix.
General Distribution (GDR)
A GDR is typically associated with a critical patch release the product groups feels should be provided to and installed by the whole customer base. GDRs are often associated with security releases, or other critical fixes needed to mitigate a problem the majority of customers will experience when using our product.
Cumulative update package (CU)
Multiple cummulative updates are released between Service Packs and contains all of the applicable hot fixes at the time.
Service Packs (SP)
When there are a large enough collection of changes they are gathered together to be released as a service pack. A service pack can also deliver new product features.
Wednesday, 6 May 2009
Upgrading Sitecore databases
After upgrading existing Sitecore database the SQL Server internal stats need to be upgraded so that it is possible to query the internal tables and see the health of the sql server databases. If this is not done you receive false information and can miss the problems. Run
DBCC UPDATEUSAGE ('databasename')
DBCC UPDATEUSAGE ('databasename')
Sitecore 6 Core Databases
The new version of Sitecore has significantly less databases than in previous versions. Previous versions had 7 now there are only 3. The Core, Master and Web Databases are the only databases in the latest version of Sitecore.
scMaster
Master stores all versions of content
scCore
Core stores all Sitecore settings for editors
scWeb
Web store published version of content
The “Sitecore” and “Extranet” security databases were removed as they are now handled by the .NET security model and stored in standard tables.
The “Archive” and “Recycle bin” databases were removed as each database now has its own internal archive and recycle bin storage areas. The archive and recycle bin have also been enhanced to contain a search facility similar to the content editor.
scMaster
Master stores all versions of content
scCore
Core stores all Sitecore settings for editors
scWeb
Web store published version of content
The “Sitecore” and “Extranet” security databases were removed as they are now handled by the .NET security model and stored in standard tables.
The “Archive” and “Recycle bin” databases were removed as each database now has its own internal archive and recycle bin storage areas. The archive and recycle bin have also been enhanced to contain a search facility similar to the content editor.
Sitecore 5 Default Database
Belows lists the default databases
scArchive
Contains archived objects. Sitecore can be configured to automatically remove objects from the Master database to the Archive database on a specific date. This removes old objects from the Master database but backs up a copy of the object. You can specify the archive date in the Content Editor » Tasks » Archive Date field.
scCore
Contains the Sitecore client.
scExtranet
Contains the extranet security settings.
scMaster
Contains the site under development.
scRecycleBin
Contains deleted objects. Items can be restored via the Sitecore » Administrator Tools » Recycle Bin application
scSecurity
Contains the client security settings.
scWeb
Contains the published web site.
scArchive
Contains archived objects. Sitecore can be configured to automatically remove objects from the Master database to the Archive database on a specific date. This removes old objects from the Master database but backs up a copy of the object. You can specify the archive date in the Content Editor » Tasks » Archive Date field.
scCore
Contains the Sitecore client.
scExtranet
Contains the extranet security settings.
scMaster
Contains the site under development.
scRecycleBin
Contains deleted objects. Items can be restored via the Sitecore » Administrator Tools » Recycle Bin application
scSecurity
Contains the client security settings.
scWeb
Contains the published web site.
SQL Injection
SQL Injection is a common vulnerability which can lead to the database being disabled, the web site displaying malicious contnet or even the the database can be destroyed. . Attacks come primarily through Web applications that allow an attacker to execute their own SQL commands on the application database. The defense against SQL-injection attacks must be code-based.
Protecting your code
A few simple steps you can take to protect your Web applications from SQL-injection attacks.
-Principle of Least Privilege
The account an application uses to connect to the database should have only the privileges that application requires.
-Validate All Input
Make the input is what you expect it to be.
-Avoid Dynamic SQL
Dynamic SQL is a great tool for performing adhoc queries, but combining dynamic SQL with user input creates exposure. You should replace dynamic SQL with prepared SQL or stored procedures .
-Use Double Quotes
Replace all the single quotes that your users' input contains with double quotes. Single quotes often terminate SQL expressions and give the input more power than is necessary.
Tools to help prevent and identify SQL Injection
Ensure the SQL Server is fully patched to include all security vulnerabilities
HP Scrawlr
Scrawlr, developed by the HP Web Security Research Group in coordination with the MSRC, is short for SQL Injector and Crawler. Scrawlr will crawl a website while simultaneously analyzing the parameters of each individual web page for SQL Injection vulnerabilities. Scrawlr is lightning fast and uses our intelligent engine technology to dynamically craft SQL Injection attacks on the fly.
Download here
https://h30406.www3.hp.com/campaigns/2008/wwcampaign/1-57C4K/index.php?mcc=DNXA&jumpid=in_r11374_us/en/large/tsg/w1_0908_scrawlr_redirect/mcc_DNXA
urlscan
UrlScan version 3.0 Beta is a Microsoft security tool that restricts the types of HTTP requests that Internet Information Services (IIS) will process.
Download here
http://www.iis.net/downloads/default.aspx?tabid=34&g=6&i=1697
Microsoft Source Code Analyzer for SQL Injection tool
The tool is a static code analysis tool that helps you find SQL injection vulnerabilities in Active Server Pages (ASP) code.
Download here
http://www.microsoft.com/downloads/details.aspx?FamilyId=58A7C46E-A599-4FCB-9AB4-A4334146B6BA&displaylang=en
SQLInjectionFinder
SQLInjectionFinder (v1.5.0) Tool to help determine .asp pages targeted by recent SQL Injection attacks
Download here
http://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=WSUS&ReleaseId=13436
Protecting your code
A few simple steps you can take to protect your Web applications from SQL-injection attacks.
-Principle of Least Privilege
The account an application uses to connect to the database should have only the privileges that application requires.
-Validate All Input
Make the input is what you expect it to be.
-Avoid Dynamic SQL
Dynamic SQL is a great tool for performing adhoc queries, but combining dynamic SQL with user input creates exposure. You should replace dynamic SQL with prepared SQL or stored procedures .
-Use Double Quotes
Replace all the single quotes that your users' input contains with double quotes. Single quotes often terminate SQL expressions and give the input more power than is necessary.
Tools to help prevent and identify SQL Injection
Ensure the SQL Server is fully patched to include all security vulnerabilities
HP Scrawlr
Scrawlr, developed by the HP Web Security Research Group in coordination with the MSRC, is short for SQL Injector and Crawler. Scrawlr will crawl a website while simultaneously analyzing the parameters of each individual web page for SQL Injection vulnerabilities. Scrawlr is lightning fast and uses our intelligent engine technology to dynamically craft SQL Injection attacks on the fly.
Download here
https://h30406.www3.hp.com/campaigns/2008/wwcampaign/1-57C4K/index.php?mcc=DNXA&jumpid=in_r11374_us/en/large/tsg/w1_0908_scrawlr_redirect/mcc_DNXA
urlscan
UrlScan version 3.0 Beta is a Microsoft security tool that restricts the types of HTTP requests that Internet Information Services (IIS) will process.
Download here
http://www.iis.net/downloads/default.aspx?tabid=34&g=6&i=1697
Microsoft Source Code Analyzer for SQL Injection tool
The tool is a static code analysis tool that helps you find SQL injection vulnerabilities in Active Server Pages (ASP) code.
Download here
http://www.microsoft.com/downloads/details.aspx?FamilyId=58A7C46E-A599-4FCB-9AB4-A4334146B6BA&displaylang=en
SQLInjectionFinder
SQLInjectionFinder (v1.5.0) Tool to help determine .asp pages targeted by recent SQL Injection attacks
Download here
http://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=WSUS&ReleaseId=13436
Policy Based Management
Terminology
Facet - Defines a management area within the policy based framework for a predefined set of properties
Condition - contains a single condition that you want to enforce that evaluates to True or False; i.e. the state of a Facet
Policy - defines the permitted stated for properties of a single facet and is a set of conditions specified on the facets of a target.
Target - an entity that is managed by Policy-Based management; e.g. a database, a table, an index, etc.
Order creation
Select a facet that contains properties you want to configure
Define a condition that specifies the permitted states of the facet
Define a policy that contains the conditions and sets one of the evaltion modes
Policy evaluation modes
• On demand - The policy is evaluated only when directly ran by the administrator.
• On change: prevent - DDL triggers are used to prevent policy violations.
• On change: log only - Event notifications are used to check a policy when a change is made.
• On schedule - A SQL Agent job is used to periodically check policies for violations.
Facet - Defines a management area within the policy based framework for a predefined set of properties
Condition - contains a single condition that you want to enforce that evaluates to True or False; i.e. the state of a Facet
Policy - defines the permitted stated for properties of a single facet and is a set of conditions specified on the facets of a target.
Target - an entity that is managed by Policy-Based management; e.g. a database, a table, an index, etc.
Order creation
Select a facet that contains properties you want to configure
Define a condition that specifies the permitted states of the facet
Define a policy that contains the conditions and sets one of the evaltion modes
Policy evaluation modes
• On demand - The policy is evaluated only when directly ran by the administrator.
• On change: prevent - DDL triggers are used to prevent policy violations.
• On change: log only - Event notifications are used to check a policy when a change is made.
• On schedule - A SQL Agent job is used to periodically check policies for violations.
SQL Server Surface Area Configuration for Feature
Security configuration settings should only be enabled if you need to use the security feature. You should follow the principal of least privilege, which states that a system can be made more secure by granting a user or process only those privileges it requires.
-- To enable xp_cmdshell
- Ad Hoc Remote Queries - Disabled
- CLR Integration - Disabled. For use with .NET Assemblies on your SQL Server.
- DAC - Disabled. For Database Administrators connection.
- Database Mail - Enabled for SQL Server Agent alerting on jobs and sending emails from SQL Server
- Native XML Web Services - This would only have an option if you've configured it manually. If so, consider transitioning to a web service written in .NET deployed on IIS due to the deprecation in SQL Server 2008.
- OLE Autmation - Disabled.
- Service Broker - Disabled.
- SQL Mail - Disabled as Database Mail is the new version for SQL Mail.
- Web Assistant - Disabled.
- xp_cmdshell - Disabled.
-- To enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for -- advanced options
RECONFIGURE;
GO
-- To disable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0;
GO
-- To update the currently configured value for this -- feature
RECONFIGURE;
GO
Surface Area Configuration tool removed
The Surface Area Configuration tool has been removed from the SQL Server 2008 version. Instead the settings can now be found under facets which is part of the new Policy-Based Management.
Tuesday, 5 May 2009
Copy only backups
If you need to keep a sql server backup independent of the sequence of conventional backups it can be acheived with copy only backups. A copy-only backup will not impact the overall backup and restore procedures for the database and is created independently of the regular scheduled conventional backups.
A copy-only full backup is not supported within SQL Server Management Studio. The script below should be used:-
BACKUP DATABASE database_name
TO DISK = 'F:\oneoffbackups\database_name_date_time.bak'
WITH INIT,COPY_ONLY
A copy-only full backup is not supported within SQL Server Management Studio. The script below should be used:-
BACKUP DATABASE database_name
TO DISK = 'F:\oneoffbackups\database_name_date_time.bak'
WITH INIT,COPY_ONLY
Monday, 4 May 2009
What is a SQL Server's name
This is a helpful script to check naming within SQL Server.
select @@SERVERNAME as [@@SERVERNAME],
SERVERPROPERTY('ServerName')as [SERVERPROPERTY('ServerName')],
SERVERPROPERTY('MachineName')as [SERVERPROPERTY('MachineName')],
SERVERPROPERTY('InstanceName')as [SERVERPROPERTY('InstanceName')],
host_name()as [host_name()]
select @@SERVERNAME as [@@SERVERNAME],
SERVERPROPERTY('ServerName')as [SERVERPROPERTY('ServerName')],
SERVERPROPERTY('MachineName')as [SERVERPROPERTY('MachineName')],
SERVERPROPERTY('InstanceName')as [SERVERPROPERTY('InstanceName')],
host_name()as [host_name()]
WMI script to enable TCP settings
This script returns the status of the TCP protocol settings and changes the status to enabled
set wmi = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement")
for each prop in wmi.ExecQuery("select * " & _
"from ServerNetworkProtocol " & _
"where InstanceName = 'sqltools'")
WScript.Echo prop.ProtocolName & " - " & _
prop.ProtocolDisplayName & " " & _
prop.Enabled
next
' enable tcpip
for each changeprop in wmi.ExecQuery("select * " & _
"from ServerNetworkProtocol " & _
"where InstanceName = 'sqltools' and " & _
"ProtocolName = 'Tcp'")
changeprop.SetEnable()
next
set wmi = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement")
for each prop in wmi.ExecQuery("select * " & _
"from ServerNetworkProtocol " & _
"where InstanceName = 'sqltools'")
WScript.Echo prop.ProtocolName & " - " & _
prop.ProtocolDisplayName & " " & _
prop.Enabled
next
' enable tcpip
for each changeprop in wmi.ExecQuery("select * " & _
"from ServerNetworkProtocol " & _
"where InstanceName = 'sqltools' and " & _
"ProtocolName = 'Tcp'")
changeprop.SetEnable()
next
Create a script to backup user databases
This creates a script that will backup all user databases on the server.
USE MASTER
SELECT 'USE [' + NAME + ']'+ 'BACKUP DATABASE [' + NAME + ']'
+ 'TO DISK = ''Z:\oneoffbackups\' + NAME + '.bak'' WITH INIT'
FROM sysdatabases
WHERE dbid > 4
USE MASTER
SELECT 'USE [' + NAME + ']'+ 'BACKUP DATABASE [' + NAME + ']'
+ 'TO DISK = ''Z:\oneoffbackups\' + NAME + '.bak'' WITH INIT'
FROM sysdatabases
WHERE dbid > 4
Create a script to grant stored procedure permissions
This script creates a script which allows stored procedure permissions to be granted to the public role
SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs
The output below is the scripts to run e.g.
GRANT EXECUTE ON usp_insert_user TO PUBLIC
GRANT EXECUTE ON usp_insert_address TO PUBLIC
SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs
The output below is the scripts to run e.g.
GRANT EXECUTE ON usp_insert_user TO PUBLIC
GRANT EXECUTE ON usp_insert_address TO PUBLIC
change object owners
This script changes the owner of an object in the current database.
Note: A database user can't be deleted if it owns objects.
EXEC sp_changeobjectowner '.', 'dbo'
Note: A database user can't be deleted if it owns objects.
EXEC sp_changeobjectowner '
list all table sizes
This is a useful script for collecting table sizes with the number of rows
Print 'All table sizes with the number of rows'
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
PRINT 'Current Date Time.........: ' + convert(varchar(30),getdate(),113)
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
Print 'All table sizes with the number of rows'
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
PRINT 'Current Date Time.........: ' + convert(varchar(30),getdate(),113)
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
Sunday, 3 May 2009
SQLToolkit relaunched
I have now relaunched my website SQLtoolkit.co.uk with a new look and feel. Still loads of data to migrate from the old site to the new.
Friday, 1 May 2009
Sharepoint 2003 Backup and Restore Utilities
Tool | Purpose |
---|---|
Stsadm.exe | Makes a full-fidelity backup of site collections. |
SharePoint Migration tool (Smigrate.exe) | Backs up and restores sites and subsites. Does not make a full-fidelity backup; you might lose some customizations or settings during the process. |
SharePoint Portal Server Data Backup and Restore utility (Spsbackup.exe) | Backs up and restores all databases, except the configuration database. Restores content indexes and content sources. |
SPBackup.exe | Determines which site collections have changed and generates a batch file that backs up changed site collections using the Stsadm.exe tool. |
Sharepoint 2003 Database Recovery models
Database | Default install | Changed |
---|---|---|
Database ending with _Config_db | FULL | FULL |
Database ending with _SITE | FULL | FULL |
Database ending with _PROF | SIMPLE | FULL |
Database ending with _SERV | SIMPLE | FULL |
Full Recovery Mode is enabled because it is created by Windows SharePoint Services (_SITES and Config DB database). The other databases (_PROF and _SERV) are created by SharePoint Portal Server.
Microsoft SharePoint Portal Server 2003
The following table lists the default databases
Database | Description |
---|---|
Database ending with _Config_db | it stores the entire configuration database for the SharePoint server/farm. Anything pertaining to global configuration and which is set through the SharePoint central administration is stored in this database. |
Database ending with _SITE | it stores all the content of a specific portal. It contains the files, web pages, webs, sites, and all inter-related SharePoint infrastructure. |
Database ending with _PROF | it stores entire information about User Profiles; it also stores details of what data to gather about various User Profiles. |
Database ending with _SERV | It stores data regarding search, notification, and indexing. It stores the gatherer log information as well as the text indexes for the content that is crawled. In fact, it can be easily checked up by right-clicking on any of the database and choosing Properties. In fact, that could be a way to know how much space each function/activity of your portal is taking up |
Microsoft BizTalk Server 2004
The following table lists the databases and provides details of the ones that you must back up on a regular basis.
BAM Primary Import (BAMPrimaryImport)
This is the database where the Business Activity Monitoring tool collects the raw tracking data.
BAM Star Schema (BAMStarSchema)
This database contains the staging table, and the measure and dimension tables.
BAM Analysis (BAMAnalysis)
This database contains Business Activity Monitoring OLAP cubes for both online and offline analysis.
BAM Archive (BAMArchive)
This database archives old Business Activity data. Create a BAM Archive database to minimize the accumulation of Business Activity data in the BAM Primary Import database.
HWS Administration (BizTalkHWSDb)
This database contains all administration information related to Human Workflow Services (HWS).
BizTalk Tracking (BizTalkDTADb)
This database stores business and health monitoring data tracked by the BizTalk Server tracking engine.
BizTalk Configuration (BizTalkMgmtDb)
This database is the central meta-information store for all BizTalk Servers.
BizTalk Message Box (BizTalkMsgBoxDb)
This database stores subscription predicates. It is a host platform, where the queues and state tables for each BizTalk Server host are kept. This database also stores the messages and message properties.
Rule Engine (BizTalkRuleEngineDb)
This database is a repository for:
• Policies, which are sets of related rules.
• Vocabularies, which are collections of user-friendly, domain-specific names for data references in rules.
Credential (SSODB)
This Enterprise Single Sign-On credential database securely stores the configuration information for receive locations.
Trading Partner Management (TPM)
This database stores trading partner data for Business Activity Services (BAS).
Tracking Analysis Server (BizTalkAnalysisdb)
This database stores both business and health monitoring OLAP cubes.
BizTalk EDI (BizTalkEDIdb)
This database stores state for the electronic data interchange (EDI) feature.
Backing Up Your Databases
Backing Up Your Databases
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/operations/htm/ebiz_ops_backuprestore_inpu.asp
BAM Primary Import (BAMPrimaryImport)
This is the database where the Business Activity Monitoring tool collects the raw tracking data.
BAM Star Schema (BAMStarSchema)
This database contains the staging table, and the measure and dimension tables.
BAM Analysis (BAMAnalysis)
This database contains Business Activity Monitoring OLAP cubes for both online and offline analysis.
BAM Archive (BAMArchive)
This database archives old Business Activity data. Create a BAM Archive database to minimize the accumulation of Business Activity data in the BAM Primary Import database.
HWS Administration (BizTalkHWSDb)
This database contains all administration information related to Human Workflow Services (HWS).
BizTalk Tracking (BizTalkDTADb)
This database stores business and health monitoring data tracked by the BizTalk Server tracking engine.
BizTalk Configuration (BizTalkMgmtDb)
This database is the central meta-information store for all BizTalk Servers.
BizTalk Message Box (BizTalkMsgBoxDb)
This database stores subscription predicates. It is a host platform, where the queues and state tables for each BizTalk Server host are kept. This database also stores the messages and message properties.
Rule Engine (BizTalkRuleEngineDb)
This database is a repository for:
• Policies, which are sets of related rules.
• Vocabularies, which are collections of user-friendly, domain-specific names for data references in rules.
Credential (SSODB)
This Enterprise Single Sign-On credential database securely stores the configuration information for receive locations.
Trading Partner Management (TPM)
This database stores trading partner data for Business Activity Services (BAS).
Tracking Analysis Server (BizTalkAnalysisdb)
This database stores both business and health monitoring OLAP cubes.
BizTalk EDI (BizTalkEDIdb)
This database stores state for the electronic data interchange (EDI) feature.
Backing Up Your Databases
Backing Up Your Databases
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/operations/htm/ebiz_ops_backuprestore_inpu.asp
Microsoft SQL Server 2005 Reporting Services
SQL Server reporting services (subsequently SSRS) default install databases are:-
Database | Description |
---|---|
ReportServer | Report Server database is a SQL Server database that stores part of SSRS configuration, report definitions, report metadata, report history, cache policy, snapshots, resources, security settings, encrypted data, scheduling and delivery data, extension information and report execution log information. Note: Although users can certainly directly access databases in the SSRS catalog and directly modify objects that SSRS uses, this is not a recommended (or supported) practice. Underlying data and structures within the SSRS catalog are not guaranteed to be compatible between different releases of SSRS, service packs, or patches. |
ReportServerTempDB | This database is responsible for storing intermediate processing products, such as cached reports (to increase performance), and session and execution data. Note To store temporary snapshots in the file system, instead of the database, administrators should complete the following steps. First modify RSReportServer.config and set WebServiceUseFileShareStorage and WindowsServiceUseFileShareStorage to True. Then set FileShareStorageLocation to a fully qualified path. The default path is C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\RSTempFiles. Unlike SQL Server's tempdb, data in ReportServerTempDB survives SQL Server and Report Server restarts. Report Server periodically cleans expired and orphan data in ReportServerTempDB. All data in ReportServerTempDB can be deleted at any time with minimal or no impact. The minimal impact that a user might experience, for example, is a temporary performance reduction due to lost cache data and a loss of an execution state. The execution state is stored in the table SessionData. Loss of the execution state results in an error: "Execution 'j4j3vfblcanzv3qzcqhvml55' cannot be found (rsExecutionNotFound)." To resolve the loss of the execution state, a user would need to reopen a report. TIP SSRS does not recover deleted ReportServerTempDB or tables within this database. To quickly recover from erroneous deletions of objects in this database, keep a script or a backup of an empty ReportServerTempDB handy. |
RSExecutionLog | Additionally, the RSExecutionLog database can be added after the initial installation. This database stores additional logging information. |
Fix: sa login is not mapped to dbo
The sa login is the login of the system administrator.
Check whether the login is matched.
USE databasename
GO
SELECT u.name AS "Name", ISNULL(l.name, 'dbo is unmatched') AS "Matched Login"
FROM sysusers u
LEFT JOIN master.dbo.syslogins l ON u.sid = l.sid
WHERE u.name = 'dbo'
GO
If unmatched change the database owner.
USE databasename
EXEC sp_changedbowner 'sa'
Then fix the sa dbo mapping.
USE databasename
EXEC sp_change_users_login 'auto_fix', sa
To run a report to check username logins have matching userSID
USE databasename
EXEC sp_change_users_login 'report'
Check whether the login is matched.
USE databasename
GO
SELECT u.name AS "Name", ISNULL(l.name, 'dbo is unmatched') AS "Matched Login"
FROM sysusers u
LEFT JOIN master.dbo.syslogins l ON u.sid = l.sid
WHERE u.name = 'dbo'
GO
If unmatched change the database owner.
Then fix the sa dbo mapping.
To run a report to check username logins have matching userSID
Best Practice for Security
The white paper entitled
Best practices for setting up and maintaining security in SQL Server 2005
can be downloaded here
” http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc
Best practices for setting up and maintaining security in SQL Server 2005
can be downloaded here
” http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc
Data Dictionary
Every database should have a data dictionary. A data dictionary is a file that defines the basic organization of a database. It contains a collection of data element definitions in the database. More advanced data dictionary contains database schema with reference keys, still more advanced data dictionary contains entity-relationship model of the data elements or objects. Data dictionaries do not contain any actual data from the database, only bookkeeping information for managing it. The contents
1. Data element definitions
2. Table definitions
3. Database schema
4. Entity-relationship model of data
5. Database security model
1. Data element definitions
2. Table definitions
3. Database schema
4. Entity-relationship model of data
5. Database security model
Entity Relationship Diagrams (ERD)
Every Database should have an ER Diagram. These data models are tools used in analysis to describe the data requirements and assumptions in the system. They also set the stage for the design of databases.
There are three basic elements in ER models:
1. Entities are the "things" about which we seek information.
2. Attributes are the data we collect about the entities.
3. Relationships provide the structure needed to draw information from multiple entities.
There are three basic elements in ER models:
1. Entities are the "things" about which we seek information.
2. Attributes are the data we collect about the entities.
3. Relationships provide the structure needed to draw information from multiple entities.
RML Utilities for SQL Server
The RML utilities allow you to process SQL Server trace files and view reports showing how SQL Server is performing. For example, you can quickly see:
- Which application, database or login is using the most resources, and which queries are responsible for that
- Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed
- What queries are running slower in today's data compared to a previous set of data
Download from
http://www.microsoft.com/downloads/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en
- Which application, database or login is using the most resources, and which queries are responsible for that
- Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed
- What queries are running slower in today's data compared to a previous set of data
Download from
http://www.microsoft.com/downloads/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en
SQL Nexus Tool
SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.
Feature Highlights
Download from
http://www.codeplex.com/sqlnexus
Feature Highlights
Download from
http://www.codeplex.com/sqlnexus
XML Notepad 2007
XML Notepad 2007 provides a simple intuitive user interface for browsing and editing XML documents
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=72d6aa49-787d-4118-ba5f-4f30fe913628&displaylang=en
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=72d6aa49-787d-4118-ba5f-4f30fe913628&displaylang=en
Sysinternals Suite
The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools. These are advanced system utilities for troubleshooting.
Download from
http://technet.microsoft.com/en-us/sysinternals/bb842062.aspx
Download from
http://technet.microsoft.com/en-us/sysinternals/bb842062.aspx
SQLIOSim utility
The SQLIOSim utility replaces the SQLIOStress utility
The SQLIOSim utility has been upgraded from the SQLIOStress utility. The SQLIOSim utility more accurately simulates the I/O patterns of Microsoft SQL Server 2005, of SQL Server 2000, and of SQL Server 7.0. The I/O patterns of these versions of SQL Server resemble one another. The SQLIOStress utility has been used to test SQL Server 2005 I/O requirements for many years.
Download from
http://support.microsoft.com/kb/231619
The SQLIOSim utility has been upgraded from the SQLIOStress utility. The SQLIOSim utility more accurately simulates the I/O patterns of Microsoft SQL Server 2005, of SQL Server 2000, and of SQL Server 7.0. The I/O patterns of these versions of SQL Server resemble one another. The SQLIOStress utility has been used to test SQL Server 2005 I/O requirements for many years.
Download from
http://support.microsoft.com/kb/231619
Scriptomatic 2.0
Scriptomatic is a utility that writes WMI scripts for you.
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=09dfc342-648b-4119-b7eb-783b0f7d1178&displaylang=en
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=09dfc342-648b-4119-b7eb-783b0f7d1178&displaylang=en
Log Parser 2.2
Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files. The results of your query can be custom-formatted in text based output, or they can be persisted to more specialty targets like SQL, SYSLOG, or a chart.
Download from
http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en
Download from
http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en
LINQPad
LINQPad lets you interactively query SQL databases in LINQ.
LINQPad supports everything in C# 3.0 and Framework 3.5:
* LINQ to Objects
* LINQ to SQL
* Entity Framework
* LINQ to XML
LINQPad comes preloaded with 200 examples from the book, C# 3.0 in a Nutshell. There's no better way to experience the coolness of LINQ and functional programming.
Download from
http://www.linqpad.net/
LINQPad supports everything in C# 3.0 and Framework 3.5:
* LINQ to Objects
* LINQ to SQL
* Entity Framework
* LINQ to XML
LINQPad comes preloaded with 200 examples from the book, C# 3.0 in a Nutshell. There's no better way to experience the coolness of LINQ and functional programming.
Download from
http://www.linqpad.net/
Open DBDiff
Open DBDiff is an open source database schema comparison tool for SQL Server 2005/2008. It reports differences between two database schemas and provides a synchronization script to upgrade a database from one to the other.
Open DBDiff can synchronize
* Tables (including Table Options like vardecimal, text in row, etc.)
* Columns (including Computed Columns, XML options, Identities, etc.)
* Constraints
* Indexes (and XML Indexes)
* XML Schemas
* Table Types
* User Data Types (UDT)
* CLR Objects (Assemblies, CLR-UDT, CLR-Store Procedure, CLR-Triggers)
* Triggers (including DDL Triggers)
* Synonyms
* Schemas
* File groups
* Views
* Functions
* Store Procedures
* Partition Functions/Schemes
* Users
* Roles
Download from
http://www.codeplex.com/OpenDBiff
Open DBDiff can synchronize
* Tables (including Table Options like vardecimal, text in row, etc.)
* Columns (including Computed Columns, XML options, Identities, etc.)
* Constraints
* Indexes (and XML Indexes)
* XML Schemas
* Table Types
* User Data Types (UDT)
* CLR Objects (Assemblies, CLR-UDT, CLR-Store Procedure, CLR-Triggers)
* Triggers (including DDL Triggers)
* Synonyms
* Schemas
* File groups
* Views
* Functions
* Store Procedures
* Partition Functions/Schemes
* Users
* Roles
Download from
http://www.codeplex.com/OpenDBiff
SQL Server 2008 System Views Map
The Microsoft SQL Server 2008 System Views Map shows the key system views included in SQL Server 2008, and the relationships between them. The map is similar to the Microsoft SQL Server 2005 version and includes updates for the new and updated the Microsoft SQL Server 2008 features such as resource governor, extended events, full-text search, and others.
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=531C53E7-8A2A-4375-8F2F-5D799AA67B5C&displaylang=en
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=531C53E7-8A2A-4375-8F2F-5D799AA67B5C&displaylang=en
SQL Server 2005 System Views Map
The Microsoft SQL Server 2005 System Views Map shows the key system views included in SQL Server 2005, and the relationships between them.
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=2EC9E842-40BE-4321-9B56-92FD3860FB32&displaylang=en
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=2EC9E842-40BE-4321-9B56-92FD3860FB32&displaylang=en
SQL Server Health and History Tool (SQLH2)
The Microsoft SQL Server Health and History Tool (SQLH2) allows you to collect information from instances of SQL Server, store this information, and run reports against the data in order to determine how SQL Server is being used.
SQLH2 collects four main types of information:
1. Feature Usage – What services/features are installed, running and level of workload on the service.
2. Configuration Settings – Machine, OS and SQL configuration settings, SQL instance and database metadata.
3. Uptime of the SQL Server service
4. Performance Counters (optional) – Used to determine performance trends
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=EEDD10D6-75F7-4763-86DE-D2347B8B5F89&displaylang=en
SQLH2 collects four main types of information:
1. Feature Usage – What services/features are installed, running and level of workload on the service.
2. Configuration Settings – Machine, OS and SQL configuration settings, SQL instance and database metadata.
3. Uptime of the SQL Server service
4. Performance Counters (optional) – Used to determine performance trends
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=EEDD10D6-75F7-4763-86DE-D2347B8B5F89&displaylang=en
SQL Server 2005 Performance Dashboard Reports
The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.
Common performance problems that the dashboard reports may help to resolve include:
- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking
- Latch contention
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en
Common performance problems that the dashboard reports may help to resolve include:
- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking
- Latch contention
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en
SQLIO Disk Subsystem Benchmark Tool
SQLIO is a tool provided by Microsoft which can also be used to determine the I/O capacity of a given configuration.
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en
Internals Viewer for SQL Server
Internals Viewer is a tool for looking into the SQL Server storage engine and seeing how data is physically allocated, organised and stored.
Download from
http://internalsviewer.codeplex.com/
Download from
http://internalsviewer.codeplex.com/
SQLPing
SQLPing 3.0 performs both active and passive scans of your network in order to identify all of the SQL Server/MSDE installations in your enterprise.
Download from
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
Download from
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
SQL Server 2005 Best Practices Analyzer (BPA)
This tool collects data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en
Download from
http://www.microsoft.com/downloads/details.aspx?familyid=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en
Subscribe to:
Posts (Atom)