Microsoft SQL Server

Microsoft SQL Server Blog - Announcing the Modern Servicing Model for SQL Server Published Mar 23 2019 04:49 PM

Blogs

Microsoft SQL Server Development Customer Advisory Team zie ook:sqlCAT
Microsoft SQL Server Native Client team blog
SQL Protocols Topics from the Microsoft SQL Server Protocols team - Netlibs, TDS, SQL Browser, etc…
SQL Server Express WebLog
Esoteric All esoteric things about SQL Server and Microsoft Developer Tools
SQL Server Storage Engine
Microsoft SQL Server Release Services

Ramblings of a DBA (Tara Kizer)
SQL Learnings
Phoenix
SQL Server Pro - Articles and Tips from a SQL Server DBA and Consultant
Glenn Berry's SQL Server Performance
Kimberly L. Tripp
some thoughts... - Development|T-SQL

Documentation

Microsoft TechNet - SQL Server Best Practices
MSDN - Create or Delete a Server Alias for Use by a Client (SQL Server Configuration Manager)

Microsoft Learn - Servicing models for SQL Server
Microsoft Learn - Latest updates and version history for SQL Server

KB articles

INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
Error message when you try to install a SQL Server 2005 service pack or a SQL Server 2005 hotfix package: "Error 29528. The setup has encountered an unexpected error while Setting Internal Properties"
The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was released
You cannot restore system database backups to a different build of SQL Server
How to uninstall an instance of SQL Server 2005 manually
Microsoft Support - Concurrencty enhancements for the tempdb database (KB328551)

How to update or slipstream an installation of SQL Server 2008
Where to find information about the latest SQL Server builds
Microsoft Docs - Latest updates for Microsoft SQL Server

How to transfer logins and passwords between instances of SQL Server from SQL Server < 2005 to SQL Server ⇐ 2005.
How to transfer logins and passwords between instances of SQL Server from SQL Server >= 2005 to SQL Server ⇐ 2012.

How to configure security for SQL Server log shipping

Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment

Blogposts/Articles

Glenn Berry:
SQL Server and the “Lock pages in memory” Right in Windows Server
Suggested Max Memory Settings for SQL Server 2005/2008
Windows Instant File Initialization and SQL Server
Microsoft Support Policies for Windows Server and SQL Server
SQL Server Workload Types and Hardware Selection
simple-talk - Configuring the Storage Subsystem 20 july 2011, by Glen Berry.

Microsoft SQL Server Development Customer Advisory Team - Using SQL Agent Job Categories to Automate SQL Agent Job Enabling with Database Mirroring
SQLWebpedia - SQL Server OLTP Disk IO Configuration Best Practices
SQLWebpedia - Preserving SQL Job History

Nick Olsen's Programming Tips - SQL: SQL Server 2008 Express Missing ConfigurationFile.ini describes how to get the SQL Server 2008 R2 Express setup to make you a ConfigurationFile.ini, which it doesn't do by default, by using the /UIMode=Normal commandline switch.

The Scary DBA - Expert? Ha! describes four SQL Server trace flags that influence how execution plans get built (2861, 2335, 2389, 2390)

Insufficient data from Andrew Fryer - SQL Server on Windows Server Core - part 1 Server Core installation
Insufficient data from Andrew Fryer - SQL Server on Windows Server Core part 2–Configuring Server Core
Insufficient data from Andrew Fryer - SQL Server on Windows Server Core part 3–Installing SQL Server
Insufficient data from Andrew Fryer - SQL Server on Windows Server Core part 4 – Sysprep & SQL Server

Thavash's Sql Blog - Analysis Services performance guide
simple-talk - Great SQL Server Debates: Lock Pages in Memory
Brent Ozar PLF - SQL Server Can Run Databases from Network Shares & NAS
Brent Ozar Unlimited - What is the CXPACKET Wait Type, and How Do You Reduce It? describes Maximum Degree of Parallelism (MAXDOP) optimizations.
SQLRockstar - How To Improve Database Performance Without Changing Code
Bill Graziano's SQL Server Blog - SQL Server Scripts I Use

Brent Ozar Unlimited - SQL Server 2005/2008/2012 Setup Checklist
Brent Ozar Unlimited - SQL Server 2005 Setup Checklist Part 2: After The Install
Brent Ozar Unlimited - SQL Server Performance Tuning
Brent Ozar Unlimited - SQL Server Backup Best Practices
SQL Server Performance - Using AWE Memory in SQL Server 2000

MSDN Blogs > Carpe Datum > The Windows Page File and SQL Server

Sysadmins of the North - Install SQL Server cumulative updates silently

AlwaysOn

Brent Ozar - SQL Server 2014 and 2012 AlwaysOn Availability Groups

Backup

TroubleshootingSQL - Troubleshooting that darn backup error
TroubleshootingSQL - SQL Server Backup Simulator v2 available now
TroubleshootingSQL - SQL Server Backup Simulator: Cumulative update

Permissions

SQL Stuff - Admin: Bulkadmin vs ADMINISTER BULK OPERATIONS

Tools

SQLScheduler is a fully functional client/server application written in C# that allows administrators to schedule various SQL jobs for SQL Server Express and other versions of SQL Server. (dead link) Last SQLScheduler version.
Rob Kennedy Ingenuities - MSSQL Dump 2007 (dead link, archive.org version)
Profiler for Microsoft SQL Server 2005/2008 Express Edition (dead link)
Insulin Power - SQL Tools heeft de volgende tool: DbaMgr - DbaMgr2K A little alternative administrative console for Microsoft MSDE 1.0 and MSDE 2000 written in Microsoft Visual Basic 6.0.
SSMS Tools Pack is an add-in for Microsoft SQL Server Management Studio (SSMS) 2005, 2008, 2008 R2, Denali CTP3 and their respective Express versions. It contains a few upgrades to the SSMS IDE.
MSDN Blogs > Microsoft SQL Server Tips & Tricks > SQL Server Backup Simulator

Auto Close

Auto Close is a property on a SQL database. If set to true the database is closed and used resources are freed when there are no more connections to the databaase. This option is only recommended for use on development systems.
AutoClose Property (SQL-DMO)
Event ID: 17137

The following T-SQL generates ALTER DATABASE statements for all databases that currently have auto close set to on:

SELECT 'ALTER DATABASE ['+name+'] 
SET AUTO_CLOSE OFF
GO'
---SELECT name
FROM master.sys.databases
WHERE is_auto_close_on = 1

Source: SQL Server Pro - Practical SQL Server - Worst Practice: Allowing AutoClose on SQL Server Databases

Collation

Change the collation of your database:

USE Tempdb
GO
ALTER DATABASE (database name here) COLLATE (collation name here)
GO

Bron:Change the collation of your database

Activate Database mail

Tested and used on SQL Server 2005/2008/2019.

--Source: http://www.mssqltips.com/tip.asp?tip=1736
 
USE master
DECLARE @SMTPServer VARCHAR(100)
DECLARE @AdminEmail VARCHAR(100)
DECLARE @DomainName VARCHAR(100)
DECLARE @replyToEmail VARCHAR(100)
 
SET @SMTPServer = 'smtpserver.domain.com'
SET @AdminEmail = 'admin@domain.com'
SET @DomainName = '@domain.com'
SET @replyToEmail = 'replytomail@domain.com'
 
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Database Mail XPs', 1
EXEC sp_configure 'Agent XPs',1
RECONFIGURE --WITH OVERRIDE
 
DECLARE @servername VARCHAR(100)
DECLARE @email_address VARCHAR(100)
DECLARE @display_name VARCHAR(100)
DECLARE @testmsg VARCHAR(100)
SET @servername = REPLACE(@@servername,'\','_')
set @email_address = @servername + @DomainName
set @display_name = 'MSSQL - ' + @servername
set @testmsg = 'Test FROM ' + @servername
IF EXISTS(SELECT * from msdb.dbo.sysmail_profile)
PRINT 'DB mail already configured'
ELSE
BEGIN
  --Create database mail account.
   exec msdb.dbo.sysmail_add_account_sp
   @Account_name = 'SQLMail Account'
   , @description = 'Mail account FOR USE BY ALL DATABASE users.'
   , @email_address = @email_address
   , @replyto_address = @replyToEmail
   , @display_name = @display_name
   , @mailserver_name = @SMTPServer
   --Create global mail profile.
   exec msdb.dbo.sysmail_add_profile_sp
   @profile_name = 'SQLMail Profile'
   , @description = 'Mail profile setup FOR email FROM this SQL Server'
   --Add the account to the profile.
   exec msdb.dbo.sysmail_add_profileaccount_sp
   @profile_name = 'SQLMail Profile'
   , @Account_name = 'SQLMail Account'
   , @sequence_number=1
   --grant access to the profile to all users in the msdb database
   use msdb
   exec msdb.dbo.sysmail_add_principalprofile_sp
   @profile_name = 'SQLMail Profile'
   , @principal_name = 'public'
  , @is_default = 1
END
--send a test message.
exec msdb..sp_send_dbmail
@profile_name = 'SQLMail Profile', 
@recipients = @AdminEmail,
@subject = @testmsg,
@body = @testmsg
 
EXEC msdb.dbo.sysmail_help_profile_sp
 
-- Enabling SQL Agent notification
USE [msdb]
EXEC msdb.dbo.sp_set_sqlagent_properties 
 @email_save_in_sent_folder=1
 
EXEC master.dbo.xp_instance_regwrite 
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
 , N'UseDatabaseMail'
 , N'REG_DWORD'
 , 1
 
EXEC master.dbo.xp_instance_regwrite 
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
 , N'DatabaseMailProfile'
 , N'REG_SZ'
 , N'SQLMail Profile'

Bron:MSSQLTips - SQL Server Database Mail and SQL Agent Mail setup by using a script

Changing the mailserver

Check current settings

EXEC msdb.dbo.sysmail_help_account_sp

Change the mailserver

EXEC msdb.dbo.sysmail_update_account_sp @account_name = 'SQLMail Account', @mailserver_name = 'smtpserver.domain.com'

Send a test email

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLMail Profile', @recipients = 'admin@domain.com', @subject = 'Testmail', @body = 'Testmail'

Adding Operators (to be mailed)

To quickly add an operator which can be mailed (from a SQL Server Agent Job) use the following T-SQL code (and change name and email_address):

EXEC msdb.dbo.sp_add_operator @name=N'Email1', 
		@enabled=1, 
		@pager_days=0, 
		@email_address=N'email1@domain.com'
GO

A SQL Server Agent Job can only mail 1 operator.
To be able to mail multiple e-mailaddresses, add a new operator containing each of the e-mailaddresses separated by semicolons (;). The total length of the e-mailaddress field cannot exceed 100 characters.

EXEC msdb.dbo.sp_add_operator @name=N'Email1 & Email2', 
		@enabled=1, 
		@pager_days=0, 
		@email_address=N'email1@domain.com;email2@domain.com'
GO

Source:Stack Overflow - SQL Server Agent Job Notify multiple operators on failure

Deleting mail profiles and accounts

Query the configured profiles and accounts:

EXEC msdb.dbo.sysmail_help_profile_sp
EXEC msdb.dbo.sysmail_help_account_sp

Delete profile and account by name:

EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = 'SSIS ERROR';
EXEC msdb.dbo.sysmail_delete_account_sp @account_name = 'SSIS JOBS';

Sources:

Full Text Indexing

Enable:

EXEC sp_fulltext_database 'enable'

Show information about all full-text catalogs associated with the current database:

EXEC sp_help_fulltext_catalogs

Start a full population:

EXEC sp_fulltext_catalog 'catalogname', 'start_full'

Bron:SQL Server 2005 Books Online - Full-Text Search Stored Procedures (Transact-SQL)
See also:SQL Server 2005 Books Online - Full-Text Search Fundamentals

Memory Usage

Microsoft Support - How to configure SQL Server to use more than 2 GB of physical memory also look at:Large memory support is available in Windows Server 2003 and in Windows 2000
Memory Addressing for the Siebel Database Server with SQL Server 2000 - 32 bit
Boot.ini
Memory Configuration and /3GB /PAE
SQL Memory
A significant part of sql server process memory has been paged out. This may result in performance degradation
CSS SQL Server Engineers - The SQL Server Working Set Message
Microsoft Support - How to reduce paging of buffer pool memory in the 64-bit version of SQL Server (KB918483)
MSDN Blogs > CSS SQL Server Engineers > Fun with Locked Pages, AWE, Task Manager, and the Working Set…

Installation

SQL Server 2005 and Express Installation Tips and Tricks
Appdeploy - SQL Server 2005

Troubleshooting
CSS SQL Server Engineers - SQL Server 2005 Setup Fails in WOW (x86) On Computer With More Than 32 CPUs meld trace flag -T8015 als oplossing.

MSDE 2000 Release A

Installatie MSDE 2000 Release A (=SP3a):

Start MSDE2000A.exe en laat het uitpakken naar bijvoorbeeld D:\Setup\MSDE.
Maak vervolgens in D:\Setup\MSDE\ een install.cmd aan met de juiste setup.exe parameters. Een voorbeeld voor een installatie met instancenaam MSDESP3a, benaderbaar via het netwerk, mixed mode authenticatie, datadir D:\Data\MSSQL#E2000SP3a (laatste directory maakt setup zelf aan) en installatiemap D:\MSDE2000\E2000SP3a\Binn (laatste twee directories zet setup er zelf achter bij named instance) ziet er zo uit:

D:\Setup\MSDE\setup.exe SAPWD="password" INSTANCENAME="E2000SP3a" DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL DATADIR=D:\Data\ TARGETDIR="D:\MSDE2000\"

Mocht je meer dan één instance willen installeren, kopieer dan bovenstaande regel en pas INSTANCENAME aan.

Mocht je tegen de melding “Please go to the Control Panel to install and configure system components.” aanlopen, zorg dan dat het volledige pad naar setup.exe is vermeld in install.cmd of dat je 'm dubbelklikt vanuit explorer in de D:\Setup\MSDE\ directory. Zie ook:Experts Exchange - MSDE SQL Server Questions.

Bij een instancenaam van 2000SP3a liep ik tegen de melding “The instance name specified is invalid.” aan, na aanpassen instancenaam naar E2000SP3a liep de setup wel door.

SQL 2005 Client Tools and SSMS

The following commands can be executed to install the SQL 2005 Client Tools and SQL Server Management Studio.

Install

setup.exe /qb ADDLOCAL=Client_Components,Connectivity,SQLXML,SQL_Tools90

Repair

setup.exe /qb REPAIR=Client_Components,Connectivity,SQLXML,SQL_Tools90

Uninstall

setup.exe /qn REMOVE=Client_Components,Connectivity,SQLXML,SQL_Tools90

SQL Server 2005 SP1 Installation for Client Tools

The following options can be used when updating the SQL 2005 SP1 Client Toos and SSMS installation.

Install (x86-English)

SQLServer2005SP1-KB913090-x86-ENU.exe /quiet

Report Only Option (To see what installed components will be upgraded)

SQLServer2005SP1-KB913090-x86-ENU.exe /reportonly

Installation Log is located in %SystemRoot%\hotfix and are labled hotfix.log and hotfixX.log where X is the next incremented number.

SP2 for SQL Server 2005

Commando:
SQLServer2005SP2-KB921896-x86-ENU.exe met hierachter de volgende opties:

Command	Function
/?	Displays Help dialog.
/quiet	Runs Setup in quiet mode.
/reportonly	Displays the features that the package can update.
/allinstances	Upgrades all SQL Server instances and all shared components.
/instancename	Upgrades a specific SQL Server instance and all shared components.
/sapwd	Password for the SQL Server sa account to run scripts.
/user	User account for connecting to a remote cluster node.
/password	Password for user account for connecting to a remote cluster node.
/rsupgradedatabaseaccount	User account for upgrading Reporting Services.
/rsupgradepassword	Password for user account for upgrading Reporting Services.
/rsupgradedatabase	Prevents the Reporting Services database from being upgraded when the parameter equals 0.

For more information, see the 'Installing from the Command Prompt (Hotfix)' Help topic.

Om SP2 silent te installeren:

SQLServer2005SP2-KB921896-x86-ENU.exe /quiet /allinstances

Met alleen /quiet installeerde de SP2 update bij mij niet op een installatie van SQL Server 2005 Developer Edition.

Cumulative Update installation fails on SQL Server 2008 R2

Scenario:
Installing SQL Server 2008 R2 SP2 CU3 on two named SQL Server 2008 R2 SP2 instances fails.
The detailed setup log files mention the following:

2012-12-12 16:47:29 Slp: Error: Action “Microsoft.SqlServer.Configuration.ScoExtension.VerifiedCopyFileAction” threw an exception during execution.
2012-12-12 16:47:29 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: File d:\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Binn\DllTmp64\sqlagentctr100.dll could not be copied to file path C:\Windows\system32\perf-MSSQL10_50.SQL2008R2-sqlagtctr.dll. —> Microsoft.SqlServer.Configuration.ScoExtension.CopyFileException: File d:\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Binn\DllTmp64\sqlagentctr100.dll could not be copied to file path C:\Windows\system32\perf-MSSQL10_50.SQL2008R2-sqlagtctr.dll. —> Microsoft.SqlServer.Configuration.Sco.ScoException: The process cannot access the file 'C:\Windows\system32\perf-MSSQL10_50.SQL2008R2-sqlagtctr.dll' because it is being used by another process. —> System.IO.IOException: The process cannot access the file 'C:\Windows\system32\perf-MSSQL10_50.SQL2008R2-sqlagtctr.dll' because it is being used by another process.

2012-12-12 16:47:34 Slp: Error: Action “AgentPatch_AgentPatching_Cpu64” failed during execution.
2012-12-12 16:47:34 Slp: Completed Action: AgentPatch_AgentPatching_Cpu64, returned False

2012-12-12 16:48:29 Slp: Error result: -2058354685
2012-12-12 16:48:29 Slp: Result facility code: 1360
2012-12-12 16:48:29 Slp: Result error code: 3

Workaround:

Rename the file by executing the following in an (elevated) command prompt:

REN C:\Windows\system32\perf-MSSQL10_50*-sqlagtctr.dll perf-MSSQL10_50*-sqlagtctr.dll.old

Rerun the Cumulative Update installer.

Sources:
SQL Server Developer Center > SQL Server Forums > SQL Server Setup & Upgrade > Failed install of CU7 for SQL Server 2008 R2 SP1
SQL Server Developer Center > SQL Server Forums > SQL Server Setup & Upgrade > SQL 2008 R2 repair error
Microsoft Connect - The process cannot access the file 'C:\Windows\SysWOW64\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll' because it is being used by another process. The page mentions that it should be fixed starting with SQL Server 2008 R2 SP1 CU10 and SQL Server 2008 R2 SP2 CU4.

Installatie SQL Server 2005 en 4 x 6-core CPU's

KB954835 - On a computer that has a multicore processor, if the ratio of the logical processors to the physical sockets is not divisible by two, you cannot install SQL Server 2005

Dit probleem onlangs ondervonden op een IMB x3850 M2 met 4 x een 6-core Xeon. Deze machine had effectief 24 cores, dit is deelbaar door 2, dus de inhoud van KB954835 zou niet van toepassing moeten zijn op deze situatie, maar de oplossing voor en de symptomen van dit probleem waren dat wel.

Ik probeerde SQL Server 2005 x64 Enterprise op een Windows Server 2003 x64 Enterprise editie op deze x3850 M2 te installeren en liep tegen hetzelfde probleem aan waarbij de SQL Server service niet wilde starten en setup de melding “The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, “How to: View SQL Server 2005 Setup Log Files” and “Starting SQL Server Manually”” gaf met een Retry en Cancel knop. Klikken op Retry gaf weer dezelfde melding en klikken op Cancel rolde de installatie van het SQL Server service gedeelte terug waardoor je met een onvolledige installatie kwam te zitten.

Uiteindelijk de machine opnieuw geinstalleerd en via msconfig de processoren beperkt tot 1. Hierna kon de installatie van SQL Server 2005 uitgevoerd worden en daarna SP3 voor SQL Server 2005. Vervolgens via msconfig de standaard startup opties hersteld en na een herstart draaide SQL Server 2005 SP3 op de 24 cores.

Hieronder een drietal van de bijbehorende meldingen in het event log:

Event Type:	Error
Event Source:	Application Error
Event Category:	(100)
Event ID:	1000
Date:		2/16/2009
Time:		3:57:21 PM
User:		N/A
Computer:	MACHINE01
Description:
Faulting application sqlservr.exe, version 2005.90.1399.0, faulting module sqlservr.exe, version 2005.90.1399.0, fault address 0x0000000000b323f0.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 41 70 70 6c 69 63 61 74   Applicat
0008: 69 6f 6e 20 46 61 69 6c   ion Fail
0010: 75 72 65 20 20 73 71 6c   ure  sql
0018: 73 65 72 76 72 2e 65 78   servr.ex
0020: 65 20 32 30 30 35 2e 39   e 2005.9
0028: 30 2e 31 33 39 39 2e 30   0.1399.0
0030: 20 69 6e 20 73 71 6c 73    in sqls
0038: 65 72 76 72 2e 65 78 65   ervr.exe
0040: 20 32 30 30 35 2e 39 30    2005.90
0048: 2e 31 33 39 39 2e 30 20   .1399.0 
0050: 61 74 20 6f 66 66 73 65   at offse
0058: 74 20 30 30 30 30 30 30   t 000000
0060: 30 30 30 30 62 33 32 33   0000b323
0068: 66 30                     f0

Event Type:	Information
Event Source:	DrWatson
Event Category:	None
Event ID:	4097
Date:		2/16/2009
Time:		3:57:22 PM
User:		N/A
Computer:	MACHINE01
Description:
The application, D:\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe, generated an application error The error occurred on 02/16/2009 @ 15:57:21.979 The exception generated was c0000005 at address 0000000001B323F0 (sqlservr!DmpGetClientExport)

Event Type:	Error
Event Source:	MsiInstaller
Event Category:	None
Event ID:	10005
Date:		2/16/2009
Time:		4:01:37 PM
User:		MACHINE01\Administrator
Computer:	MACHINE01
Description:
Product: Microsoft SQL Server 2005 (64-bit) -- Error 29503. The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
The error is  (1053) The service did not respond to the start or control request in a timely fashion.
.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 7b 37 39 46 31 42 36 35   {79F1B65
0008: 45 2d 38 46 43 30 2d 34   E-8FC0-4
0010: 44 30 33 2d 39 35 34 44   D03-954D
0018: 2d 46 39 45 37 31 43 38   -F9E71C8
0020: 35 41 45 43 37 7d         5AEC7}

Mirroring

MSDN - Setting Up Database Mirroring
Microsoft TechNet - Database Mirroring FAQ
Microsoft TechNet - Replication and Database Mirroring
Database Mirroring in SQL Server 2005
KB907741 - Issues to consider when you use the database mirroring feature in the initial release of SQL Server 2005
MSDN forums - use trace flag 1400 during startup
In de RTM release van SQL Server 2005 (voor SP1) moet trace flag -T1400 gebruikt worden om mirroring mogelijk te maken.
Microsoft TechNet - Database Mirroring Best Practices and Performance Considerations voor SQL Server 2005.
Microsoft TechNet - Database Mirroring in SQL Server 2005 onderaan de pagina is een link naar een Word document met dezelfde inhoud.
Phoenix - Database Mirroring Errors
SQLCAT - Mirroring a Large Number of Databases in a Single SQL Server Instance
Microsoft SQL Server Support Blog - Setting up database mirroring with certificates
MSDN Blogs > Blogs from Suhas > Step-by-step guide to configure Database Mirroring between SQL Server Instances in a Workgroup

ODBC connectie naar SQL Server 2005 Express mogelijk maken

Ga naar SQL Server Surface Area Configuration en dan Surface Area Configuration for Services and Connections.
Ga vervolgens naar SQLEXPRESS→Database Engine→Remote Connections. Stel deze in op Using TCP/IP only. Herstart de SQL Server (SQLEXPRESS) service.
Ga vervolgens naar SQL Server Browser→Service, zet de startup type op Automatic en start de service.

I/O Requests Taking Longer Than 15 Seconds To Complete

SQL Server Storage Engine - Why are IOs taking so long?
Troubleshooting Microsoft SQL Server - I/O requests taking longer than 15 seconds to complete on file
rusan consulting - Event ID 833: I/O requests taking longer than 15 seconds
SQL Server Pro - I/O Requests Taking Longer Than 15 Seconds To Complete
Microsoft Support - SQL Server timing values may be incorrect when you use utilities or technologies that change CPU frequencies (KB931279)

Time Stamp Counter

SQL Server 2005 - RDTSC Truths and Myths Discussed
SQL Server 2005 SP2 will introduce new messages to the error log related to timing activities.
Microsoft Support - SQL Server timing values may be incorrect when you use utilities or technologies that change CPU frequencies
Subject: The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.

Reporting Services

Client Print

Kodyaz Development Resources - Reporting Services Client-Side Printing and Silent Deployment of RSClientPrint.cab ActiveX file.
Maria's two cents - "Unable to load client print control" after install a Service Pack or Cumulative Update of SQL Server 2005 zie ook:Maria's two cents.
Microsoft Support - How To Install ActiveX Controls in Internet Explorer Using the Active Directory
Microsoft Support - Error message when you try to print a SQL Server 2005 Reporting Services report on a client computer: "Unable to load client print control"
MSDN Forum - Unable to load client print control

SQL Server > SQL Server Forums > SQL Server Reporting Services > Unable to load client print control sql2008 sp2 describes that the correct updated version of RSClientPrint will not be installed with SP2 for SQL Server 2008 because they forgot to include it in the SP2 installer. A quick/good fix is suggested, namely “Install SQL server 2008 R2 on a test server and copy the cab from: …\Reporting Services\ReportServer\bin”.

E-mail

Reporting Services bied (in een AD omgeving) de optie om mail te sturen naar de alias van de gebruiker, of naar een in te vullen mailadres.
Als je gebruik maakt van het alias, moet er in de (Exchange) mailomgeving een mailadres bestaan met het alias en moet in RSReportServer.config de <DefaultHostName></DefaultHostName> geconfigureerd zijn. Als je geen gebruik maakt van het alias, dan sta je de eindgebruikers toe om zelf een mailadres in te vullen, soms is dit niet een gewenste situatie.

Als je niet een vanaf extern mailbaar alias wil aanmaken, dan kun je er voor kiezen om alle (Exchange) mailgebruikers te voorzien van een alias@ADdomeinnaam mailadres en de volgende configuratie in RSReportServer.config te gebruiken:

<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName>ADdomeinnaam</DefaultHostName>

Vergeet niet om ook <SMTPServer> te configureren.

Bronnen:
SQL Server 2008 Books Online (May 2009) - Configuring a Report Server for E-Mail Delivery
SQL Server 2008 Books Online (May 2009) - RSReportServer Configuration File
Microsoft Support - SQL Server 2005 Reporting Services e-mail subscriptions do not work in Windows Vista and in Windows Server 2008 if you use aliases as e-mail addresses
SQL Server magazine - Self-Service Subscriptions bied als workaround stored procedures om achteraf de mailadressen te corrigeren in een SQL table.

Openquery/Openrowset en Active Directory(/LDAP)

Deze werkt:

SELECT Name, displayName,givenname,distinguishedName, SAMAccountName
FROM 
OPENROWSET('ADSDSOObject','adsdatasource'; 'domain\user';'password',
'SELECT  Name, displayName,givenname,distinguishedName, SAMAccountName
    FROM ''LDAP://domainfqdn/dc=domain,dc=local''')

Dan ben je echter, met de standaard instellingen op de domain controller, beperkt tot een resultaat van maximaal 1000 regels.

De volgende statement zou dit moeten verhelpen door PageSize=10000, maar deze geeft een foutmelding:

SELECT Name, displayName,givenname,distinguishedName, SAMAccountName
FROM 
OPENROWSET('ADSDSOObject','User ID=domain\user;Password=password;ADSI Flag=0x11;Page Size=10000',
'SELECT  Name, displayName,givenname,distinguishedName, SAMAccountName
    FROM ''LDAP://domainfqdn/dc=domain,dc=local''')

Bovenstaande statement geeft de volgende foutmelding:

Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "SELECT  Name, displayName,givenname,distinguishedName, SAMAccountName
    FROM 'LDAP://domainfqdn/dc=domain,dc=local' WHERE objectClass = 'USER'" for execution against OLE DB provider "ADSDSOObject" for linked server "(null)".

some thoughts... - SQL Server View of AD Users or Performing a SQL Distributed Query by Using ADSI
LessThanDot Forum - Querying Active Directory The Code Project - SQL Server –Active Directory Interaction
Database Journal - Query Active Directory Data from SQL Server using T-SQL
weblaunch - How to run a SSRS report showing the data in the Global Address List
CodeBetter.com Blogs - Brendan Tompkins - Create a SQL Server View of your AD Users
Hilltop Lab - SQL Distributed Queries
SQLTeam.com Forums - querying active directory through openquery
SQLTeam.com Forums - OPENROWSET with ADsDSOObject
sql newgroups - Active Directory Linked Server Fails After Upgrade to SQL Server 2

Microsoft Support - Performing a SQL distributed query by using ADSI (KB299410)
MSDN - Microsoft OLE DB Provider for Microsoft Active Directory Service

“It is my understanding that the OLEDB provider for Microsoft Directory Services doesn't expose the page size property….I'm pretty sure I've seen this logged as a feature request on MS connect before but it might not hurt to log it again” aldus Tommy Bollhofer op SQL ServerCentral.com Forum - ADSI, OpenRowset and Page Size

“Based on my research, OLEDB Command object does not expose the property “Page Size”. A possible workaround is to use ADODB in our .NET project, instead of using OLEDB.” aldus Jialiang Ge op MSDN Visual Basic Developer Center - Forums - How to query Active Directory with OLEDB and get over the 1000 rows limi

Connection is busy with results for another hstmt

With the standard SQL Server driver on an ODBC data source, you can only have one active cursor at a time. When the program using the ODBC data source tries to open a second cursor when the previous one hasn't been neatly closed, you get the “Connection is busy with results for another hstmt” error.

If the SQL Native Client driver from SQL Server 2005 is supported by the application you could work around the problem by replacing the ODBC data source with the SQL Server driver by one that uses the SQL Native Client driver and setting MARS to enabled. To create such an ODBC data source use the following command:

On a 32-bit system running a 32-bit program or a 64-bit system running a 64-bit program:

"%systemroot%\system32\odbcconf.exe" /a {CONFIGSYSDSN "SQL Native Client" "DSN=datasourcename|Description=fill in description|SERVER=Servername\instancename|Trusted_Connection=no|Database=databasename|MARS_Connection=yes"}

On a 64-bit system running a 32-bit program:

"%systemroot%\syswow64\odbcconf.exe" /a {CONFIGSYSDSN "SQL Native Client" "DSN=datasourcename|Description=fill in description|SERVER=Servername\instancename|Trusted_Connection=no|Database=databasename|MARS_Connection=yes"}

Or add the “MARS_Connection, REG_SZ, yes” setting to the registry key for the ODBC data source below HKLM\SOFTWARE\ODBC\ODBC.INI\DATASOURCENAME or HKCU\Software\ODBC\ODBC.INI\DATASOURCENAME if it already uses the SQL Native Client driver.

Dba Spot - Connection is busy with results for another hstmt - sqlserver-odbc
PHWinfo - how to fix error "Connection is busy with results for another hstmt"
Coding With The Enemy - Thursday, June 12, 2008 " Connection is busy with results for another hstmt"
MSDN Blogs > Data Access blog > Using MARS with SQL Native Client [Chris Lee]
SQL Server Developer Center > SQL Server Forums > SQL Server Data Access > Connection is busy with results for another hstmt

Firewall exceptions

To enable remote connections to SQL Server and any named instances open the following ports in the firewall:

Default Instance	tcp 1433
SQL Server Browser	udp 1434
SQL Server Analysis Services	tcp 2382

Sources:
MSDN Blogs > SQL BI > SQL Server Analysis Services Port (SQL 2005 / 2008)

Named instance

To open the correct tcp port for a SQL Server named instance, do the following:
Open SQL Server Configuration Manager, go to “SQL Server Network Configuration” rightclick on TCP/IP below “Protocols for instancename” and select properties. On the IP Addresses tab take note of the value for “TCP Dynamic Ports” and open that tcp port in the firewall.|

SQL 2008 R2 Max Memory

Max 64 GB of memory for SQL Server 2008 R2 Standard.

This seems to be per instance:

Posted by Robert on 6 April 2011
Glenn,

Regarding the max server memory for SQL 2008 R2, it looks like Standard Edition can utilize 64GB. Do you know if this is 64GB per server or per instance? For example, if I have a server with 96GB of memory and two SQL instances, could I set max server memory to 45GB on each instance or would I need to set it to 32GB on each instance?

Robert

Posted by Glenn Berry on 6 April 2011
Robert,

That 64GB limit for SQL Server 2008 R2 Standard Edition is per instance. You could go 45GB per instance, or you might decide to favor one instance over the other, depnding on the relative workloads.

Source: Glenn Berry's SQL Server Performance - Some Suggested SQL Server 2008 R2 Instance Configuration Settings
See also: Server Fault - SQL Server 2008 R2 memory limit per instance or server?

Database versioning

CodeProject - Automatic Script SQL Server 2005 Objects and Commit under Subversion
All about Business Intelligence - Synchronize a SQL Server Database with an SVN Repository

Can't delete SQL Agent Job for Maintenance Plan

While trying to delete a SQL Agent job in SQL Server 2005 for a maintenance plan (that no longer exists) you receive the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for Job 'MaintenancePlan.Subplan_1'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.5000.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5000&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

The following script should fix it:

USE [msdb]
 declare @job_name varchar(100)
 set @job_name = N'MaintenancePlan.Subplan_1'


--First, delete the logs for the plan

 delete sysmaintplan_log
 FROM sysmaintplan_subplans AS subplans INNER JOIN
 sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id INNER JOIN
 sysmaintplan_log ON subplans.subplan_id = sysmaintplan_log.subplan_id
 WHERE (syjobs.name = @job_name)


--delete the subplan

 delete sysmaintplan_subplans
 FROM sysmaintplan_subplans AS subplans INNER JOIN
 sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id
 WHERE (syjobs.name = @job_name)


--delete the actual job (You can do the same thing through Management Studio (Enterprise Manager)

 delete
 from msdb.dbo.sysjobs_view where name = @job_name

Source:Archive.org - Gediminas (Gedas) Gudenas - Sql Server 2005 Delete Maintenance Plan Error, original link, via

Event ID 28005

The full event log item:

Log Name:      Application
Source:        MSSQLSERVER
Date:          26-6-2013 12:50:55
Event ID:      28005
Task Category: (2)
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      server.domain.local
Description:
An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 11. Could not obtain information about Windows NT group/user 'DOMAIN\username', error code 0x534.

This occurs because a database owner is set to a certain domain account, but SQL Server can't get the account properties (SID) of that account.

The workaround is to check which database has the DOMAIN\username as owner and set the database owner for that database to sa:

USE databasename
GO
EXEC sp_changedbowner 'sa'
GO

Sources:
David Klein's Corner - Fix for SQL 2008 Exception - An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 19. Could not obtain information about Windows NT group/user 'DOMAIN\username', error code 0x5
Ektron Devcenter - PROBLEM:Notifications not updated in Activity Stream
geekProject.com - Change authorization to ease SQL Server development
SQLTeam Forums - SQL Error 15404: Could not obtain information...

Permissions

MSDN - ServerPermissionSet Properties
MSDN - GRANT Database Permissions (Transact-SQL)
SQLBackupRestore.com - Backup rights
MSDN - CREATE SERVER ROLE (Transact-SQL)
MSDN - CREATE ROLE (Transact-SQL)

Event ID 18456 - Login failed - Token-based server access validation failed

TechNet Blogs » The 9z, by Chris Davis » SQL Event ID 18456: Login failed for user Reason: Token-based server access validation failed
Stuart Blackler - SQL SERVER Fix - Token-based server access validation failed with an infrastructure error

Table of Contents

Microsoft SQL Server

Blogs

Documentation

SQL Server Management Studio

SQL Server 2000

SQL Server 2005

Installation

SQL Server 2008

Installation

SQL Server 2008 R2

Installation

SQL Server 2012

Installation

SQL Server 2014

SQL Server 2016

SQL Server 2017

SQL Server 2019

SQL Server 2022

KB articles

Blogposts/Articles

AlwaysOn

Backup

Permissions

Other links

Tools

Auto Close

Collation

Activate Database mail

Changing the mailserver

Adding Operators (to be mailed)

Deleting mail profiles and accounts

Full Text Indexing

Memory Usage

Installation

MSDE 2000 Release A

SQL 2005 Client Tools and SSMS

SQL Server 2005 SP1 Installation for Client Tools

SP2 for SQL Server 2005

Cumulative Update installation fails on SQL Server 2008 R2

Installatie SQL Server 2005 en 4 x 6-core CPU's

Mirroring

ODBC connectie naar SQL Server 2005 Express mogelijk maken

I/O Requests Taking Longer Than 15 Seconds To Complete

Time Stamp Counter

Reporting Services

Client Print

E-mail

Openquery/Openrowset en Active Directory(/LDAP)

Connection is busy with results for another hstmt

Firewall exceptions

Named instance

SQL 2008 R2 Max Memory

Database versioning

Can't delete SQL Agent Job for Maintenance Plan

Event ID 28005

Permissions

Event ID 18456 - Login failed - Token-based server access validation failed