Archive for the ‘SQL Server 2008’ Category

SQL Server 2008 R2 Reporting Services in ASP.Net 4.0 Webseite einbinden

13. Juni 2012

Die letzten Stunden habe ich damit verbracht, auf einem SQL Server 2008 R2 in den Reporting Services einen Bericht zu erstellen,
der auf eine Datenbank zugreift,
der eine Shared DataSource dazu verwendet,
der ein Shared DataSet verwendet,
der per Parameter über das DataSet gefilter wird,
der alle fünf Minuten als SnapShot historisiert wird,
und von dem die letzten 10 SnapShots gehalten werden.

Diesen Report wollte ich in einer ASP.Net Seite auf zwei Wegen bekommen:
1.) Anzeige des Reports im ReportViewer Control
2.) Report als PDF-Datei zum Download

1.) Anzeige des Reports im ReportViewer Control

Diese Variante war relativ einfach – Control auf die Seite ziehen und die Properties für den Report und den ReportServer angeben:

<ServerReport
 ReportPath="/ReportProject1/Report2"
 ReportServerUrl="http://meinServer/reportserver" />

Der ReportViewer zeigt so konfiguriert immer die Daten des aktuellsten SnapShot an.

2.) Report als PDF-Datei zum Download

Zunächst braucht man den NameSpace Microsoft.Reporting.WebForms bzw. in der web.config den folgenden Verweis (Version 10 = SSRS 2008 R2 ):

<add assembly=“Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A“/>

Im Code sieht das dann etwa so aus:

Dim bytes As Byte()
'// Instanz des ReportViewer-Steuerelements
Using rv = New ReportViewer
'// Festlegen des Verarbeitungsmodus des ReportViewer-Steuerelements.
 rv.ProcessingMode = ProcessingMode.Remote
'// Berichtsserver festlegen
 rv.ServerReport.ReportServerUrl = New Uri("http://meinServer/reportserver")
'// Festlegen des Pfades zum Bericht
 rv.ServerReport.ReportPath = "/ReportProject1/Report1"
'// Parameter aus der am Bericht hinterlegten Parameterliste auswählen
 Dim pars = rv.ServerReport.GetParameters
 Dim par0 = pars(0)
 Dim p = New ReportParameter(name:=par0.Name, value:=par0.ValidValues(0).Value) 'rv.ServerReport.ReportParameter()
 rv.ServerReport.SetParameters(p)
'// Verarbeitet den Bericht und rendert ihn im angegebenen Format und speichert diesen in ein byteArray
 bytes = rv.ServerReport.Render(format:="PDF", deviceInfo:=Nothing, _
 mimeType:=mimeType, encoding:=encoding, fileNameExtension:=extension, _
 streams:=streamIDs, warnings:=warnings)
End Using
'// Schreibt das Resultat in ein PDF
 Using fs = IO.File.Create(IO.Path.Combine(Server.MapPath("~/."), "result.pdf"), bytes.Length)
 fs.Write(bytes, 0, bytes.Length)
 fs.Close()
 End Using

Damit man auf Daten aus einem bestimmten SnapShot zugreifen kann, habe ich den SSRS WebService / SOAP Client verwendet:

Zunächst also eine ServiceReference setzen auf :

http://meinServer:80/ReportServer/ReportService2010.asmx?wsdl

Dann die paar folgenden Zeilen nach der Parameterübergabe und vor dem Rendern des Berichts im obigen Code eingefügt:

    Dim rs2010 As New ReportingService2010.ReportingService2010SoapClient
    Dim tuh2010 As New ReportingService2010.TrustedUserHeader
    Dim itemHist2010() As ReportingService2010.ItemHistorySnapshot = Nothing
    Dim sih2010 As ReportingService2010.ServerInfoHeader = rs2010.ListItemHistory( _
        TrustedUserHeader:=tuh2010 _
        , ItemPath:=rv.ServerReport.ReportPath _
        , ItemHistory:=itemHist2010 _
        )
    '// einfach mal den 5. Snapshot herausgepickt
    Dim histId = (From o In itemHist2010 Order By o.CreationDate Select o.HistoryID)(5)

    rv.ServerReport.HistoryId = histId

Dann gab’s ein paar Fehlerchen bzgl. irgendwelcher Anmeldewurschteleien. Diese konnte ich durch folgende Maßnahmen vermeinden:

In der web.config:

<!--<security mode="None">-->
<security mode="TransportCredentialOnly">
  <!--<transport clientCredentialType="None" proxyCredentialType="None" realm="" />-->
  <transport clientCredentialType="Ntlm" proxyCredentialType="None" realm="" />

Und im Code wie folgt entsprechende Benutzerdaten angeben:

    Dim cred As New System.Net.NetworkCredential(userName:="ich", password:="wirdnichtverraten", domain:="meinreich")
    rs2010.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation
    rs2010.ClientCredentials.Windows.ClientCredential = cred

Damit funktionierte dann alles wie gewünscht.

Ganz brauchbar dabei fand ich die folgenden Links:

SSRS Part 5 Demo2 – Configuring a Report Snapshot

Report Server Web Service

Developer’s Guide: Tutorials (Reporting Services)

Creating the Web Service Proxy

Viewing Report History Snapshot …

Verwalten von SQL Server mit der richtlinienbasierten Verwaltung

1. Dezember 2011

Man kennt das ja: DB angelegt, ein paar Kollegen halten sich nicht an die Namenskonvention für die DB-Objekte.
Darum habe ich heute ein paar Minuten Zeit investiert und mich mal mit den Richtlinien/Policies des SQL Server beschäftigt.

Dabei sind mir v.a. folgende Artikel untergekommen:

Mit der schönen Abfrage …

SELECT facet.name as ‚Facet Name‘,
OnDemand = CASE facet.execution_mode & 4
WHEN 4 THEN ‚On Demand; On Schedule‘
END,
OnChangeLogOnly = CASE facet.execution_mode & 2
WHEN 2 THEN ‚On Change, Log Only‘
END,
OnChangePrevent = CASE facet.execution_mode & 1
WHEN 1 THEN ‚On Change, Prevent‘
END
FROM msdb.dbo.syspolicy_management_facets facet

… lässt sich schön Abfragen, welche Facets (ich würde das mal als ZielObjekte bezeichnen) bestimmte Evaluation Modi unterstützen.

In meinem Fall wollte ich z.B. erzwingen, dass Tabellennamen in der Datenbank DB1 immer mit tDb1 beginnen. Leider scheint genau dieses über die Policies nicht unterstützt zu sein, da dafür der Evalutation Mode On Change:  prevent verwendet können werden müßte.

Es kann vermutlich nur über DDL Trigger realisiert werden.

Datumstypen im SQL Server

8. November 2011

Der Post mit dem bescheidenen Artikel Der ultimative Guide für die Datetime Datentypen ist zwar schon ein paar Tage alt, aber trotzdem sehr interessant und einigermaßen ausführlich.

SQL Server – Anmeldung wenn nichts mehr geht

26. Oktober 2011

Falls der SQL Server nicht mehr reagiert (z.B. wenn alle Worker Threads belegt bzw. blockiert sind), dann kann man sich mit der Dedicated Admin Connection (DAC) behelfen.
Mehr dazu und auch was man unternehmen kann, um die Maschine zu retten im Post Troubleshooting THREADPOOL Waits von Klaus.
Weitere Details zur DAC in der MSDN Library:

 

Wichtig ist, dass man sich im SSMS nicht mit dem Explorer verbindet, sondern ein neues Query öffnet und damit die Verbindung zur DB herstellt (Wichtig: dem Instanznamen admin: voranstellen und einen Sysadmin verwenden).

Das Öffnen einer DAC per SQLCMD direct auf dem DB-Server sieht wie folgt aus:

sqlcmd -Sadmin:localhost -U sa -P password -d master

Der SQL Browser lief bei mir übrigens dabei nicht.

SQL Server Plan Cache

21. Oktober 2011

Dass SQL Server (in meinem Fall 2008 R2) sich Ausführungspläne im (endlichen) Speicher merkt, ist wohl den meisten DB-Administratoren und -Entwicklern bekannt.
Ebenso leuchtet ein, dass Adhoc Abfragen diesen Cache vollmüllen und dadurch die Leistung des Systems nicht gerade verbessert wird.
Wenn dann noch die ungünstige Situation eintritt, dass man häufig direkt auf dem ProduktivSystem entwickeln muss, dann ist spätestens das ein Grund, sich mal mit dem Thema zu beschäftigen.

Das Blog von Pinal Dave war mit dem Post SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script Ausgangspunkt meiner Recherche.

Zunächst einmal kann man sich mit folgender Abfrage einmal alle gespeicherten Pläne im Cache anzeigen lassen, die nur einmal benutzt wurden:

SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;
GO

Einzelne Pläne können mit dem Verwaltungsbefehl DBCC FREEPROCCACHE aus dem Cache entfernt werden, indem man das plan_handle aus der obigen Abfrage dem Befehl in Klammern hinten anstellt, wie im folgenden Beispiel:

DBCC FREEPROCCACHE(0x06000100804AD30040219A86000000000000000000000000)
WITH NO_INFOMSGS;
GO

Wenn ansonsten kein plan_handle angegeben wird, werden alle Pläne aller DBs auf dem Server gelöscht.
Das ist auf dem Produktivserver nicht unbedingt best practice.

Da das Finden und Löschen eines Plans die Entwicklerproduktivität negativ beeinflusst, ist es hilfreich, dass man die Erstellung der Pläne schon relativ einfach im Vorfeld verhindern kann.
Dazu kann man dem jeweiligen SQL Befehl wie im folgenden Beispiel den Query Hint OPTION (RECOMPILE) hinzufügen:

SELECT * FROM Customers
OPTION (RECOMPILE);
GO

Ein Hinweis von Klaus Aschenbrenner und der Blogpost Clearing your ad-hoc SQL plans while keeping your SP plans intact von Maciej Pilecki führten mich zu folgendem Befehl, der wohl nur die Adhoc Pläne, nicht aber die von Prozeduren löscht:

DBCC FREESYSTEMCACHE('SQL Plans');
GO

Allerdings hat das Teil den Nachteil, dass es auch parameterisierte Abfragen (z.B. von LinqToSQL und Entity Framework) wegputzt.

Übrigens gibt es auch beim Zugriff mit .Net-Anwendungen über das Entity Framework das Query Plan Caching eine Property System.Data.EntityClient.EntityCommand.EnablePlanCaching.
Die habe ich allerdings noch nicht genauer untersucht.

Evtl. könnte man mit Hilfe der obigen Abfrage in Kombination mit DBCC FREEPROCCACHE automatisch in bestimmten Zeitzyklen einfach alle Pläne löschen, die nur einmal verwendet wurden.

SQL Server 2008 Extended Events

11. Oktober 2011

Zur Fehlersuche oder zur Untersuchung aller möglicher Probleme kannt ich bisher nur die DMVs und den Profiler.
Aber der SQL Server 2008 bietet ein noch weit besseres Toolset für solche Anlässe, nämlich die Extended Events.

Damit läßt sich scheinbar so ziemlich alles verfolgen, was auf der Box abgeht und mit dem SQL Server zu tun hat.
Vereinfacht gesagt, legt man fest, welche Ereignisse beobachtet werden sollen und wo die gesammtelten Informationen gesammelt werden sollen.

Als einführende Beispiele haben mir die folgenden Artikel sehr geholfen:

Ausführlicher kann man sich mit dem Whitepaper Using SQL Server 2008 Extended Events einarbeiten.

Auf CodePlex gibt’s auch eine grafische Oberfläche für die Extended Events, das SQL Server 2008 Extended Events SSMS Addin. Damit kommt man anfangs wahrscheinlich einfacher zu einem Ergebnis, da es einige Events, Targets, usw. gibt.

Mit folgendem Skript kann man z.B. auftretenden Fehler mitprotokollieren:

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AjErrorsReported')
DROP EVENT SESSION AjErrorsReported ON SERVER
GO

--SELECT * FROM sysmessages WHERE msglangid=1031 ORDER BY 1,2

-- Create Event
CREATE EVENT SESSION AjErrorsReported
ON SERVER
-- Add event to capture event
ADD EVENT sqlserver.error_reported
(
-- Add action - event property
ACTION (
sqlserver.session_id
,sqlserver.sql_text
,sqlserver.tsql_stack
,sqlserver.database_id
,sqlserver.username
,sqlserver.client_hostname
)
WHERE sqlserver.error_reported.severity>=11
)
-- Add target for capturing the data - XML File
ADD TARGET package0.asynchronous_file_target(
SET filename='E:\SqlServerData\MSSQL10_50.MSSQLSERVER\MSSQL\Log\AjErrorsReported.xet'
, metadatafile='E:\SqlServerData\MSSQL10_50.MSSQLSERVER\MSSQL\Log\AjErrorsReported.xem')
-- Add target for capturing the data - Ring Bugger
--,ADD TARGET package0.ring_buffer(SET max_memory = 4096)
WITH (max_dispatch_latency = 3 seconds)
GO

-- Enable Event
ALTER EVENT SESSION AjErrorsReported ON SERVER
STATE=START
GO

-- Read the data from Ring Buffer
--SELECT CAST(dt.target_data AS XML) AS xmlLockData
--FROM sys.dm_xe_session_targets dt
--JOIN sys.dm_xe_sessions ds ON ds.Address = dt.event_session_address
--JOIN sys.server_event_sessions ss ON ds.Name = ss.Name
--WHERE dt.target_name = 'ring_buffer'
--AND ds.Name = 'AjErrorsReported'
--GO

-- Read the data from XML File
SELECT
event_data_XML.value('(event/data[1])[1]','INT') AS error
,event_data_XML.value('(event/data[2])[1]','INT') AS severity
,event_data_XML.value('(event/data[3])[1]','INT') AS [state]
,event_data_XML.value('(event/data[4])[1]','BIT') AS user_defined
,event_data_XML.value('(event/data[5])[1]','VARCHAR(1024)') AS [message]
,event_data_XML.value('(event/action[1])[1]','INT') AS session_id
,event_data_XML.value('(event/action[4])[1]','INT') AS database_id
,event_data_XML.value('(event/action[5])[1]','VARCHAR(50)') AS username
,event_data_XML.value('(event/action[6])[1]','VARCHAR(10)') AS client_hostname
,event_data_XML.value('(event/action[2])[1]','VARCHAR(1024)') AS sql_text
,event_data_XML.value('(event/action[3])[1]','VARCHAR(512)') AS tsql_stack
--SELECT CAST(event_data AS XML) event_data, *
FROM
(
SELECT CAST(event_data AS XML) event_data_XML, *
FROM sys.fn_xe_file_target_read_file
('E:\SqlServerData\MSSQL10_50.MSSQLSERVER\MSSQL\Log\AjErrorsReported*.xet',
'E:\SqlServerData\MSSQL10_50.MSSQLSERVER\MSSQL\Log\AjErrorsReported*.xem',
NULL, NULL)) T
GO

-- Stop the event
ALTER EVENT SESSION AjErrorsReported ON SERVER
STATE=STOP
GO

-- Clean up. Drop the event
DROP EVENT SESSION AjErrorsReported
ON SERVER
GO

Wenn’s mal eng wird … How compressed is your SQL Server DB backup?

31. August 2011

Während des DB Backup kann das Backup File größer sein, als am Ende der Sicherung.
Dieses Verhalten ist über das Traceflag 3042 beeinflussbar.

Siehe dazu diesen Post bzw.  KB-Artikel.

SQL Server Database Engine Permissions

31. August 2011

Folgendes war im SQL PASS Newsletter zu lesen:

SQL Server 2008 R2 has 195 permissions.
SQL Server Code-named ‚Denali‘ has 214 permissions
Ein neues Poster zeigt die Berechtigungen des SQL Servers (incl. Denali) und ihre Beziehungen zueinander.

Link zum Poster

Link zum BlogPost

 

Artikel zur SQL Server Performance

15. März 2011

Zwei ältere Artikel zur SQL Server Performance sind:

Aus ersterem stammt auch das folgende SQL Statement zur Analyse von Performance Problemen:

 SELECT
 COALESCE(DB_NAME(t.[dbid]),'Unknown') AS [DB Name],
 ecp.objtype AS [Object Type],
 t.[text] AS [Adhoc Batch or Object Call],
             SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
             ((CASE qs.[statement_end_offset]
                         WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
                                     - qs.[statement_start_offset])/2) + 1) AS [Executed Statement]
             , qs.[execution_count] AS [Counts]
             , qs.[total_worker_time] AS [Total Worker Time], (qs.[total_worker_time] /
 qs.[execution_count]) AS [Avg Worker Time]
             , qs.[total_physical_reads] AS [Total Physical Reads],
  (qs.[total_physical_reads] / qs.[execution_count]) AS [Avg Physical Reads]
             , qs.[total_logical_writes] AS [Total Logical Writes],
 (qs.[total_logical_writes] / qs.[execution_count]) AS [Avg Logical Writes]
             , qs.[total_logical_reads] AS [Total Logical Reads],
 (qs.[total_logical_reads] / qs.[execution_count]) AS [Avg Logical Reads]
             , qs.[total_clr_time] AS [Total CLR Time], (qs.[total_clr_time] /
 qs.[execution_count]) AS [Avg CLR Time]
             , qs.[total_elapsed_time] AS [Total Elapsed Time], (qs.[total_elapsed_time]
 / qs.[execution_count]) AS [Avg Elapsed Time]
             , qs.[last_execution_time] AS [Last Exec Time], qs.[creation_time] AS [Creation Time]
 FROM sys.dm_exec_query_stats AS qs
     JOIN sys.dm_exec_cached_plans ecp ON qs.plan_handle = ecp.plan_handle
             CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
 --    ORDER BY [Total Worker Time] DESC
 --    ORDER BY [Total Physical Reads] DESC
 --    ORDER BY [Total Logical Writes] DESC
 --    ORDER BY [Total Logical Reads] DESC
 --    ORDER BY [Total CLR Time] DESC
 --    ORDER BY [Total Elapsed Time] DESC
 ORDER BY [Counts] DESC

SQL Server 2008 und GeoDaten bzw. Spatials

23. November 2010

Heute musste ich mich mal ganz unerwartet mit den „neuen“ spatial Datentypen und Funktionen des SQL Servers auseinandersetzen, nachdem ein Kollege die Mitbewerberstandorte im Umkreis von 10 Kilometern einer bestimmten Filiale haben wollte.

Längen- und Breitengrad hatten wir schon länger in einzelnden decimal Spalten in unseren Tabellen
Das Ergebnis sah dann so aus:

SELECT
 geography::STGeomFromText( 'POINT(' + CONVERT(VARCHAR(100),fil.laengenGrad) + ' ' +  CONVERT(VARCHAR(100),fil.breitenGrad) +')',4326) AS FilGeo
 ,geography::STGeomFromText( 'POINT(' + CONVERT(VARCHAR(100),mfil.laengenGrad) + ' ' +  CONVERT(VARCHAR(100),mfil.breitenGrad) +')',4326) AS MitGeo
 ,((geography::STGeomFromText( 'POINT(' + CONVERT(VARCHAR(100),fil.laengenGrad) + ' ' +  CONVERT(VARCHAR(100),fil.breitenGrad) +')',4326)).STDistance(
 geography::STGeomFromText( 'POINT(' + CONVERT(VARCHAR(100),mfil.laengenGrad) + ' ' +  CONVERT(VARCHAR(100),mfil.breitenGrad) +')',4326))/1000) AS DistanzKilometer
 --,fil.ortsId, mfil.ortsid
 , fil.filialName, fil.filialNummer, filialName2, ort.postLeitZahl AS FilialPlz, ort.ortsName AS FilialOrt
 , mfir.mitbewerberFirmenBez, m.mitbewerberName, mfil.strasse, mort.postLeitZahl AS MitbewerberPlz, mort.ortsName AS MitbewerberOrt
 FROM Filialen AS fil
 INNER JOIN Orte AS ort ON ort.ortsId = fil.ortsId
 INNER JOIN MitbewerberFilialen AS mfil ON
 ((geography::STGeomFromText( 'POINT(' + CONVERT(VARCHAR(100),fil.laengenGrad) + ' ' +  CONVERT(VARCHAR(100),fil.breitenGrad) +')',4326)).STDistance(
 geography::STGeomFromText( 'POINT(' + CONVERT(VARCHAR(100),mfil.laengenGrad) + ' ' +  CONVERT(VARCHAR(100),mfil.breitenGrad) +')',4326))/1000)<10
 INNER JOIN MitbewerberFirmen AS mfir ON mfil.mitbewerberFirmenId = mfir.mitbewerberFirmenId
 INNER JOIN Mitbewerber AS m ON m.mitbewerberId = mfil.mitbewerberFirmenId
 INNER JOIN Orte AS mort ON mort.ortsId = mfil.ortsId
 WHERE fil.filialNummer=1173

Hier noch ein paar Erläuterungen und Links, die mich zur Lösung führten:

  • Denis‘ Post SQL Server 2008 Proximity Search With The Geography Data Type war ein guter Einstieg, um mal grundsätzlich herauszufinden, wie man aus zwei Decimal-Typen einen Geographie-Typ erzeugt
    -> geography::STGeomFromText
    und wie man im definierten Umkreis sucht
    -> .STDistance
  • Die Funktion STGeoFromText ist kurz hier in der MSDN erläutert.
  • Antwort auf die Frage, was der Ausdruck 4326 zu bedeuten hat, fand ich auf stackoverflow.
  • Mit der Abfrage
    select * from sys.spatial_reference_systems where spatial_reference_id=4326
    kriegt man dann auch die passende Info aus der Datenbank und erfährt auch gleich, in welcher Maßeinheit die Distanz geliefert wird (in diesem Fall sind es Meter).
  • Dieses Problem ist mir zum Glück erspart geblieben 😉