Brad Marsh’s Weblog

Here @ my current work place Elcom we are heavy uses of FTP for various methods. However we have recently been asked to supply a client with a particular solution that requires a Secure FTP solution, while our current FTP allows FTPS (SSL) we wanted something will a little more security, so we opted to go the SSH route also known as SFTP, I have looked high and low for suitable FTP applications that have all protocols SFTP, FTPS and plain old FTP, I have since found that while there are many app’s that do this, some are just much too pricey and some well the interface is poorly thought out and hard to navigate through.

After hours of trailing paid software, I found that they just didnt do exactly what I required, or maybe what I expected.

So I when on the hunt again this time looking for GNU based software, and to my surprise I found one that has all protocols.

It has a simple but yet easy to navigate and work through, and even better it integrates with NT authentication, when I say NT authentication I am talking about Active Directory…. and its FREE.

It even Supports Virtual Directories.

While there could be more with users and assigning them specific paths, rather than global directories, I really can’t complain given its price tag.

FreeFTd can be found here

http://www.freeftpd.com

Twitter This

Usuario: Password:

When attempting to modify a table design (by right clicking on the table and select ‘design’) you get and error something along the lines of:

Saving change is not permitted. the changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that cant be re-created or enabled the option Prevent Saving changes that require the table to be re-created.

You can fix this quite easily by the following method

Tools Menu > Options > Designers > Untick ‘Prevent saving changes that require a table re-creation’

Done

Twitter This

Usuario: Password:

Ok due to high demand, and for some very odd reason 2007 was not writing the .txt file and the .rtf.

Now I have implemented various changes so it does this, however due to time constraints its not as
automated as I would like, I will try to review this when I get some free time (hopefully ).

So if you are using the old script the only changes are:

‘=========================================================
‘ This adds the Text file, will need to be changed accordingly
‘==========================================================

Set objFile = objFSO.CreateTextFile(Folderlocation&”Elcom.txt”,True)
objfile.writeLine “”& FullName & ” | ” & title & ” |  ” & title & ” | “& Company & ” | Australian Technology Park”
objfile.writeLine “T +612 ” & PhoneNumber & ” | F +612 9209 4423 | www.elcom.com.au | ” & Email &” ”
objfile.writeLine ” ”
objfile.writeLine ” ”
objfile.writeLine “——————————————————————————–”
objfile.writeLine “This email is intended for the intended recipients(s) and may contain confidential information. ”
objfile.writeLine “Reproduction, dissemination or distribution of this message is prohibited unless authorised by the sender.”
objfile.writeLine “If you are not the intended recipient, please notify the sender immediately and you must not read,”
objfile.writeLine “keep, use, disclose, copy or distribute this email without the sender’s prior permission.”
objfile.writeLine “The views expressed by the sender are not necessarily those of Elcom Technology Pty Ltd ”
objFile.Close

as you can see I have had to write this out again (without the HTML tags), this is far from perfect but
it is a working fix for the time being. You will have to edit the above to suite your Text based
Signature.

Next addition is

‘====================================================
‘ This copies the .htm file and changes it to a RTF format
‘====================================================

‘Set the Source and Destination paths below – CHANGE your file name
‘—————————————————-

Const OverwriteExisting = True
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
objFSO.CopyFile Folderlocation & “Elcom.htm” , Folderlocation & “Elcom.rtf”, OverwriteExisting 

Being that RTF will show a HTML format this is any easy fix, all you have to do with this is change the
file name which we set in the begining of the script. So for example I would change “Elcom.htm” to
“MyCompany.htm”

  Here is the complete code

'====================

'

' VBScript: <Signatures.vbs>

' AUTHOR: Peter Aarts

' Contact Info: peter.aarts@l1.nl

' Version 2.04

' Date: January 20, 2006

' Moddified By Brad Marsh Now works with both 2003 and 2007 outlook 

' Contact: gentex@tpg.com.au

' Date 19 feb 08

' Tested on Vista, XP, XP64 and office 2003 and 2007. 

' NOTE will not work that well with various email accounts

' Additions added on 20 Nov 08 - Tested on Office 07 and Vista 

'====================  

'Option Explicit

On Error Resume Next

Dim qQuery, objSysInfo, objuser

Dim FullName, EMail, Title, PhoneNumber, MobileNumber, FaxNumber, OfficeLocation, Department

Dim web_address, FolderLocation, HTMFileString, StreetAddress, Town, State, Company

Dim ZipCode, PostOfficeBox, UserDataPath

' Read LDAP(Active Directory) information to asigns the user's info to variables.

'====================

Set objSysInfo = CreateObject("ADSystemInfo")

objSysInfo.RefreshSchemaCache

qQuery = "LDAP://" & objSysInfo.Username

Set objuser = GetObject(qQuery)

FullName = objuser.displayname

EMail = objuser.mail

Company = objuser.Company

Title = objuser.title

PhoneNumber = objuser.TelephoneNumber

FaxNumber = objuser.FaxNumber

OfficeLocation = objuser.physicalDeliveryOfficeName

StreetAddress = objuser.streetaddress

PostofficeBox = objuser.postofficebox

Department = objUser.Department

ZipCode = objuser.postalcode

Town = objuser.l

MobileNumber = objuser.TelephoneMobile

web_address = "http://www.elcom.com.au"

' This section creates the signature files names and locations.

'====================

' Corrects Outlook signature folder location. Just to make sure that

' Outlook is using the purposed folder defined with variable : FolderLocation

' Example is based on Dutch version.

' Changing this in a production enviremont might create extra work

' all employees are missing their old signatures

'====================

Dim objShell, RegKey, RegKey07, RegKeyParm

Set objShell = CreateObject("WScript.Shell")

RegKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common\General"

RegKey07 = "HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common\General"

RegKey07 = RegKey07 & "\Signatures"

RegKey = RegKey & "\Signatures"

objShell.RegWrite RegKey , "AD_elcom"

objShell.RegWrite RegKey07 , "AD_elcom"

UserDataPath = ObjShell.ExpandEnvironmentStrings("%appdata%")

FolderLocation = UserDataPath &"\Microsoft\AD_elcom\"

HTMFileString = FolderLocation & "Elcom.htm"

' This section disables the change of the signature by the user.

'====================

'objShell.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common\MailSettings\NewSignature" , "L1-Handtekening"

'objShell.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common\MailSettings\ReplySignature" , "L1-Handtekening"

'objShell.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Options\Mail\EnableLogging" , "0", "REG_DWORD"  

' This section checks if the signature directory exits and if not creates one.

'====================

Dim objFS1

Set objFS1 = CreateObject("Scripting.FileSystemObject")

If (objFS1.FolderExists(FolderLocation)) Then

Else

Call objFS1.CreateFolder(FolderLocation)

End if

' The next section builds the signature file

'====================

Dim objFSO

Dim objFile,afile

Dim aQuote

aQuote = chr(34)

' This section builds the HTML file version

'====================

Set objFSO = CreateObject("Scripting.FileSystemObject")

' This section deletes to other signatures.

' These signatures are automaticly created by Outlook 2003.

'====================

Set AFile = objFSO.GetFile(Folderlocation&"Elcom.rtf")

aFile.Delete

Set AFile = objFSO.GetFile(Folderlocation&"Elcom.txt")

aFile.Delete

'=========================================================

' This adds the Text file, will need to be changed accordingly

'========================================================== 

Set objFile = objFSO.CreateTextFile(Folderlocation&"Elcom.txt",True)

objfile.writeLine ""& FullName & " | " & title & " |  " & title & " | "& Company & " | Australian Technology Park"

objfile.writeLine "T +612 " & PhoneNumber & " | F +612 9209 4423 | www.elcom.com.au | " & Email &" "

objfile.writeLine " "

objfile.writeLine " "

objfile.writeLine "--------------------------------------------------------------------------------"

objfile.writeLine "This email is intended for the intended recipients(s) and may contain confidential information. "

objfile.writeLine "Reproduction, dissemination or distribution of this message is prohibited unless authorised by the sender."

objfile.writeLine "If you are not the intended recipient, please notify the sender immediately and you must not read,"

objfile.writeLine "keep, use, disclose, copy or distribute this email without the sender's prior permission."

objfile.writeLine "The views expressed by the sender are not necessarily those of Elcom Technology Pty Ltd "

objFile.Close

Set objFile = objFSO.CreateTextFile(HTMFileString,True)

objFile.Close

Set objFile = objFSO.OpenTextFile(HTMFileString, 2)

objfile.write "<!DOCTYPE HTML PUBLIC " & aQuote & "-//W3C//DTD HTML 4.0 Transitional//EN" & aQuote & ">" & vbCrLf

objfile.write "<HTML><HEAD><TITLE>Microsoft Office Outlook Signature</TITLE>" & vbCrLf

objfile.write "<META http-equiv=Content-Type content=" & aQuote & "text/html; charset=windows-1252" & aQuote & ">" & vbCrLf

objfile.write "<META content=" & aQuote & "MSHTML 6.00.3790.186" & aQuote & " name=GENERATOR></HEAD>" & vbCrLf

objfile.write "<body>" & vbCrLf

objfile.write "<font color=696969 face=" & aQuote & "Arial" & aQuote & "><h6> "& FullName & " | " & title & " | "& Company & " | Australian Technology Park <br>"& vbCrLf

objfile.write "T +612 " & PhoneNumber & " | F +612 9209 4423 | <a href=http://www.elcom.com.au><font color=#696969>www.elcom.com.au</font></a> |<a href=mailto:" & Email &" > <font color=#696969>" & Email &" " & vbCrLf

objfile.write "</h6></font></a></B>" & vbCrLf

objfile.write "</font>" & vbCrLf

objfile.write "<font color=696969 face=arial><A href=http://www.elcom.com.au/> <img src=http://www.elcom.com.au/images/elcom-logo-web-175x70.gif border=0></a>" & vbCrLf

objfile.write "<br>" & vbCrLf

objfile.write "<img src=http://www.elcom.com.au/images/mspartner.jpg>" & vbCrLf

objfile.write "<br>" & vbCrLf

objfile.write "</font><font color=696969 size=1 face=arial><br>" & vbCrLf

objfile.write "<hr size=1 align=left width=465 color=696969>" & vbCrLf

objfile.write " This email is intended for the intended recipients(s) and may contain confidential information. <br> Reproduction, dissemination or distribution of this message is prohibited unless authorised by the sender.<br> If you are not the intended recipient, please notify the sender immediately and you must not read,<br> keep, use, disclose, copy or distribute this email without the sender's prior permission.<br> The views expressed by the sender are not necessarily those of Elcom Technology Pty Ltd</font>"  & vbCrLf

objfile.write "</FONT></BODY></HTML>" & vbCrLf

objFile.Close

'==========================================================================

' This copies the .htm file and changes it to a RTF format

'==========================================================================

'Set the Source and Destination paths below - CHANGE your file name

'---------------------------------------------------- 

Const OverwriteExisting = True

Set objFSO = CreateObject("Scripting.FileSystemObject")

objFSO.CopyFile Folderlocation & "Elcom.htm" , Folderlocation & "Elcom.rtf", OverwriteExisting

' ===========================

' This section readsout the current Outlook profile and then sets the name of the default Signature

' ===========================

' Use this version to set all accounts

' in the default mail profile

' to use a previously created signature  

Call SetDefaultSignature("Elcom","")

' Use this version (and comment the other) to

' modify a named profile.

'Call SetDefaultSignature _

' ("Signature Name", "Profile Name")  

Sub SetDefaultSignature(strSigName, strProfile)

Const HKEY_CURRENT_USER = &H80000001

strComputer = "."

If Not IsOutlookRunning Then

Set objreg = GetObject("winmgmts:" & _

"{impersonationLevel=impersonate}!\\" & _

strComputer & "\root\default:StdRegProv")

strKeyPath = "Software\Microsoft\Windows NT\" & _

"CurrentVersion\Windows " & _

"Messaging Subsystem\Profiles\"

' get default profile name if none specified

If strProfile = "" Then

objreg.GetStringValue HKEY_CURRENT_USER, _

strKeyPath, "DefaultProfile", strProfile

End If

' build array from signature name

myArray = StringToByteArray(strSigName, True)

strKeyPath = strKeyPath & strProfile & _

"\9375CFF0413111d3B88A00104B2A6676"

objreg.EnumKey HKEY_CURRENT_USER, strKeyPath, _

arrProfileKeys

For Each subkey In arrProfileKeys

strsubkeypath = strKeyPath & "\" & subkey

objreg.SetBinaryValue HKEY_CURRENT_USER, _

strsubkeypath, "New Signature", myArray

objreg.SetBinaryValue HKEY_CURRENT_USER, _

strsubkeypath, "Reply-Forward Signature", myArray

Next

Else

strMsg = "Please shut down Outlook before " & _

"running this script."

MsgBox strMsg, vbExclamation, "SetDefaultSignature"

End If

End Sub

Function IsOutlookRunning()

strComputer = "."

strQuery = "Select * from Win32_Process " & _

"Where Name = 'Outlook.exe'"

Set objWMIService = GetObject("winmgmts:" _

& "{impersonationLevel=impersonate}!\\" _

& strComputer & "\root\cimv2")

Set colProcesses = objWMIService.ExecQuery(strQuery)

For Each objProcess In colProcesses

If UCase(objProcess.Name) = "OUTLOOK.EXE" Then

IsOutlookRunning = True

Else

IsOutlookRunning = False

End If

Next

End Function

Public Function StringToByteArray _

(Data, NeedNullTerminator)

Dim strAll

strAll = StringToHex4(Data)

If NeedNullTerminator Then

strAll = strAll & "0000"

End If

intLen = Len(strAll) \ 2

ReDim arr(intLen - 1)

For i = 1 To Len(strAll) \ 2

arr(i - 1) = CByte _

("&H" & Mid(strAll, (2 * i) - 1, 2))

Next

StringToByteArray = arr

End Function

Public Function StringToHex4(Data)

' Input: normal text

' Output: four-character string for each character,

' e.g. "3204" for lower-case Russian B,

' "6500" for ASCII e

' Output: correct characters

' needs to reverse order of bytes from 0432

Dim strAll

For i = 1 To Len(Data)

' get the four-character hex for each character

strChar = Mid(Data, i, 1)

strTemp = Right("00" & Hex(AscW(strChar)), 4)

strAll = strAll & Right(strTemp, 2) & Left(strTemp, 2)

Next

StringToHex4 = strAll

End Function

Enjoy

Twitter This

Usuario: Password:

Recently I applied another ROM (yes another one seem as though I am changing ROM’s as often as my undies) because I am sick of waiting for windows mobile 7.

So anyway’s I found myself an AWSOME windows mobile 6.1 rip off of the HTC touch series (diamond, PRO and HD)

called HTC_HERMES_S64_WM_6.1_TouchFlo2D_UC_ WWE v5 / GER v1 BIG THANKS to:

_Alex_, A_C, abusalza, anichillus, anryl, bennec83, Bepe, bluemetalaxe, CRCinAu,
dotfred, Dr Puttingham, dutty, duttythroy, efrost, gullum, herg62123, kin0kin,
Koterpillar, l3v5y, lagoskon, lepsyfou, levenum, Lordsmiff, MaRaHoX, Martie, mattk_r
Mort, mrvanx, N2A, no2chem, patr!k, PaY87, poorlyduck, Rhapsody, s.l.i, sakajati,
schaps, schen, Shamanix, shogunmark, Slither2006, tadzio, zocster
with friendly greet
Starbase64

Awesome ROM

Anyway’s, if you are like me and have the good old Dopod 838 Pro, and loaded a new ROM on that is a different country; you might have some keyboard problems.

First problem you might find is that it might not be the QWERTY keyboard but a QWERTZ (German).

Second is that you will find characters more accurately punctuation all over the place , which is just annoying when your in a rush to type something up.

So in order to fix these problems you are going to need a registry editor for your device, there are many around that are free. But I recently stumbled across one that runs as an .exe from a desktop machine, and when your device is in sync it will read from it and allow you to modify the registry, this works perfectly you can find it here

Once you see the registry up and running you need to change the following registry keys:

HKEY_LOCAL_MACHINE\Software\Oem\Qwerty

“layout” = “132105″

HKEY_CURRENT_USER\keyboard layout\Preload\

“Default”=”e0010409″

HKEY_CURRENT_USER\keyboard layout\Preload\1

“Default”=”e0010409″

restart your PDA.

DONE, you now have your keyboard back to normal

If you have another phone the below could possibly help you

http://forum.xda-developers.com/showthread.php?t=428597

Twitter This

Usuario: Password:

Creating a Linked Server in MSSQL for a MySQL database
1. Download the MySQL ODBC driver from mysql.com (download from here) and Install MySQL ODBC driver on the MSSQL Server

2. Create a DSN using the MySQL ODBC driver

Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC)

> Click on the System DSN tab
> Click Add
>Select the MySQL ODBC Driver
> Click Finish

On the Login Tab:
> Type a name for your DSN.
> Type the server name or IP Address into the Server text box.
> Type the username needed to connect to the MySQL database into the user text box.
> Type the password needed to connect to the MySQL database into the password text box.
> Select the database you want to be able link to.

> make sure you test your DSN by Clicking the ‘Test’ button

NOTES: If your test was unsuccessful, you may need to add the server you are connecting from to the allowed list, and ensure the user you are using has privileges to the database you are trying to link to

3. Creating a Linked Server in SSMS (SQL Server Management Studio) for the MySQL database

there is 2 ways of doing this,

1. Using the GUI

SSMS (SQL Server Management Studio -> Expand Server Objects
> Right Click Linked Servers -> Select New Linked Server
On the General Page:
> Linked Server: Type the Name for your Linked Server
> Server Type: Select Other Data Source
> Provider: Select Microsoft OLE DB Provider for ODBC Drivers
> Product name: Type MySQLDatabase
> Data Source: Type the name of the DSN you created
On The Security Page
> Map a login to the Remote User and provide the Remote Users Password
> Click Add under Local server login to remote server login mappings:
> Select a Local Login From the drop down box
> Type the name of the Remote User
> Type the password for the Remote User

OR

2. through TSQL

– Add Linked Server
EXEC sp_addlinkedserver ‘mysqlDB’, ‘MySQL’, ‘MSDASQL’, Null, Null, ‘Driver={MySQL ODBC 5.1 Driver};DB=[DB_NAME];SERVER=[HOSTNAME];uid=[USER];pwd=[PASSWWORD]‘

Note: Change items enclosed with [  ] and the bold Items

Now test your linked server

if you are unable to connect there could be sevral problems here are a few things to check

Expand Providers > Right Click MSDASQL > Select Properties
> Enable Nested queries (not needed but handy)
> Enable Level zero only (this one’s the kicker)
> Enable Allow inprocess (this is a MUST)
> Enable Supports ‘Like’ operator (not needed but handy)

Change settings in SQL Server Surface Area Configuration for Features
> Enable OPENROWSET and OPENDATASOURCE support.

Restart SQL Server and SQL Server Agent

Run this TSQL (using the master database)

– Set up login mapping using current user’s security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘mySQLDB‘,
@useself = ‘TRUE’,
@locallogin = NULL
GO

 
Note: change the bold items

Ok so now you should be able to connect

use this to ensure you can query you Link server

– List the tables on the linked server
EXEC sp_tables_ex ‘mysqlDB’
GO

 Note: change the bold items

Now if you need to query your table, you cant use the typical select statements, like

SELECT * FROM data.data1..test
or
SELECT * FROM data.data1.user.test

Seems as though not everything is exposed properly, your not going crazy!

using something like this will work

SELECT * FROM OPENQUERY(data, ‘SELECT * FROM TEST’)

this link explains it a lot better

http://benjolo.com/blog/2003/08/27/mysql-mssql-linked-tables-or-just-kill-me-now/

Twitter This

Usuario: Password:

Today I was getting a strange error from the file server Resource manager when I was trying to save a quota, but upon the save I was getting the following error:

“The Quota Management Filter Driver Service or File Screening Filter Driver service is not running”

Simple fix is:

open a command line

start > run > type  ‘cmd’

then type
‘fltmc filters’  – (this command will check which filters are activated)

you should see 4 items if you only see 2 then you are missing a few things:

If Quota is missing type the following at the command prompt.

‘fltmc load Quota ‘

If Datascrn is missing type the following at the command prompt.
fltmc load Datascrn

Now you should be able to access the File Screen in the File Screening Management, and save your quotas etc.

Twitter This

Usuario: Password:

this below article was published byNigel Maneffa, on www.sqlservercentral.com

I found it quite a good overview, while there is some ranting and raving, as to which I agree with, however after using the management studio for SQL 2008 (dev edition) I can see other undocumented and documented enhancements which are in standard edition, find them at the very bottom

I had downloaded and played a little with the SQL2008 CTPs, and the new features were impressing me, although it appeared that SQL2008 was a development of SQL2005, as SQL2000 was a development of SQL7 despite comments to the contrary. New headline features included backup compression, governors, policy framework, data compression, encryption, change data capture etc. But then I read the recently produced Microsoft documentation on version differences and see that none of them are in Standard edition, not one. They are all Enterprise features. SQL server may be moving more towards a data platform from just a database engine, but the platform appears to be somewhat narrow for non Enterprise users!

The official list of differences can be found here: http://download.microsoft.com/download/2/d/f/2df66c0c-fff2-4f2e-b739-bf4581cee533/SQLServer%202008CompareEnterpriseStandard.pdf

This posed a question – is it worth upgrading from SQL2005 Standard edition to SQL 2008 Standard edition?

Maybe we have been spoilt by Microsoft ‘giving away’ too many new features in the upgrade from SQL2000 to SQL2005. The potential performance/scalability differences between a say 32 bit 2 GB RAM SQL2000 Standard on NT4 Server and a 64 bit 32 GB RAM SQL2005 Standard on W2K3 Standard system is very large indeed, despite both the OS and SQL Server version being the Standard edition (a likely OS/SQL combination).

SQL2005 Standard offered such new goodies as clustering, unlimited RAM, mirroring (safety always on unfortunately), and log shipping – the unlimited RAM (to OS max) was a real eye opener when I saw the specification sheet, although the price of RAM made such levels of RAM just a paper exercise for typical Standard edition users. That is no longer the case, as RAM is now much more affordable. I would have capped the RAM on SQL2005 Standard edition to around the 8 GB mark, allowing an increase to say 16 GB in SQL2008. The 4 processor limit was a good selection IMO, although I can see disk IO being far more of a bottleneck in most servers of this specification.

SQL2005 Standard (still) did not support indexed views during optimisation, but I can afford to learn a bit about ‘noexpand’ with the money I had saved and still use the feature when required. As for partitioned data, that was ‘just for people with terabyte databases’ and data warehousing, and I would never see that sort of disk IO anytime soon. One feature that would have been useful was database snapshots, but I could see they would need to differentiate the versions. The parallel indexing I could live without too. They even threw in the (little used) CLR and service broker, and the (doomed) notification services.

One of the subsystems that I frequently use very heavily, merge replication, had so many new features in SQL2005 I could hardly keep count. I could also see on paper improvements for DTS/SSIS and reporting, but had written my own systems many years ago when SQL Server did not have them, so cannot comment on their improvements in practise.

Upgrading from SQL2000 Standard to SQL2005 Standard provided many new features. I remember the discussion at the time, when many thought that SQL2000 was good enough for many applications. I still think that is the case today to be truthful, but have used quite a few of the new SQL2005 features over the last year or two.

However, let’s look at what SQL2008 Standard offers over 2005 Standard.

• There are no processor or RAM improvements, not that they are required any more. Mirroring looks like it might be improved marginally.
• I am really peeved to see that backup compression has not made it to the Standard feature list.
• There are the usual incremental improvements in SSIS and Reporting, again which I do not use.

To cap it all the (to me) business critical merge replication has gained no features, but its growing deprecated item list makes it look more like it’s on the way out than moving ahead. The new sync services look interesting in the longer term, but offer nothing except a likely version 1.0 buglist for those wishing to take the early adopter plunge. With the massive increase in the number of Enterprise features I would have thought it would make great sense to throw in a few of the ‘old’ Enterprise features into the Standard edition – database snapshots would have been nice, or lock pages in memory.

Forgive me if I feel short changed, but that feature list does not look a great reason to upgrade. Or I have I failed to turn over the magic stone with hidden features?

So I am left looking at programming/development enhancements – date/time fields, HierarchyID, spatial, merge SQL statement etc. Nice but not critical. There are 2 features that have gained relatively little attention but might improve application performance dramatically for the applications I see – optimize for ad hoc workloads, and filtered indexes. It appears on the surface that these features apply to all editions.

So I am left with the question of whether to upgrade, or wait till the next version of SQL server. Or in the longer term investigate ‘cheaper’ alternatives such as MySQL, which will become ever more viable as the performance of hardware hides any performance deficiencies (either perceived or real). Bearing in mind I have ‘free’ upgrade rights within our Microsoft licensing agreement, I would be very worried if someone that can upgrade for free cannot see the benefits of using the new version.

A related topic is that the CTP’s and developer versions of SQL Server are in effect Enterprise versions, with all the features always on. I can understand this (particularly with the CTPs), but surely it must be simple to add a switch to tell developer version which edition you wish to emulate, so it is possible to develop against the edition that will be used in production. This problem was annoying in SQL2005, but the feature is critically required now, with the vast difference in feature set. I see that there is now a management view that tells you which Enterprise features need to be dropped to use a database in Standard edition.

It could be that Microsoft has done their homework well, and are really targeting the ‘Enterprise’ customer, and are less concerned about the Standard version than they were a few years ago as SQL Server moves more upmarket. I think that Microsoft should make sure that everyone gets something from upgrading, not just being forced to do so as the support for the older product eventually runs out. If they don’t provide improvements I can see slippage of SQL Server customers at the lower to middle range products.

Of course, if you run SQL 2000 Standard, upgrading to SQL2008 Standard (or maybe workgroup depending on which features you require) would be a great upgrade. Or am I already getting good value for money and should just be pleased that I am running a very reliable database system, and be happy to pay extra if I need any of the new features.

What do you think?

By Nigel Maneffa, 2008/10/21

So I said some undocumented changes, well at least I think they are undocumented, I have not yet come across any reading about them.

1. When you are restoring a file from the a device, and lets say the backup came from a different server, and the SQL data
and Logs resided in a different drive or location, you would always seem to have to go to the ‘options’ tab and change the location to your new location, even with all the Service Packs applied SQL 2005 continued to annoy me with this, while funnily enough SQL 2000 corrected the location automatically. Seems 2008 also does this too…

2. This is documented but worth bringing up powershell integrated with SQL 08, whilst we could powershell with 05, its just made it a little quicker by allowing us to click on the database we want to start powershell in, just another time saver.

3. Also documented but again a nice time saver rather than having to run TSQL statements to find info about the database, maintainance tasks, security, performance and more all we now have to do is right click the database and choose ‘Facets’

there are some other small functions that are helpful, which is across the most SQL 08 range.

I am also VERY disappointed that the backup compression / encryption didn’t make the cut in std edition, this should not be just an enterprise feature!

Twitter This

Usuario: Password:

Google Chrome has now been released as Beta, you can download it here

http://www.google.com/chrome/index.html?hl=en&brand=CHMG&utm_source=en-hpp&utm_medium=hpp&utm_campaign=en

I love new software, technology anything I can get my hands on, so now google is my latest test baby and do I like it…?

well it has got a clean easy interface, super easy to use and in comparison to other browsers firefox 3 and IE7 well it seems slightly faster then both, google also has done the same thing as Mozilla where it needs pluggins for everything, which I quite like.

Also loving the Developer built in functions, the inspector is brilliant, it also seems light weight and works a treat, where you select the portion of code it highlights the part on the site where this event occurs (whilst we know IE dev toolbar, and Mozilla do the same, but this seems easy on the eye and light weight)

Now enough of the good stuff, here comes the ranting….

I have found some sites that just don’t render right, alignment is out, images misplaced and some sites just total screwed up, where in the other major browser it looks perfect.

And to be frank it just plan pisses me off, so here is another browser that we are going to have to cater for, while the 3 major players in browsers are IE, Mozilla and Safari, being Google is Google, we know its  only a matter of time before this browser becomes one of the big boys in browsers.

So why, why oh why don’t we have some sort of CSS compliance between all browsers, do they just like to create more unpaid work for us??

Sure its in beta, I just hope that they get do something to correct this.

fingers crossed… and toes.

Twitter This

Usuario: Password:

Thought I might do a quick blog about creating a one way trust, as I found there to be little text on this following scenario, where the primary domain has access to the other domain, but the secondary domain has only access to itself.

Ok so if you have the same requirement, first thing is don’t bother creating a child domain within the same forest of the current domain, you can’t seem to create a one way, buy default Microsoft will create a 2 way trust. So that means you must create a domain in  a separate forest, whilst there is a bit more involved in setting up the forest in terms of allowing domain admin’s rights to manage the other domain etc, it will be well worth it in the long run from a security perspective.

Anyways, do your DC promo and create a new forest, and follow the prompts there is stacks of text on this so I am not going to ramble on about this one.

Once the new domain is created, open ‘Active Directory Domains and Trusts’ on the primary domain find the domain right click, properties. go to the ‘trusts’ tab.

(before you do this setup you most likely will have DNS issues, I would spend a bit of time sorting that out first other wise the next steps will not work)

Now you should have nothing there are present.

here you will have enter the other forests domain, choose trust with a windows domain, then next.

Make sure you choose a one way: incoming

 THIS is really important, if you don’t choose ‘This domain only’  it will NOT create a one way trust in the way that we want.

enter a password for the trust

Here say no, because at this point you only have the one trust so you have nothing to confirm the trust with at this point.

No go back and do the same with other domain however the only difference is your looking for the primary domain as a trust and you will need to specify a outgoing trust:

“Outgoing: Users in the specified domain can authenticate in the local domain, but users in the local domain cannot authenticate in the specified domain.”

Once you have done this then go to the properties and then you will see a validate, click on this put your administrator credentials for each domain, on completion it should give you the below message:

That’s it you have a one way trust!

for more reading on this have a look at: http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/deploy/dgbe_sec_ztsn.mspx?mfr=true

Twitter This

Usuario: Password:

This post is a follow on from a previous post ‘Complete Guide to SQL 2005 mirroring using the GUI‘

Now we know when Mirroring in the ‘High safety with automatic failover’ mode it will automatic failover, which works superbly, but a few questions for you, are you using SQL authentication for you databases? have you tried the failover and then tried to call your app through the failover server now as the principle server? Do you manually change your .config or use a script to do a find and replace if your web application is using a connection string?

If you answered

Yes to SQL authentication
No to trying the failover
Yes to manually change the connection string

this post if for you read on…

Lets start with the connection string, now hopefully your application is using .NET 2.0 or above, if it is you will be very happy to know you don’t have to script anything for when a server dies and your mirrored database takes over, for those using 1.1 or below UPGRADE, but if you cant well I under stand, you might want to read another post about a find and replace tool I found that will make your scripting life much easier for the failover it can be found here, anyway back to the .NET 2.0 users, if you are using a mirror then you will be very happy Microsoft have included some additional functionality within the connection string, where you can specify a failover partner, this will allow you to enter the failover’s server name or IP and once the principle mirror has failed you need not worry if you have the failover partner string within the connection string, it will detect this and failover immediately.

Here is an example:

Server=MyPrincipleDatabaseServer; Failover Partner=MyFailoverDatabaseServer;; Database=AdventureWorks;

it works perfectly, try it, you will be amazed.

find more info on the above here

Ok so manually change the connection string….. Check

now moving along to SQL authentication

If you use SQL authentication and lets say you take a backup of database and then restore it to another SQL server, what one thing that you will always have to do….?

give up… You will have to remove the user and then re-add the user to the database, why because the SID (security Identifier) is different on each SQL server, the same applies to mirroring, when you failover even though you are using the same user and password for the SQL authentication, the app / database will fail to work because once again just like a restore you will have to delete the user and then re-create it, one massive pain in the but, especially if you want automatic failover and then it does failover in the middle of the night, but yet all your databases still fail, now wouldn’t that just be a kick in the face.

However we have an easy fix for that, all you need to do is make the failover database SID the same as the principles SID for that user, or all users that you are using for the mirrored databases.

So first thing is getting the SID of the principle SQL server.

use the below script: (use master for the query)

SELECT 

'create login [' + p.name + '] ' + 

case when p.type in('U','G') then 'from windows ' else '' end +

'with ' +

case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +

'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +

case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +

case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end 

else ''<
/span> end +

'default_database = ' + p.default_database_name +

case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end

FROM sys.server_principals p

LEFT JOIN sys.sql_logins l

ON p.principal_id = l.principal_id

LEFT JOIN sys.credentials c

ON  l.credential_id = c.credential_id

WHERE p.type in('S','U','G')

AND p.name <> 'sa'

once run you should see something like (you would probably have a heck of a lot more users)

Ok so find the user you want to replicate the SID on your Failover, for this example I am using test_user

The below is what I have pulled out directly from the above query for my Test_user

create login [test_user] with password = 0×01004981172537214b13a072a6674d0316673b48cbdadd184d3b hashed, sid = 0×6f7bd56e4f79ab4d89247b79180db390, check_expiration = OFF, check_policy = OFF, default_database = master, default_language = us_english

So now I want to copy user above and take its SID and re-create the user on my fail over so I would now go to the failover database and then run 

Use Master

create login [test_user] with password = 'PasswordGoesHere', sid = 0x6f7bd56e4f79ab4d89247b79180db390, check_expiration = OFF, check_policy = OFF, default_database = master, default_language = us_english

REMEMBER: to remove the hashed password replace it with a clear text password, I also advise to keep the password expiration off, other wise you will find yourself doing this task again and again, or worse yet forget about it and then when the SQL server fails, so does the mirror

Now that that is done time for that testing you answered No to, so lets start the test:

(I do suggest you try this on a dev environment before you do this on production)

go on to the primary and then tasks > Mirror > click on Failover

your failover I should now failover quickly and smoothly, without any manual intervention.

Now just one last thing that might be helpful, if you are running a mirror and your failover server is not the same spec as your Primary SQL server, or you just don’t want the databases to run on the failover server until the next failover, then you can run a TSQL script to make them go back to the primary database server:

Use master

ALTER DATABASE DatabaseNameHere SET PARTNER FAILOVER

go

Repeat this for each database, if you have hundreds of databases, do a SP_database and then copy to excel and then save to CSV and the play with it that way.

Twitter This

Usuario: Password: