Backup SQL Server to Amazon S3

Backing up your SQL Server database to Amazon S3 takes a few steps to get the process correct, and this article from Alexandr Omelchenko shows us how to get it done, with step-by-step instructions.

There are many ways of making SQL Server database backups and there are as many methods as to how and where to store them. But are you sure that your method really convenient for you? What if you database backup plan is: a full backup twice a day, differential every four hours and transaction log backups every 30 minutes, and all these backups you need to send to Amazon S3. How much time will you spend every day to make all these backup? Fortunately, you can create a backup job with the help of SqlBak in a few minutes, which will make all backups according to your schedule and send them to Amazon S3.

Using PowerShell to Manage Audits

PowerShell is a power scripting tool that can also be used to manage your SQL Server audits. In this article by Colleen Morrow we learn some of the advanced techniques. You can also start at the beginning here.

Creating an Audit Object

The first step in implementing SQL Audit is to create the audit object, so that’s where we’ll start. Let’s look at the whole script and then break it down.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$instance = 'MyServer'
$auditName = $instance+"_TestAudit"
$auditDir = '\\MyServer\D$\Audits\'
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
$newAudit = new-object Microsoft.SqlServer.Management.Smo.Audit($srv, "$auditName")
$newAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::File
$newAudit.FilePath = $auditDir
$newAudit.MaximumRolloverFiles = 10
$newAudit.MaximumFileSize = 100
$newAudit.QueueDelay = 1000
$newAudit.Create()
$newAudit.Enable()

The first thing we’re doing is simply declaring some variables to hold our instance name, the name of the audit we want to create, and the folder where we want our audit file to be written. For re-usability, we could even make these into parameters, but I wanted to keep this simple. Next we create a new SMO connection to our instance with the command

1
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance

Once we’re connected to SQL Server, we can create a new audit class object and start assigning attribute values. Here, we’re setting the destination to a file, and the file path to our $auditDir variable. We set the maximum number of rollover files, the queue delay, etc. All of the available properties can be found here: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.audit.aspx, but everything I’m setting here should look very familiar if you’ve been following along in this series.

1
2
3
4
5
6
$newAudit = new-object Microsoft.SqlServer.Management.Smo.Audit($srv, "$auditName")
$newAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::File
$newAudit.FilePath = $auditDir
$newAudit.MaximumRolloverFiles = 10
$newAudit.MaximumFileSize = 100
$newAudit.QueueDelay = 1000

And once we’ve got all of our properties set, we do the PowerShell equivalent of clicking OK: we create the audit and enable it.

1
2
$newAudit.Create()
$newAudit.Enable()

Want to make sure our audit was created? We can list all of our audit objects like this:

1
2
3
4
5
6
$instance = 'MyServer'
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
foreach ($a in $srv.Audits)
{
   Write-Host $a.Name
}

ASP.NET Core logging with NLog and SQL Server

NLog is a free logging platform for .NET, Xamarin, Silverlight and Windows Phone with rich log routing and management capabilities. NLog makes it easy to produce and manage high-quality logs for your application regardless of its size or complexity. NLog can dynamically write to one of multiple targets for each log message, including text files, Event Logs, email, and SQL Server.

In this article by Damien Bod shows how to use NLog to write your log events to SQL Server.

The NLog.Extensions.Logging is required to add NLog to a ASP.NET Core application. This package as well as the System.Data.SqlClient are added to the dependencies in the project.json file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
"dependencies": {
       "Microsoft.NETCore.App": {
           "version": "1.0.0",
           "type": "platform"
       },
       "Microsoft.AspNetCore.Mvc": "1.0.0",
       "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
       "Microsoft.AspNetCore.Diagnostics": "1.0.0",
       "Microsoft.AspNetCore.Server.Kestrel": "1.0.0",
       "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0",
       "Microsoft.Extensions.Configuration.FileExtensions": "1.0.0",
       "Microsoft.Extensions.Configuration.Json": "1.0.0",
       "Microsoft.Extensions.Logging": "1.0.0",
       "Microsoft.Extensions.Logging.Console": "1.0.0",
       "Microsoft.Extensions.Logging.Debug": "1.0.0",
       "Microsoft.Extensions.Options.ConfigurationExtensions": "1.0.0",
       "NLog.Extensions.Logging": "1.0.0-rtm-alpha4",
       "System.Data.SqlClient": "4.1.0"
 },

Now a nlog.config file is created and added to the project. This file contains the configuration for NLog. In the file, the targets for the logs are defined as well as the rules. An internal log file is also defined, so that if something is wrong with the logging configuration, you can find out why.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
<?xml version="1.0" encoding="utf-8" ?>
      autoReload="true"
      internalLogLevel="Warn"
      internalLogFile="C:\git\damienbod\AspNetCoreNlog\Logs\internal-nlog.txt">
    
  <targets>
    <target xsi:type="File" name="allfile" fileName="nlog-all.log"
                layout="${longdate}|${event-properties:item=EventId.Id}|${logger}|${uppercase:${level}}|${message} ${exception}" />
    <target xsi:type="File" name="ownFile-web" fileName="nlog-own.log"
             layout="${longdate}|${event-properties:item=EventId.Id}|${logger}|${uppercase:${level}}|  ${message} ${exception}" />
    <target xsi:type="Null" name="blackhole" />
    <target name="database" xsi:type="Database" >
    <connectionString>
        Data Source=N275\MSSQLSERVER2014;Initial Catalog=Nlogs;Integrated Security=True;
    </connectionString>
<!--
  Remarks:
    The appsetting layouts require the NLog.Extended assembly.
    The aspnet-* layouts require the NLog.Web assembly.
    The Application value is determined by an AppName appSetting in Web.config.
    The "NLogDb" connection string determines the database that NLog write to.
    The create dbo.Log script in the comment below must be manually executed.
  Script for creating the dbo.Log table.
  SET ANSI_NULLS ON
  SET QUOTED_IDENTIFIER ON
  CREATE TABLE [dbo].[Log] (
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Application] [nvarchar](50) NOT NULL,
      [Logged] [datetime] NOT NULL,
      [Level] [nvarchar](50) NOT NULL,
      [Message] [nvarchar](max) NOT NULL,
      [Logger] [nvarchar](250) NULL,
      [Callsite] [nvarchar](max) NULL,
      [Exception] [nvarchar](max) NULL,
    CONSTRAINT [PK_dbo.Log] PRIMARY KEY CLUSTERED ([Id] ASC)
      WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
-->
          <commandText>
              insert into dbo.Log (
              Application, Logged, Level, Message,
              Logger, CallSite, Exception
              ) values (
              @Application, @Logged, @Level, @Message,
              @Logger, @Callsite, @Exception
              );
          </commandText>
          <parameter name="@application" layout="AspNetCoreNlog" />
          <parameter name="@logged" layout="${date}" />
          <parameter name="@level" layout="${level}" />
          <parameter name="@message" layout="${message}" />
          <parameter name="@logger" layout="${logger}" />
          <parameter name="@callSite" layout="${callsite:filename=true}" />
          <parameter name="@exception" layout="${exception:tostring}" />
      </target>
      
  </targets>
  <rules>
    <!--All logs, including from Microsoft-->
    <logger name="*" minlevel="Trace" writeTo="allfile" />
    <logger name="*" minlevel="Trace" writeTo="database" />
      
    <!--Skip Microsoft logs and so log only own logs-->
    <logger name="Microsoft.*" minlevel="Trace" writeTo="blackhole" final="true" />
    <logger name="*" minlevel="Trace" writeTo="ownFile-web" />
  </rules>
</nlog>

The nlog.config also needs to be added to the publishOptions in the project.json file.

1
2
3
4
5
6
7
8
9
10
"publishOptions": {
   "include": [
       "wwwroot",
       "Views",
       "Areas/**/Views",
       "appsettings.json",
       "web.config",
       "nlog.config"
   ]
 },

Now the database can be setup. You can create a new database, or use and existing one and add the dbo.Log table to it using the script below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Log] (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Application] [nvarchar](50) NOT NULL,
    [Logged] [datetime] NOT NULL,
    [Level] [nvarchar](50) NOT NULL,
    [Message] [nvarchar](max) NOT NULL,
    [Logger] [nvarchar](250) NULL,
    [Callsite] [nvarchar](max) NULL,
    [Exception] [nvarchar](max) NULL,
  CONSTRAINT [PK_dbo.Log] PRIMARY KEY CLUSTERED ([Id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The table in the database must match the configuration defined in the nlog.config file. The database target defines the connection string, the command used to add a log and also the parameters required.

You can change this as required. As yet, most of the NLog parameters, do not work with ASP.NET Core, but this will certainly change as it is in early development. The NLog.Web Nuget package, when completed will contain the ASP.NET Core parameters.

Not to Rely on Government for IT Security

bd051-hacker

The Cybersecurity Information Sharing Act (CISA) is a U.S. federal law designed, in its own words, to “improve cybersecurity in the United States through enhanced sharing of information about cybersecurity threats, and for other purposes.” The law allows traffic information from the internet to be shared between technology and manufacturing companies and the U.S. government. The bill was introduced in the U.S. Senate on July 10, 2014, and passed as an amendment into a consolidated spending bill, which was signed into law by President Barack Obama on Dec. 18, 2015.

CISA offers data sharing and liability protection for data shared as security information at the B2B (business to business) and B2G (business to government) levels. Opponents to the law question the value of the law, saying it will move responsibility from private business to the government, thereby increasing vulnerability of personal private information. It also helps disperse personal private information across seven government agencies, including the National Security Agency (NSA), FBI, state law enforcement, and local police agencies. Many people believe this gives too many people anonymous access to business and personal information (such as credit card data) that could be easily compromised without a clear line of responsibility.

Most former, and many current, government employees are questioning the logic of sharing so much information without clearly defining security requirements and responsibilities. After more than one data breach by foreign hackers of government systems, you should ask yourself why should business hold any expectation of help and cooperation from the government if they can’t protect their own systems.

 

Employee Expectations and Enterprise Software

It seems employees are more interested in shadow IT solutions than using approved enterprise software. This is a constant battle between IT Management and various departments that has no end in sight, and it gets more difficult to manage every day.

First, you have to accept that enterprise software can be very complex, difficult to learn, and complex to access. What can you to do to get non-technical employees to accept your enterprise solution?

When you look at the challenges of training your non-technical employees on how to use the new systems, maintaining minimum levels of employee productivity during the transition to approved software,  all while customizing the off-the-shelf product to fit your custom needs may make a typical employee question the wisdom of moving to the selected choice.

Most employees won’t accept the idea of required training to fully utilize the new software, but unfortunately many employees won’t know how to use the new applications without proper training. Make sure you include a budget for developing training material, training hours for employees, and time to develop quality training materials.

Employees expect constant access to the data they need to do their job, including remote access from multiple devices. If your chosen solution doesn’t perform the functions required by the team, expect resistance to accepting your solution over other solutions that appear to meet their expectations.

You have to balance corporate needs against what the team expects and find a solution that meets corporate needs while providing the functionality the team expects when looking at the solutions available today.

Using Unreal Engine in Visual Studio

Unreal Engine is designed to integrate with Visual Studio, allowing you to quickly and easily make code changes in your projects to immediately see results upon compilation. Setting up Visual Studio to work with the Unreal Engine can help improve efficiency and the overall user experience for developers using this powerful tool.

You can read some step-by-step instructions for installing the Unreal Engine in Visual Studio in this article from The Visual Studio Blog.

The Unreal Engine is a cross-platform game engine targeting DirectX 11 and 12 for PC and Xbox, OpenGL for iOS, OS X and Android, and WebGL for the Web.  Console development on Xbox One and PlayStation 4 is also available on the Unreal Engine for registered developers of those platforms. Unreal Engine also supports virtual reality development targeting platforms including Oculus, PlayStation VR, SteamVR and Gear VR.

Unreal Engine games can be programmed using C++ and Unreal’s visual scripting framework called Blueprints.  Visual Studio serves as a powerful code editor and debugger when using C++ for Unreal development.

Unreal C++ code inside Visual StudioUnreal C++ code inside Visual Studio

Visual scripting with BlueprintsVisual scripting with Blueprints

SQL Server Management Studio – August 2016 Release

SQL Server

Microsoft has announced the latest generally-available (GA) quality release of SQL Server Management Studio (SSMS) for SQL Server 2016. This fully-supported release is available for download here.

New or Updated Features in Version 13.0.15700.28:

  • ‘Active Directory Universal Authentication’ authentication option.
  • ‘Create database’ dialog to streamline creation of Azure SQL databases.
  • Initial beta support for high-resolution displays.
  • Several bug fixes.
  • New Extended Events templates.