How to move a DHCP database from Windows Server 2003 to Windows Server 2008


I needed to duplicate my primary DHCP scopes to a secondary DHCP server so I could perform maintenance on my domain controller. Both of my servers are 2003 R2. I came across the following procedure on the Microsoft support site for moving from Server 2003 to 2008 and decided to give it a try.

http://support.microsoft.com/kb/962355

The procedure worked and was quick and easy to implement. I received an error message at the end however upon stopping and restarting the DHCP service I could see all my scopes and reservations on the secondary DHCP server.

Advertisements
Tagged with: , , , , ,
Posted in Windows

Creating WebMarshal Database on SQL Server 2012, Error “Could not find stored procedure’sp_dboption'”


Version 6.9.6.7487  of WebMarshal is not officially supported on Microsoft’s SQL Server 2012 and I wanted to find out if it would work. I installed WebMarshal on to a Windows Server 2012 box for the application server with a backend SQL Server 2012. Following the install procedure for WebMarshal was pretty simple and everything worked great up until the option to create an SQL database. I had previously created my SQL authentication login and password created and I had allowed for SQLServer, SQLBrowser and SQLServer (Port 1433) Inbound on my database server. However I received the error “Could not find stored procedure’sp_dboption'”. The database and log files had been mostly created but the process crashed out.

I proceded to do some research on changes to SQL2012 and came across a few others that had similar problems with other applications. It turns out (suprisingly) that Microsoft had removed the ‘sp_dboption’ from SQL 2012. I continued researching this across Microsoft sites and others until I came across a site by Paul Nezhencev. On this site Paul stated that you can recreate the ‘sp_dboption’ from 2008R2 on the SQL 2012 server and provided an SQL script to do so. The script was great except for a minor error, due to Microsoft having removed (what a surprise) the built-in function ‘databaseproperty’ in favour of ‘databasepropertyex’.

After making the minor modification to the script and running it in the SQL Server Management Studio, I received the message Command(s) completed successfully.

I then went back to my WebMarshal install which I had left at the point of error and told it to create the database again but replace existing as I had no data in the current one. The install proceeded smoothly after this and within minutes I had a fully functioning WebMarshal application.

I have included below the code from Paul with the modifications in case anyone else as the same problem.

USE [master]

GO

/****** Object:  StoredProcedure [sys].[sp_dboption]    Script Date: 06/06/2012 21:16:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[sp_dboption] -- 1999/08/09 18:25

@dbname sysname = NULL,   -- database name to change

@optname varchar(35) = NULL, -- option name to turn on/off

@optvalue varchar(10) = NULL -- true or false

as

set nocount    on

declare @dbid int   -- dbid of the database

declare @catvalue int  -- number of category option

declare @optcount int  -- number of options like @optname

declare @allstatopts int -- bit map off all options stored in sysdatqabases.status

-- that can be set by sp_dboption.

declare @alloptopts int  -- bit map off all options stored in sysdatqabases.status

-- that can be set by sp_dboption.

declare @allcatopts int  -- bit map off all options stored in sysdatqabases.category

-- that can be set by sp_dboption.

declare @exec_stmt nvarchar(max)

declare @fulloptname varchar(35)

declare @alt_optname varchar(50)

declare @alt_optvalue varchar(30)

declare @optnameIn varchar(35)

select @optnameIn = @optname

,@optname = LOWER (@optname collate Latin1_General_CI_AS)

-- If no @dbname given, just list the possible dboptions.

--  Only certain status bits may be set or cleared by sp_dboption.

-- Get bitmap of all options that can be set by sp_dboption.

select @allstatopts=number from master.dbo.spt_values where type = 'D'

and name = 'ALL SETTABLE OPTIONS'

select @allcatopts=number from master.dbo.spt_values where type = 'DC'

and name = 'ALL SETTABLE OPTIONS'

select @alloptopts=number from master.dbo.spt_values where type = 'D2'

and name = 'ALL SETTABLE OPTIONS'

if @dbname is null

begin

select 'Settable database options:' = name

from master.dbo.spt_values

where (type = 'D'

and number & @allstatopts <> 0

and number not in (0,@allstatopts)) -- Eliminate non-option entries

or (type = 'DC'

and number & @allcatopts <> 0

and number not in (0,@allcatopts))

or (type = 'D2'

and number & @alloptopts <> 0

and number not in (0,@alloptopts))

order by name

return (0)

end

--  Verify the database name and get info

select @dbid = dbid

from master.dbo.sysdatabases

where name = @dbname

--  If @dbname not found, say so and list the databases.

if @dbid is null

begin

raiserror(15010,-1,-1,@dbname)

print ' '

select 'Available databases:' = name

from master.dbo.sysdatabases

return (1)

end

-- If no option was supplied, display current settings.

if @optname is null

begin

select 'The following options are set:' = v.name

from master.dbo.spt_values v, master.dbo.sysdatabases d

where d.name=@dbname

and ((number & @allstatopts <> 0

and number not in (-1,@allstatopts)

and v.type = 'D'

and (v.number & d.status)=v.number)

or (number & @allcatopts <> 0

and number not in (-1,@allcatopts)

and v.type = 'DC'

and d.category & v.number <> 0)

or (number & @alloptopts <> 0

and number not in (-1,@alloptopts)

and v.type = 'D2'

and d.status2 & v.number <> 0))

return(0)

end

if @optvalue is not null and lower(@optvalue) not in ('true', 'false', 'on', 'off')

begin

raiserror(15241,-1,-1)

return (1)

end

--  Use @optname and try to find the right option.

--  If there isn't just one, print appropriate diagnostics and return.

select @optcount = count(*) ,@fulloptname = min(name)

from master.dbo.spt_values

where lower(name collate Latin1_General_CI_AS) like '%' + @optname + '%'

and ((type = 'D'

and number & @allstatopts <> 0

and number not in (-1,@allstatopts))

or (type = 'DC'

and number & @allcatopts <> 0

and number not in (-1,@allcatopts))

or (type = 'D2'

and number & @alloptopts <> 0

and number not in (-1,@alloptopts)))

--  If no option, show the user what the options are.

if @optcount = 0

begin

raiserror(15011,-1,-1,@optnameIn)

print ' '

select 'Settable database options:' = name

from master.dbo.spt_values

where (type = 'D'

and number & @allstatopts <> 0

and number not in (-1,@allstatopts)) -- Eliminate non-option entries

or (type = 'DC'

and number & @allcatopts <> 0

and number not in (-1,@allcatopts))

or (type = 'D2'

and number & @alloptopts <> 0

and number not in (-1,@alloptopts))

order by name

return (1)

end

--  If more than one option like @optname, show the duplicates and return.

if @optcount > 1

begin

raiserror(15242,-1,-1,@optnameIn)

print ' '

select duplicate_options = name

from master.dbo.spt_values

where lower(name collate Latin1_General_CI_AS) like '%' + @optname + '%'

and ((type = 'D'

and number & @allstatopts <> 0

and number not in (-1,@allstatopts))

or (type = 'DC'

and number & @allcatopts <> 0

and number not in (-1,@allcatopts))

or (type = 'D2'

and number & @alloptopts <> 0

and number not in (-1,@alloptopts))

)

return (1)

end

--  Just want to see current setting of specified option.

if @optvalue is null

begin

select OptionName = v.name,

CurrentSetting = (case

when ( ((v.number & d.status) = v.number

and v.type = 'D')

or (d.category & v.number <> 0

and v.type = 'DC')

or (d.status2 & v.number <> 0

and v.type = 'D2')

)

then 'ON'

when not

( ((v.number & d.status) = v.number

and v.type = 'D')

or (d.category & v.number <> 0

and v.type = 'DC')

or (d.status2 & v.number <> 0

and v.type = 'D2')

)

then 'OFF'

end)

from master.dbo.spt_values v, master.dbo.sysdatabases d

where d.name=@dbname

and ((v.number & @allstatopts <> 0

and v.number not in (-1,@allstatopts) -- Eliminate non-option entries

and v.type = 'D')

or (v.number & @allcatopts <> 0

and v.number not in (-1,@allcatopts) -- Eliminate non-option entries

and v.type = 'DC')

or (v.number & @alloptopts <> 0

and v.number not in (-1,@alloptopts) -- Eliminate non-option entries

and v.type = 'D2')

)

and lower(v.name) = lower(@fulloptname)

return (0)

end

select @catvalue = 0

select @catvalue = number

from master.dbo.spt_values

where lower(name) = lower(@fulloptname)

and type = 'DC'

-- if setting replication option, call sp_replicationdboption directly

if (@catvalue <> 0)

begin

select @alt_optvalue = (case lower(@optvalue)

when 'true' then 'true'

when 'on' then 'true'

else 'false'

end)

select @alt_optname = (case @catvalue

when 1 then 'publish'

when 2 then 'subscribe'

when 4 then 'merge publish'

else quotename(@fulloptname, '''')

end)

select @exec_stmt = quotename(@dbname, '[')   + '.dbo.sp_replicationdboption'

EXEC @exec_stmt @dbname, @alt_optname, @alt_optvalue

return (0)

end

-- call Alter Database to set options

-- set option value in alter database

select @alt_optvalue = (case lower(@optvalue)

when 'true' then 'ON'

when 'on' then 'ON'

else 'OFF'

end)

-- set option name in alter database

select @fulloptname = lower(@fulloptname)

select @alt_optname = (case @fulloptname

when 'auto create statistics' then 'AUTO_CREATE_STATISTICS'

when 'auto update statistics' then 'AUTO_UPDATE_STATISTICS'

when 'autoclose' then 'AUTO_CLOSE'

when 'autoshrink' then 'AUTO_SHRINK'

when 'ansi padding' then 'ANSI_PADDING'

when 'arithabort' then 'ARITHABORT'

when 'numeric roundabort' then 'NUMERIC_ROUNDABORT'

when 'ansi null default' then 'ANSI_NULL_DEFAULT'

when 'ansi nulls' then 'ANSI_NULLS'

when 'ansi warnings' then 'ANSI_WARNINGS'

when 'concat null yields null' then 'CONCAT_NULL_YIELDS_NULL'

when 'cursor close on commit' then 'CURSOR_CLOSE_ON_COMMIT'

when 'torn page detection' then 'TORN_PAGE_DETECTION'

when 'quoted identifier' then 'QUOTED_IDENTIFIER'

when 'recursive triggers' then 'RECURSIVE_TRIGGERS'

when 'default to local cursor' then 'CURSOR_DEFAULT'

when 'offline' then (case @alt_optvalue when 'ON' then 'OFFLINE' else 'ONLINE' end)

when 'read only' then (case @alt_optvalue when 'ON' then 'READ_ONLY' else 'READ_WRITE' end)

when 'dbo use only' then (case @alt_optvalue when 'ON' then 'RESTRICTED_USER' else 'MULTI_USER' end)

when 'single user' then (case @alt_optvalue when 'ON' then 'SINGLE_USER' else 'MULTI_USER' end)

when 'select into/bulkcopy' then 'RECOVERY'

when 'trunc. log on chkpt.' then 'RECOVERY'

when 'db chaining' then 'DB_CHAINING'

else @alt_optname

end)

if @fulloptname = 'dbo use only'

begin

if @alt_optvalue = 'ON'

begin

if databasepropertyex(@dbname, 'IsSingleUser') = 1

begin

raiserror(5066,-1,-1);

return (1)

end

end

else

begin

if databasepropertyex(@dbname, 'IsDBOOnly') = 0

return (0)

end

end

if @fulloptname = 'single user'

begin

if @alt_optvalue = 'ON'

begin

if databasepropertyex(@dbname, 'ISDBOOnly') = 1

begin

raiserror(5066,-1,-1);

return (1)

end

end

else

begin

if databasepropertyex(@dbname, 'IsSingleUser') = 0

return (0)

end

end

select @alt_optvalue = (case @fulloptname

when 'default to local cursor' then (case @alt_optvalue when 'ON' then 'LOCAL' else 'GLOBAL' end)

when 'offline' then ''

when 'read only' then ''

when 'dbo use only' then ''

when 'single user' then ''

else  @alt_optvalue

end)

if lower(@fulloptname) = 'select into/bulkcopy'

begin

if @alt_optvalue = 'ON'

begin

if databasepropertyex(@dbname, 'IsTrunclog') = 1

select @alt_optvalue = 'RECMODEL_70BACKCOMP'

else

select @alt_optvalue = 'BULK_LOGGED'

end

else

begin

if databasepropertyex(@dbname, 'IsTrunclog') = 1

select @alt_optvalue = 'SIMPLE'

else

select @alt_optvalue = 'FULL'

end

end

if lower(@fulloptname) = 'trunc. log on chkpt.'

begin

if @alt_optvalue = 'ON'

begin

if databasepropertyex(@dbname, 'IsBulkCopy') = 1

select @alt_optvalue = 'RECMODEL_70BACKCOMP'

else

select @alt_optvalue = 'SIMPLE'

end

else

begin

if databasepropertyex(@dbname, 'IsBulkCopy') = 1

select @alt_optvalue = 'BULK_LOGGED'

else

select @alt_optvalue = 'FULL'

end

end

-- construct the ALTER DATABASE command string

select @exec_stmt = 'ALTER DATABASE ' + quotename(@dbname) + ' SET ' + @alt_optname + ' ' + @alt_optvalue + ' WITH NO_WAIT'

EXEC (@exec_stmt)

if @@error <> 0

begin

raiserror(15627,-1,-1)

return (1)

end

return (0) -- sp_dboption&
Tagged with: , ,
Posted in SQL

SQL Server Does Not Exist or Access Denied


I was unable to see my SQL 2012 Server from my Applications Server and was getting the error “SQL server does not exist or access denied.

I checked in the SQL Server Configuration Manager that TCP/IP was enabled.

I also checked in SQL Management Studio that the SQL Server Agent was running and in the properties of the server, within Connections, that the “Allow remote connections to this server” setting was enabled.

The cause of the issue was the Windows Firewall on the SQL Server. I added the following to the Inbound Rules of the Firewall.
Firewall - SQL Server Ports
The program file paths I used are below.

SQL Browser: %ProgramFiles% (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe
SQL Server: %ProgramFiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe

I was now able to select my SQL server from my Application Server.

Tagged with: , , , ,
Posted in SQL

Setting the Time on Windows Server Using NTP Server.


To configure an external time source on the PDC emulator, execute the following command (pool.ntp.org, used here only as an example):

w32tm /config /manualpeerlist:pool.ntp.org /syncfromflags:MANUAL /update

Always restart the windows time service (w32time) when making any changes:

net stop w32time
net start w32time

To force synchronization with the external time source:

w32tm /resync

To verify the time source you just set:

w32tm /query /source

On Domain Members

To synch  a domain member server or workstation to the domain hierarchy, execute:

w32tm /config /update /syncfromflags:DOMHIER

restart W32Time Services:

net stop w32time
net start w32time

To verify time synchronization  is functioning:

w32tm /query /status
Tagged with: , , , ,
Posted in Windows

Guide to Setting Up SQL Server 2008 R2


Not being a fulltime DBA I came across a great guide to setting up SQL Server 2008 R2. I used this to help setup my SQL Server 2012.

http://www.idevelopment.info/data/SQLServer/DBA_tips/Installation/INSTALL_4.shtml

Tagged with: , , , ,
Posted in Databases, SQL, Windows

Windows Server 2012 Storage Spaces: Virtual Disks Detach After Reboot


I built myself a NAS\SAN in my test lab using Windows Server 2012 Storage Spaces, a WD Black 1.5TB disk and a WD Green 2TB disk, Initially this worked well. With the success I decided to upgraded the physical disks to WD Red 3TB disks the process of adding the WD Red disks appeared to go smoothly and I was able to see the new storage from my virtual hosts.

A couple of days later I was preping to migrate the data from my WD Black to the new WD Reds before doing so I rebooted the server. Upon reboot within Server Manger – File and Storage Services – Storage Pools I noticed a yellow warning triangle next to my virtual disks.

My virtual disks had become detached and would not reattach as I was told the disk and name were already in use in the system.

After spending some time attempting to re-add the disks I tried some of the Powershell commands to diagnose the problem.

Get-VirtualDisk

Upon running the Get-VirtualDisk command I noticed that the ISManualAttach values were set to TRUE which did not sound right.

Upon searching for the Set-VirtualDisk parameters (http://technet.microsoft.com/en-us/library/hh848712.aspx) I was able to change this setting by using the following command in PowerShell.

Set-VirtualDisk -FriendlyName yourdiskname -IsManualAttach $False

I checked the settings had changed by running another Get-VirtualDisk and they had.

Upon rebooting and checking in Storage Pools my disks were attached and all my volumes and iSCSI disks were restored.

Posted in Windows

Install Firefox 4 on Fedora 14


Mozilla Firefox 4 has been released and this guide will show you how to install Firefox 4 on Fedora 14 using YUM. It’s important to take backups of all Firefox profiles before you install as this is a major version upgrade and can behave unpredictably.

What’s new in Firefox 4

  • New HTML5 parser
  • Support for more HTML5 form controls
  • Native support for the HD HTML5 WebM video format
  • Crash protection – when there is a crash in the Adobe Flash, Apple Quicktime or Microsoft Silverlight plugins
  • Web developers can animate content using CSS Transitions
  • New Addons Manager and extension management API
  • Full WebGL support – is included but disabled by default at this time

Read full details from Firefox 4 release notes.

Install Mozilla Firefox 4 on Fedora 14

1. Backup your current Firefox 3 profiles

tar -cvzf $HOME/mozilla-firefox-profiles-backup.tar.gz $HOME/.mozilla/firefox/

2. Change to root user

su -
## OR ##
sudo -i

3. Install Remi repository

## Fedora 14 ##
rpm -Uvh http://rpms.famillecollet.com/remi-release-14.rpm

4. Check Available Firefox 4 versions

yum --enablerepo=remi list firefox

Output:

...
Loaded plugins: presto, refresh-packagekit
remi                                     | 2.9 kB    00:00
remi/primary_db                          | 190 kB    00:01
updates/metalink                         | 3.2 kB    00:00
Installed Packages
firefox.x86_64        3.6.17-1.fc14           @updates
Available Packages
firefox.x86_64        4.0.1-1.fc14.remi       remi

5. Install Mozilla Firefox 4

yum --enablerepo=remi install firefox

Output:

...
Loaded plugins: presto, refresh-packagekit
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package firefox.x86_64 0:4.0.1-1.fc14.remi set to be updated
--> Processing Dependency: gecko-libs(x86-64) = 2.0.1-1 for package: firefox-4.0.1-1.fc14.remi.x86_64
--> Processing Dependency: libmozalloc.so()(64bit) for package: firefox-4.0.1-1.fc14.remi.x86_64
--> Running transaction check
---> Package xulrunner2.x86_64 0:2.0.1-1.fc14.remi set to be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================
Package       Arch       Version              Repository    Size
====================================================================
Updating:
firefox       x86_64     4.0.1-1.fc14.remi    remi          17 M
Installing for dependencies:
xulrunner2    x86_64     2.0.1-1.fc14.remi    remi          10 M

Transaction Summary
=====================================================================
Install       1 Package(s)
Upgrade       1 Package(s)

Total download size: 28 M
Is this ok [y/N]: y
Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 28 M
(1/2): firefox-4.0.1-1.fc14.remi.x86_64.rpm                      |  17 MB     00:18
(2/2): xulrunner2-2.0.1-1.fc14.remi.x86_64.rpm                   |  10 MB     00:11
----------------------------------------------------------------------------------------
Total                                                   933 kB/s |  28 MB     00:30
warning: rpmts_HdrFromFdno: Header V3 DSA/SHA1 Signature, key ID 00f97f56: NOKEY
remi/gpgkey                                                      | 2.6 kB     00:00 ...
Importing GPG key 0x00F97F56:
Userid : Remi Collet <RPMS@FamilleCollet.com>
Package: remi-release-14-6.fc14.remi.noarch (installed)
From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-remi
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
** Found 2 pre-existing rpmdb problem(s), 'yum check' output follows:
alchemist-1.0.37-8.fc12.x86_64 has missing requires of python(abi) = ('0', '2.6', None)
alchemist-1.0.37-8.fc12.x86_64 has missing requires of python-abi = ('0', '2.6', None)
Installing     : xulrunner2-2.0.1-1.fc14.remi.x86_64                              1/3
Updating       : firefox-4.0.1-1.fc14.remi.x86_64                               2/3

WARNING : This firefox 4.0.1  RPM is not an official
Fedora build and it overrides the official one. Don't file bugs on Fedora Project.

Use dedicated forums http://forums.famillecollet.com/

Cleanup:
firefox-3.6.17-1.fc14.x86_64                                     3/3

Dependency Installed:
xulrunner2.x86_64 0:2.0.1-1.fc14.remi

Updated:
firefox.x86_64 0:4.0.1-1.fc14.remi

Complete!

6. Start Firefox 4

Then start Firefox with FF4 profile using command:

firefox

Use Firefox 4 launcher

Note: If you use a separate profile for Firefox 4 then modify the launcher too.

Firefox 4 Screenshots

Firefox 4 running on Fedora 14

Posted in Linux