Change the path SQL 2008 Server Database

August 30th, 2009

In the most cases there are benefits when all data (log and db) files are on the network storage. Think off the benefit in terms of performance and backup. The Install wizard of Microsoft SQL Server 2008 shows only the option to change the path of the temporary database. In this guide I show you how to move the databases. Please keep in mind to create a backup of your db’s before starting. I know backup is only for hot shower takers, gym bag losers and armpit hair blow-dryers;-). On my test server the path looks like:

master.mdf, masterlog.ldf

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\DATA

Model.mdf, modellog.ldf

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\DATA

MSDBdata.mdf, MSDBlog.mdf

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\DATA

Tempdbv.mdf, templog.ldf

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\DATA

planetgeek.mdf, planetgeek_log.ldf

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\DATA

First thing you notice the filenames are all tuned in the same schema, pls. Microsoft it cannot be so hard. Our mission is to move all log files in the path D:\mssqlserver08 and all data files to E:\mssqlserver08. The first thing we should do is give the sqlserver service account user read and write rights to this two directories. This step is not quiet necessary but the remote db creation and auto grow features will not work. Let’s start with the master db. Start the SQL Server Configuration Manager. Click “start” -> “run” and type “SQLServerManager10.msc” and right click on the properties from the SQL server Service.

clip_image002

In the advanced tab you have to edit the Startup Parameters

clip_image004

The default value is (keep in mind there are no spaces!!!):

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\DATA\master.mdf;

-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\Log\ERRORLOG;

-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\DATA\mastlog.ldf

-d is the path to the master.mdf

-e path of the “ERRORLOG” File.


-l is path to the ldf File

After change the path to (keep in mind there are no spaces!!!):

-dD:\mssqlserver08\master.mdf;

-eD:\errorlog\ERRORLOG;

-lE:\mssqlserver08\mastlog.ldf

stop the sql server (for cluster use you can use an UNC path and share name. This is useful on relaxed security cluster environments). Copy the master db files to the new path. And start the SQL server Service. One step done four steps are left, so let’s move on with the temporary db. Open the SQL Server Management Studio and open a new query and enter the following lines

clip_image006

After the alter database statement you need to stop the Sql Server move the files in explorer to their new location and start the SQL Server Service. Many of you are maybe wondering why “ … name = tempdev, …” and the “name = templog” in the SQL query. This is the internal database name. A very easy name to get this name is stored procedure sp_help

clip_image007

with this procedure you are able to easily modify the path of all other databases. Normally we would have finished at this point. But after then years of experience as IT guy I know that “developers” often don’t care about path in the file system (developers who write for planetgeek are not this kind of developers ;-) . So we should change the default database creation path to ensure it will work even when we are not in the office (Yes the IT Professionals have Holydays;-).

clip_image009

Enjoy the comfort of non direct attached storage, RIDE ON

Konrad

Big Brother on Amazon

August 6th, 2009

I’m Currently in My Yearly Military Reputation curse (WK). So i hade many time to read newspaper. At Thursday the 21 of July there was an nice article in the “Tages Anzeiger” one of the best Newspaper in Swiss. Amazon has an nice e-book reader called Kindle. The device has an “Kill switch” trough this access port Amazon had full control of the device. Many of the next gen devices have this kind of access like the iPhone, Vista, Xbox and PS 3…. So Amazon sells books without have the digital publishing rights, uses the kill switch to delete these books from their readers and give the customer the money back. the book that was deleted was 1984 from Orwell Gerorge. For all geeks how don’t have read the book READ IT. The story goes like Boy meets girl in London, the story plays in an totalitarian society led by Big Brother. The beig borther controls the mind of all citizens. To fore his control the big Brother delets books.

Nice Irony I guess

Ask a Geek Question, most useful mac os x apps.

July 12th, 2009

Martin one of are famous blog reader submitted an wish, … Would love to see kind of most useful mac os x apps. I know there are lots of other sites on the web taking care of it already, but hey guys its a growing community. Just a hint. Well keep up your great work. Martin.

The question is not so easy as it guess. Because Mac OS X leopard has many of the things I nee built-in. Let us start with some basic tools I Use.

VMware Fusion. is a virtual machine software product developed by VMware for Macintosh computers with Intel processors. Fusion allows Intel-based Macs to run x86 and x86-64 "guest" operating systems, such as Microsoft Windows, Linux, NetWare and Solaris as virtual machines simultaneously with Mac OS X as the "host" operating system using a combination of virtualization, emulation and dynamic recompilation. While similar in most respects to VMware Workstation.

Skype is a software application that allows users to make telephone calls over the Internet. Calls to other users of the service, and in some countries to free-of-charge numbers are free, while calls to other landlines and mobile phones can be made for a fee. Additional features include instant messaging, file transfer and video conferencing.

Keypass X  is a free open source password manager, which helps you to manage your passwords in a secure way. You can put all your passwords in one database, which is locked with one master key or a key file. So you only have to remember one single master password or select the key file to unlock the whole database. The databases are encrypted using the best and most secure encryption algorithms currently known (AES and Twofish)

Microsoft Office 2008, Word PowerPoint, Excel and Entourage. More or less the same bugi thing like on Windows.

LaTeX is based on the idea that authors should be able to focus on the content of what they are writing without being distracted by its visual presentation. In preparing a LaTeX document, the author specifies the logical structure using familiar concepts such as chapter, section, table, figure, etc., and lets the LaTeX system worry about the presentation of these structures. It therefore encourages the separation of layout from content while still allowing manual typesetting adjustments where needed.

Cyberduck is an open source FTP, SFTP, WebDAV, Mosso Cloud Files and Amazon S3 browser for the Mac. It features an easy to use interface with quickly accessible bookmarks. The outline view of the browser allows to browse large folder structures efficiently and you can quickly preview files with Quick Look. To edit files, a seamless integration with several external editors makes it easy to change content quickly. Both Amazon CloudFront and Cloud Files from Rackspace can be easily configured to distribute your content in the cloud. Many OS X core system technologies such as Spotlight, Bonjour and the Keychain are supported and a large number of translations makes you feel at home.

Freeciv is a multiplayer, turn-based strategy game for workstations and personal computers inspired by the commercial proprietary Sid Meier’s Civilization series. The game’s default settings are closest to Civilization II, both in gameplay and graphics (including the units and the isometric grid).

These are the Programs that make my 24 iMac to a Powerful blog machine. There is only one thing I am missing. On windows there is a free Software called Live Writer. Live writer make blogging very easy but I don’t find a Sirius alternative for Mac. If you know something pls don’t hesitated post a commend.

Regards Konrad

Kick the crashed AD controller out

May 24th, 2009

Sometimes bad things happen, sometimes ugly things happen. One of the very bad things that can happen is when  an active domain controller crashes and there is no backup available. The case becomes really ugly if nobody cares about the crashed controller for about 60 days (forest that was created on a domain controller running Windows Server 2003 and earlier) or 180 days (forest that was created on a domain controller running Windows Server 2003 sp1 and later). On this depends the default tombstone lifetime of directory objects. Later we will investigate on the tombstone.

A good place to fix this whole bunch of problems is by verifying the backup strategy and ensuring that all system-states are saved on all domain controllers. The second step is verifying that DNS are fine and syncing the proper way. Now we are ready to move the FSMO roles. For everyone that is not familiar with the five FSMO Friends, here is a small overview from Wikipedia

Flexible Single Master of Operation (FSMO, F is sometimes floating ; pronounced Fiz-mo), or just single master operation or operations master, is a feature of Microsoft’s Active Directory (AD). As of 2005, the term FSMO has been deprecated in favor of operations masters.

FSMOs are specialized domain controller (DC) tasks, used where standard data transfer and update methods are inadequate. AD normally relies on multiple peer DCs, each with a copy of the AD database, being synchronized by multi-master replication. The tasks which are not suited to multi-master replication, and are viable only with a single-master database, are the FSMOs.

Domain-wide FSMO Roles:

Every domain in an Active Directory forest must contain one of each of the following FSMO roles:
The Relative ID Master allocates security RIDs to DCs to assign to new AD security principals (users, groups or computer objects). It also manages objects moving between domains.
The Infrastructure Master maintains security identifiers, GUIDs, and DNS for objects referenced across domains. Most commonly it updates user and group links. This is another domain-specific role and its purpose is to ensure that cross-domain object references are correctly handled. For example, if you add a user from one domain to a security group from a different domain, the Infrastructure Master makes sure this is done properly. As you can guess however, if your Active Directory deployment has only a single domain, then the Infrastructure Master role does no work at all, and even in a multi-domain environment it is rarely used except when complex user administration tasks are performed, so the machine holding this role doesn’t need to have much horsepower at all.
The PDC Emulator operations master role processes all password changes in the domain. Failed authentication attempts due to a bad password at other domain controllers are forwarded to the PDC Emulator before rejection. This ensures that a user can immediately login following a password change from any domain controller, without having to wait several minutes for the change to be replicated. The PDC Emulator Operations Master role must be carefully sited in a location to best handle all password reset and failed-authentication forwarding traffic for the domain.

Forest-wide FSMO Roles:

Regardless of the number of domains in an Active Directory forest, the following FSMO roles exist only once:
The Schema Master maintains all modifications to the schema of the forest. The schema determines the types of objects permitted in the forest and the attributes of those objects.
The Domain Naming Master tracks the names of all domains in the forest and is required to add new domains to the forest or delete existing domains from the forest. It is also responsible for group membership.

Normally it’s very easy to move these roles by right clicking the forest level and choose Move …  in the Active Directory Schema snap-in, Active Directory Domains and Trusts snap-in and Active Directory Users and Computers snap-in. But it will fail to 99% with an obscure error. The reason for the error is one domain controller in the replica ring is missing and marked as Tombstone. Let’s get to the bigger guns and start “ntdsutil.exe”, open a command prompt and enter “ntdsutil.exe”. If the shell is bugging you that the exe is missing, you need to install the server support tools. They are located on the Windows CD in the support folder. Other ways you can download it from Microsoft using Google ☺.

!! Remember at this point you can do very large harm to the directory so please be sure that you have properly working backups!!

After “ntdsutil.exe” has successful started, type “roles” and press enter. Type “connections” and press enter. Now Type “connect to server xyz.planetgeek.ch”, where xyz.planetgeek.ch is the name of the server where you want to transfer the roles to. A message will appear:

“Binding to xyz.planetgeek.ch …
Connected to servername using credentials of locally logged on user.”

Tipe “quit” to leave the selection menu. Now appears: “fsmo maintenance:” now enter:

“Seize schema master” if you want move the schema master.
“Seize domain naming master” if you want move the naming master.
“Seize PDC” if you want move the PDC.
“Seize RID master” if you want move the Relative ID master.
“Seize infrastructure master” if you want move the infrastructure master.

Next thing to do is kicking the metadata out of the directory. To do this I know two possible ways. The first is use a VB script written by Clay Perrine from Microsoft. The second way is to use ntdsutil.exe. I prefer the VB script. It works on the most common Windows Operating systems (2k, XP, 03, Vista and 08). The script is below ore you can obtain it directly from Microsoft (http://go.microsoft.com/fwlink/?LinkID=123599).

REM    ==========================================================
REM                GUI Metadata Cleanup Utility
REM             Written By Clay Perrine
REM                          Version 2.5
REM    ==========================================================
REM     This tool is furnished "AS IS". NO warranty is expressed or Implied.

on error resume next
dim objRoot,oDC,sPath,outval,oDCSelect,objConfiguration,objContainer,errval,ODCPath,ckdcPath,myObj,comparename

rem =======This gets the name of the computer that the script is run on ======

Set sh = CreateObject("WScript.Shell")
key= "HKEY_LOCAL_MACHINE"
computerName = sh.RegRead(key & "\SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\ComputerName")

rem === Get the default naming context of the domain====

set objRoot=GetObject("LDAP://RootDSE")
sPath = "LDAP://OU=Domain Controllers," & objRoot.Get("defaultNamingContext")

rem === Get the list of domain controllers====

Set objConfiguration = GetObject(sPath)
For Each objContainer in objConfiguration
    outval = outval & vbtab &  objContainer.Name & VBCRLF
Next
outval = Replace(outval, "CN=", "")

rem ==Retrieve the name of the broken DC from the user and verify it’s not this DC.===

oDCSelect= InputBox (outval," Enter the computer name to be removed","")
comparename = UCase(oDCSelect)

if comparename = computerName then
    msgbox "The Domain Controller you entered is the machine that is running this script." & vbcrlf & _
        "You cannot clean up the metadata for the machine that is running the script!",,"Metadata Cleanup Utility Error."
    wscript.quit
End If

sPath = "LDAP://OU=Domain Controllers," & objRoot.Get("defaultNamingContext")
Set objConfiguration = GetObject(sPath)

For Each objContainer in objConfiguration
    Err.Clear
    ckdcPath = "LDAP://" & "CN=" & oDCSelect & ",OU=Domain Controllers," & objRoot.Get("defaultNamingContext")
    set myObj=GetObject(ckdcPath)
    If err.number <>0 Then
        errval= 1
    End If
Next

If errval = 1 then
    msgbox "The Domain Controller you entered was not found in the Active Directory",,"Metadata Cleanup Utility Error."
    wscript.quit
End If

abort = msgbox ("You are about to remove all metadata for the server " & oDCSelect & "! Are you sure?",4404,"WARNING!!")
if abort <> 6 then
    msgbox "Metadata Cleanup Aborted.",,"Metadata Cleanup Utility Error."
    wscript.quit
end if

oDCSelect = "CN=" & oDCSelect
ODCPath ="LDAP://" & oDCselect & ",OU=Domain Controllers," & objRoot.Get("defaultNamingContext")
sSitelist = "LDAP://CN=Sites,CN=Configuration," & objRoot.Get("defaultNamingContext")
Set objConfiguration = GetObject(sSitelist)
For Each objContainer in objConfiguration
    Err.Clear
    sitePath = "LDAP://" & oDCSelect & ",CN=Servers," &  objContainer.Name & ",CN=Sites,CN=Configuration," & _
        objRoot.Get("defaultNamingContext")
    set myObj=GetObject(sitePath)
    If err.number = 0 Then
        siteval = sitePath
    End If   
Next

sFRSSysvolList = "LDAP://CN=Domain System Volume (SYSVOL share),CN=File Replication Service,CN=System," & _
    objRoot.Get("defaultNamingContext")
Set objConfiguration = GetObject(sFRSSysvolList)

For Each objContainer in objConfiguration
    Err.Clear
    SYSVOLPath = "LDAP://" & oDCSelect & ",CN=Domain System Volume (SYSVOL share),CN=File Replication Service,CN=System," & _
        objRoot.Get("defaultNamingContext")
    set myObj=GetObject(SYSVOLPath)
    If err.number = 0 Then
        SYSVOLval = SYSVOLPath
    End If
Next

SiteList = Replace(sSitelist, "LDAP://", "")
VarSitelist = "LDAP://CN=Sites,CN=Configuration," & objRoot.Get("defaultNamingContext")
Set SiteConfiguration = GetObject(VarSitelist)

For Each SiteContainer in SiteConfiguration
    Sitevar = SiteContainer.Name
    VarPath ="LDAP://OU=Domain Controllers," & objRoot.Get("defaultNamingContext")
    Set DCConfiguration = GetObject(VarPath)
    For Each DomContainer in DCConfiguration
        DCVar = DomContainer.Name
        strFromServer = ""
        NTDSPATH =  DCVar & ",CN=Servers," & SiteVar & "," & SiteList
        GuidPath = "LDAP://CN=NTDS Settings,"& NTDSPATH
        Set objCheck = GetObject(NTDSPATH)
        For Each CheckContainer in objCheck
rem ====check for valid site paths =======================
            ldapntdspath = "LDAP://" & NTDSPATH
            Err.Clear
            set exists=GetObject(ldapntdspath)
            If err.number = 0 Then
                Set oGuidGet = GetObject(GuidPath)
                For Each objContainer in oGuidGet
                    oGuid = objContainer.Name
                    oGuidPath = "LDAP://" & oGuid & ",CN=NTDS Settings," & NTDSPATH 
                    Set objSitelink = GetObject(oGuidPath)
                    objSiteLink.GetInfo
                    strFromServer = objSiteLink.Get("fromServer")
                    ispresent = Instr(1,strFromServer,oDCSelect,1)

                    if ispresent <> 0 then
                        Set objReplLinkVal = GetObject(oGuidPath)
                        objReplLinkVal.DeleteObject(0)
                    end if
                next

                sitedelval = "CN=" & comparename & ",CN=Servers," & SiteVar & "," & SiteList
                if sitedelval = ntdspath then
                    Set objguidpath = GetObject(guidpath)
                    objguidpath.DeleteObject(0)
                    Set objntdspath = GetObject(ldapntdspath)
                    objntdspath.DeleteObject(0)
                end if
            End If
        next
    next
next
Set AccountObject = GetObject(ckdcPath)
temp=Accountobject.Get ("userAccountControl")
AccountObject.Put "userAccountControl", "4096"
AccountObject.SetInfo
Set objFRSSysvol = GetObject(SYSVOLval)
objFRSSysvol.DeleteObject(0)
Set objComputer = GetObject(ckdcPath)
objComputer.DeleteObject(0)
Set objConfig = GetObject(siteval)
objConfig.DeleteObject(0)
oDCSelect = Replace(oDCSelect, "CN=", "")
msgval = "Metadata Cleanup Completed for " & oDCSelect
msgbox  msgval,,"Notice."
wscript.quit

An easy to use description of the ntdsutil.exe way you find under http://technet.microsoft.com/en-us/library/cc736378.aspx

Next thing that will drive you crazy are the millions of ntfrs errors in the Eventlog. Ntfrs is the “New Technology File replication Service” from Windows. It is used for the replication of the sysvol/ netlogon. Remember Since Windows 2003 R2 nftrs is replaced trough DFS. First of all we are saving the eventlog to a file then clean it and boot every Domain Controller in the domain and wait a few minutes. On my experience this will fix half of the problems, like swiss admins tend to say “ein boot tut immer gut” ; -).