0 notes &
I went to the Great British Beer Festival again yesterday, meeting up with @TPCasey and @UncleSpong.
Here are the beers sampled:
1 note &
Today is the start of the Great British Beer Festival, relocated for 2012 to its old home of Kensington Olympia due to some Olympian Volleyballists laying claim to Earl’s Court.
Some technical problems with my CAMRA membership (expired membership card) made entry a bit more tedious than necessary but once in I have to admit I much prefer Olympia as a venue to Earls Court.
Beers tasted today (in chronological order):
Hoping to continue the beer journey later this week..
0 notes &
But now that I know C# has a GOTO statement I can bang out this classic from my Apple II days….
using System;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
ten: print("hello world");
twenty: goto ten;
}
static void print(string msg)
{
Console.WriteLine(msg);
}
}
}
1 note &
I’m not a big fan of Maintenance Plans. I know they can work quite well, but I don’t like the UI and the way it hides the actual SQL being used.
Yesterday I set about writing a stored proc that can manage backups (full and transaction log) for all databases on a server. We source control (using the awesome SQL Source Control from Red Gate) this script in our db_maintenance database so the same script is easily maintained and deployed to all SQL servers.
I started out using a script from SQLTeam.com that I found here. It looked like a good start but having been written in 2002 needed some updating!
This is what I ended up with.
You can specify whether the database is included in full backups, included in transaction log backups and how many days to retain backups for each. By default it will backup all databases (excluding tempdb), and support transaction log backups on all databases with FULL or BULK_LOGGED recovery models. The default retention period is 7 days for both full and transaction log backups.
CREATE procedure [dbo].[upDBA_Backup_All_Databases]
@Path varchar(128) ,
@Type varchar(4) -- Full / Log
as
/*
Backup file format
_Full_yyyymmdd_hhmmss.bak
_Log_yyyymmdd_hhmmss.bak
*/
/*
Usage example
exec [upDBA_Backup_All_Databases] 'c:\SQLBackups', 'Full'
*/
/*
Configuration table
create table dbo.tbDatabase_Backup_Setting (
Name varchar(128) primary key nonclustered ,
BackupFlagFull varchar(1) not null check (BackupFlagFull in ('Y','N')) ,
BackupFlagLog varchar(1) not null check (BackupFlagLog in ('Y','N')) ,
RetentionDaysFull SmallInt not null ,
RetentionDaysLog SmallInt not null
)
*/
-- NOTE: These should be single backslash characters, I just put double to fix a problem with the syntax highlighter!
if (Right(@Path, 1) <> '\\') set @Path = @Path + '\\'
declare @sql varchar(1000)
set nocount on
/*** Include new databases with default settings **/
insert dbo.tbDatabase_Backup_Setting
(
Name,
BackupFlagFull,
BackupFlagLog,
RetentionDaysFull ,
RetentionDaysLog
)
select
S.name,
'Y', -- Always full backup
case when (S.name NOT IN ('master','model','msdb')
AND DATABASEPROPERTYEX(S.name, 'Recovery') IN ('FULL','BULK_LOGGED')) then
'Y' else 'N' end, -- No txn log backup on master, model, msdb, tempdb or databases with recovery models not supporting it
7, -- keep full backups for 7 days
7 -- keep log backups for 7 days
from
master..sysdatabases S
left outer join dbo.tbDatabase_Backup_Setting DBS on DBS.Name = S.Name
where
-- Exclude temp dbs
S.name not in ('tempdb', 'ReportServerTempDB') and
-- Exclude existing, known databases
DBS.Name is null
print 'Added ' + Cast(@@rowcount as varchar) + ' new databases to tbDatabase_Backup_Setting.'
/*** Remove any non-existant databases ***/
delete dbo.tbDatabase_Backup_Setting
where not exists
(
select name
from master..sysdatabases
where master..sysdatabases.Name = dbo.tbDatabase_Backup_Setting.Name
)
print 'Removed ' + Cast(@@rowcount as varchar) + ' old databases from tbDatabase_Backup_Setting.'
-- Tables for output from xp_cmdshell
create table #ExistingBackups (Name varchar(128), ID int identity (1,1))
create table #output ([message] varchar(128))
declare
@Name varchar(128),
@RetentionDays SmallInt,
@LastBackupToKeep varchar(8),
@filename varchar(512),
@error varchar(max)
/*** Get databases and retention period for backups ***/
declare db_cursor cursor for
select
Name,
case when @Type = 'Full' then RetentionDaysFull else RetentionDaysLog end
from dbo.tbDatabase_Backup_Setting
where
(@Type = 'Full' and BackupFlagFull = 'Y') or
(@Type = 'Log' and BackupFlagLog = 'Y')
/*** Loop through each database ***/
begin try
open db_cursor
fetch next from db_cursor INTO @Name, @RetentionDays
while @@FETCH_STATUS = 0
begin
/*** Backup the database ***/
select @filename = @Path + @Name + '_' + @Type + '_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','') + '.bak'
print 'Backing up [' + @Name + '] to ''' + @filename + ''''
if @Type = 'Full'
backup database @Name to disk = @filename
else
backup log @Name to disk = @filename
/*** Clean up existing backups ***/
select @sql = 'dir /B ' + @Path
select @sql = @sql + @Name + '_' + @Type + '*.*'
delete #ExistingBackups
insert #ExistingBackups exec master..xp_cmdshell @sql
delete #ExistingBackups where Name is null
if exists (select * from #ExistingBackups where Name like '%File Not Found%')
delete #ExistingBackups
select @LastBackupToKeep = convert(varchar(8),DateAdd(dd, 0 - @RetentionDays, GetDate()), 112)
delete #ExistingBackups where Name > @Name + '_' + @Type + '_' + @LastBackupToKeep
print 'Keeping ' + cast(@@rowcount as varchar) + ' existing backups within retention period of ' + Cast(@RetentionDays as varchar) + ' days.'
declare @eID int, @eMaxID int
-- loop round all the out of date backups
select @eID = 0, @eMaxID = coalesce(max(ID), 0) from #ExistingBackups
while @eID < @eMaxID
begin
select @eID = min(ID) from #ExistingBackups where ID > @eID
select @filename = @Path + Name from #ExistingBackups where ID = @eID
select @sql = 'del ' + @filename
print 'Deleting ''' + @filename + ''''
insert #output exec master..xp_cmdshell @sql
if (select count(*) from #output where [message] is not null) > 0
begin
set @error = ''
select @error = @error + ' [' + [message] + ']' from #output where [message] is not null
raiserror ('An error occurred deleting old backup file: ''%s''.', 16, 1, @error)
end
delete #output
end
delete #ExistingBackups
-- Fetch next database
fetch next from db_cursor INTO @Name, @RetentionDays
end
end try
begin catch
-- Error reporting via RAISERROR
declare @ErrorNumber INT = ERROR_NUMBER()
declare @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
declare @ErrorProcedure NVARCHAR(4000) = ERROR_PROCEDURE()
declare @ErrorLine INT = ERROR_LINE()
RAISERROR ('An error occurred during database backup.
Error Number : %d
Error Message : %s
Database Name : %s
Affected Procedure : %s
Affected Line Number: %d',
16, 1,
@ErrorNumber, @ErrorMessage, @Name, @ErrorProcedure,@ErrorLine)
end catch
-- Ensure cursor is closed
if CURSOR_STATUS('global','db_cursor') >= 0
begin
close db_cursor
deallocate db_cursor
end
GO
1 note &
We had a bit of a hair-pulling here at eSpares this morning. The plan was to upgrade the firmware in the Dell PowerConnect 6224 switches in one of our testing environments.
Moving from version 3.2.1.3 to 3.3.1.10. According to the documentation it should’ve been a no-brainer, firmware release notes had nothing of concern, but after applying the update our iSCSI network was inaccessible and VMware was having a fit.
After much aggravation we found the culprit. The firmware upgrade reconfigured some of the ports on the switch, re-enabling STP and changing the VLAN config from untagged to tagged. Re-applied our (source-controlled of course) configurations and it was back up and running.
Nothing is ever as easy or side-effect free as the documentation suggests.
Lesson learned.
1 note &
Has your Microsoft dev shop gone on vacation because of the Nuget.org breakdown?
There are some quick fixes!
Add your local nuget cache as an alternative package source.
You probably want to use the same packages you’ve used before & these are hopefully still cached on your PC.
Open the Package Manager Settings dialogue and add a new source:
%LocalAppData%\NuGet\Cache
Or you can edit your NuGet.Config file located under C:\Users\[username]\AppData\Roaming\NuGet as follows:
<?xml version=”1.0” encoding=”utf-8”?>
<configuration>
<packageSources>
<add key=”NuGet official package source” value=”https://go.microsoft.com/fwlink/?LinkID=230477” />
<add key=”Local Cache” value=”C:\Users\[username]\AppData\Local\NuGet\Cache” />
</packageSources>
<activePackageSource>
<add key=”NuGet official package source” value=”https://go.microsoft.com/fwlink/?LinkID=230477” />
</activePackageSource>
</configuration>
The config file won’t expand environment variables so you’ll have to hard code the location of your nuget cache.
Host your own NuGet feed
You can setup your own NuGet feed, mirroring packages that you regularly need. See http://docs.nuget.org/docs/creating-packages/hosting-your-own-nuget-feeds
Use a 3rd party to host a NuGet feed for you
I haven’t tried this yet but it’s something I plan on having a look at in case, heaven forbid, NuGet goes down again!
Add the local package source to your NuGet.config
Have you enabled NuGet Package Restore on your solution?
Open the.nuget/nuget.configsolution file and add the following element:
<packageSources>
<add key=”NuGet official package source”
value=”https://go.microsoft.com/fwlink/?LinkID=206669” />
<add key=”Local Package Cache”
value=”%Localappdata%\NuGet\Cache” />
</packageSources>
UPDATE: This doesn’t actually work as you expect, unless nuget.config is in the current working directory. Please vote for this work item to have it work as expected.
8 notes &
Sorta… I came across this earlier while looking for a way to avoid opening a new PowerShell window in order to get elevated privileges for a command I wanted to run. This really should’ve been built-in to PowerShell…
1 note &
I use Gmail for all my different email addresses including some custom domain names.
One of the annoying things with the default setup is that it includes the “Sender” field in the message, for DomainKeys compatiblity, which some email clients (Outlook & Hotmail for starters) use to display the email as “From myemail@gmail.com on behalf of myemail@mycustomdomain.com”.
This just looks ugly.
The solution is to configure Gmail to use a different SMTP server (eg. from your ISP) to send emails from your custom domain, but not everyone has access to an SMTP server that supports this. For instance Sky’s SMTP server rewrites all messages as coming from your sky.com address.
There is a solution though. Use Gmail’s SMTP servers.
Open Gmail options and edit the account for your custom domain email address and set it up as follows:
One more thing… If you are using Google’s 2 Step Verification, then you will need to setup an application-specific password for the Gmail SMTP server and use that instead of your google password.
6 notes &
Following the work I’ve been doing with getting Domain Admins working across an Active Directory cross-forest trust, I also found a quirk integrating VMware ESXi 4.1 in the same environment…
I had created a Domain Local security group in the remote domain containing the administrators on my local trusted domain. I added permission for this group to VMware ESXi but was still unable to login using credentials from administrators of my local domain. If I created similar permissions for users on the remote domain it worked fine. I couldn’t even see any authentication requests being made to my domain controllers for the login attempts.
Here is the layout I was working with:
After a bit of trial and error I found that ESXi wasn’t able to recognise users from the trusted domain (DOMAIN1) as members of its local domain (DOMAIN2) groups. So I added the security group from the trusted domain (DOMAIN1\Remote Administrators) directly into ESXi and it was then able to authenticate users from the trusted domain correctly.
Hopefully someone finds this helpful!
1 note &
Update 02/06/2011: BUILTIN\Administrators on the domain controllers is just not enough, see Group Policy…
I needed to setup some of our domain administrators as administrators on a new prototype domain we are setting up. There are plenty of resources on setting up trust-relationships between domains in the same forest but what about between two separate, unrelated forests?
Here are the steps I took:
1. Check your firewalls
This got me stuck for a while since the egress filter on the firewall at the new site wasn’t allowing all traffic via the VPN. Make sure you are not blocking any traffic between the domain controllers in each forest.
2. Setup conditional forwarder DNS zones in each network
You won’t accomplish much if each network can’t properly resolve resources in the other. Adding forwarder zones to your DNS setup in each network allows them to forward DNS requests to the other network’s DNS servers for resources in that network. eg.
Given the two forests below, we can use the following commands to create forwarder zones for both the forestX.mycompany.com zone and the appropriate reverse lookup zones.
local_dom.mycompany.com
* Subnet: 192.168.1.0/24
* DNS servers: 192.168.1.2, 192.168.1.3remote_dom.mycompany.com
* Subnet: 192.168.2.0/24
* DNS servers: 192.168.2.2, 192.168.2.3At LOCAL_DOM…
dnscmd 192.168.1.2 /ZoneAdd remote_dom.mycompany.com /DsForwarder 192.168.2.2 192.168.2.3 dnscmd 192.168.1.2 /ZoneAdd 2.168.192.in-addr.arpa /DsForwarder 192.168.2.2 192.168.2.3At REMOTE_DOM…
dnscmd 192.168.2.2 /ZoneAdd local_dom.mycompany.com /DsForwarder 192.168.1.2 192.168.1.3 dnscmd 192.168.2.2 /ZoneAdd 1.168.192.in-addr.arpa /DsForwarder 192.168.1.2 192.168.1.3You can now test it:
nslookup dc1.remote_dom.mycompany.com 192.168.1.2 nslookup 192.168.2.2 192.168.1.2 nslookup dc1.local_dom.mycompany.com 192.168.2.2 nslookup 192.168.1.2 192.168.2.2
3. Create a forest-forest trust relationship
Open the Active Directory Domains and Trusts console (domain.msc) in one of the domains. Go to the properties of the domain and, under the Trusts tab, click New Trust and enter the following details:
Users in LOCAL_DOM are now part of Everyone & Authenticated Users in REMOTE_DOM but don’t have many rights to do anything yet… You can verify this by opening Active Directory Users & Computers (dsa.msc) and changing the domain to remote_dom.mycompany.com - you can view, but not modify, the other domain.
3. Create a security group for administrators of REMOTE_DOM
Our goal is to have the domain administrators of LOCAL_DOM also administrators of REMOTE_DOM. The first thing to do is to create a security group to manage which users have rights on REMOTE_DOM - call it “REMOTE_DOM Administrators”. Make sure you create the group as either Global or Universal otherwise it will not be visible to the REMOTE_DOM domain (Domain Local scoped groups are just that, local to the domain).
Add the users who should have rights (or just Domain Admins) to this group.
4. Grant REMOTE_DOM Administrators rights in the REMOTE_DOM domain
Now open dsa.msc on the REMOTE_DOM domain. Because Domain Admins is a Global scoped group you won’t be able to add users/groups from LOCAL_DOM to it. To grant the LOCAL_DOM users full administrator rights on the REMOTE_DOM domain you need to add them to the BUILTIN\Administrators group which is locally scoped.
Open the group and click Add under the Members tab. Change the location from remote_dom.mycompany.com to local_dom.mycompany.com and add the LOCAL_DOM\REMOTE_DOM Administrators group.
Setup a Group Policy to grant administrator rights across the domain.
Although after completing step 4 you’ll have Administrator rights on the domain controller, this just isn’t the same as Domain Admins. The first thing you’ll notice is that you have only user rights on other machines in the domain.
This policy will now be applied to all computers on the domain, granting members of “LOCAL_DOM\REMOTE_DOM Administrators” administrative rights on all machines.
Note: There are still permissions that Domain/Enterprise Admins have that are not granted using this procedure (eg. editing Group Policy Objects). I’m still working through them - any suggestions welcome!
