0

Code snippets : Windows : Importing Event Logs to Database

-

Code snippet from old archives. It shows how to import Windows event log entries to database. Important is to setup database and table accordingly, so it contains apropriate columns.

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "DSN=EventLogs;"
objRS.CursorLocation = 3
objRS.Open "SELECT * FROM tblEventLog" , objConn, 3, 3
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colRetrievedEvents = objWMIService.ExecQuery _
("Select Category, CategoryString, ComputerName, Data, EventCode, _
EventIdentifier, EventType, LogFile, Message, RecordNumber, SourceName, _
TimeGenerated, TimeWritten, Type, User from Win32_NTLogEvent")
For Each objEvent in colRetrievedEvents
objRS.AddNew
objRS("el_Category") = objEvent.Category
objRS("el_CategoryString") = objEvent.CategoryString
objRS("el_ComputerName") = objEvent.ComputerName
objRS("el_Data") = objEvent.Data
objRS("el_EventCode") = objEvent.EventCode
objRS("el_EventIdentifier") = objEvent.EventIdentifier
objRS("el_EventType") = objEvent.EventType
objRS("el_LogFile") = objEvent.LogFile
objRS("el_Message") = objEvent.Message
objRS("el_RecordNumber") = objEvent.RecordNumber
objRS("el_SourceName") = objEvent.SourceName
objRS("el_TimeGenerated") = objEvent.TimeGenerated
objRS("el_TimeWritten") = objEvent.TimeWritten
objRS("el_Type") = objEvent.Type
objRS("el_User") = objEvent.User
objRS.Update
Next
objRS.Close
objConn.Close
5

How-to : Setup AlwaysOn Availability Group on SQL Server 2012

-

SQL Server 2012 has very nice HADR technology built-in, AlwaysOn Availability Groups. It allows to have multiple replicas of selected databases across multiple servers, which can be even located across multiple sites. AlwaysOn itself provides mechanism to keep all replicas of database synchronized and up-to-date, as some of them might serve data for read-only purposes (for example reporting).

MSDN article: AlwaysOn Availability Groups (SQL Server) describes AlwaysOn technology in details. In this article I will focus on practical side of this HADR technology and how to get this up and running fast without any issues.

To test and demonstrate SQL Server 2012 AlwaysOn capabilities I built simple Lab environment on VMware Workstation as shown below. Lab contains 2 servers with Windows Server 2012 R2 Datacenter and SQL Server 2012 Enterprise.

Lab environment also contains Active Directory domain and Domain Controller, which is not shown on the diagram.

In order to get SQL Server AlwaysOn up and running we will complete following activities:

  • Install Failover Clustering Role
  • Configure Windows Server Failover Clustering
  • SQL Server installation
  • Demo database preparation
  • Enable Availability Groups on SQL Server
  • Creating AlwaysOn Availability Group

So, let’s get started…

(more…)

0

Code snippets : Execute SQL script using PowerShell

-

Recently I went through requirement of running SQL scripts on multiple databases on different servers. As the list of databases was different from script to script I decided to create universal solution, which will allow to easily prepare for deployment and can be simply reused at any time.

As a platform to execute scripts I went with PowerShell as that provides flexibility in case additional functionality will be required.

Whole solution contains 3 files:

  • SqlExecutionInflow.csv – file contains list of databases and servers where particular database is located. It is simple CSV file with 2 columns
  • SqlExecQuery.sql – contains SQL script which will be executed against all databases listed in SqlExecutionInflow.csv
  • SqlExec.ps1 – main script which load SqlExecInflow.csv and executes query from SqlExecQuery.sql

All files have to be placed in same folder. As a result script will create transcript file with output from all executed commands.

And here are example files and script itself…

(more…)

0

Tips & Tricks : Missing SQL Server assmeblies in PowerShell

-

When you try to connect to SQL Server from PowerShell script and you get following message:


Unable to find type [Microsoft.SqlServer.Management.Smo.Server]

then you have to download and install following components:

  • Microsoft System CLR Types for SQL Server 2008 R2
  • Microsoft SQL Server 2008 R2 Shared Management Objects
  • Microsoft Windows PowerShell Extensions for SQL Server 2008 R2

From here.

0

Tips & Tricks : SQL Server “Cannot connect to WMI provider”

-

Recently I had issue which occured without any particular reason on SQL Server 2012. All of the sudden I start getting error message:

Cannot connect to WMI provider. You do not have permission or the server is
unreachable.
Note that you can only manage SQL Server 2005 servers with SQL
Server Configuration Manager.
Invalid Namespace.

when tried to run SQL Server Configuration Tools.

What resolved situation is command:

mofcomp C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof
0

Tips & Tricks : Microsoft SQL Server 2008 & 2012 Feature Packs

-

Some of applications require additional SQL Server 2008 or 2012 fetures installed. Some of these features might not be available in standard setup of SQL Server. In that case you have to refer to Microsoft Dowloads web page in order to download additional components required for installation:

Page 1 of 212