22
Dec
13

Installing Microsoft Office SharePoint Server 2007 on SQL Server 2012

Well, I got myself into a pickle.  I needed to create a SharePoint 2007 server instance within my domain so I could test some 2007/2010/2013 interactions.  It’s been a while since I installed 2007, but I thought it would be like riding a bicycle…you never really forget.  Turns out it was more like figuring out how to ride a unicycle.

I was planning on using best practices for all of the accounts necessary to perform a proper SharePoint installation.  And, for the most part, that went well.  The one thing I hadn’t planned on, however, was installing on SQL 2012.

I had recently upgraded my SQL VM to 2012 and that went without a hitch.  It was working fine, so out of sight, out of mind.  I started on my merry way.  I didn’t even consider whether MOSS 2007 would install on SQL 2012.  Hey, it’s SQL, right?  Well, not so fast, bucko!

The slipstreamed installation of 2007 w/SP2 actually went pretty well.  It was only after the installation and applying SP3 and some CU’s that I began to experience the incompatibilities, specifically in the SharePoint Products and Technologies Configuration Wizard.

sp_dboption is Deprecated and No Longer Exists in the Master Database

This was the first issue I had to overcome.  The configuration wizard calls sp_dboption but it no longer exists.  This was fairly easy to fix.  Simply get a copy of sp_dboption and add it back.  I present Exhibit A (sp_dboption) for your perusal.  (Skip to next topic)

USE [master]
GO
/****** Object: StoredProcedure [sys].[sp_dboption] Script Date: 12/08/2013 21:22:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [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 databaseproperty(@dbname, '
IsSingleUser') = 1
begin
raiserror(5066,-1,-1);
return (1)
end
end
else
begin
if databaseproperty(@dbname, '
IsDBOOnly') = 0
return (0)
end
end

if @fulloptname = '
single user'
begin
if @alt_optvalue = '
ON'
begin
if databaseproperty(@dbname, '
ISDBOOnly') = 1
begin
raiserror(5066,-1,-1);
return (1)
end
end
else
begin
if databaseproperty(@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 databaseproperty(@dbname, '
IsTrunclog') = 1
select @alt_optvalue = '
RECMODEL_70BACKCOMP'
else
select @alt_optvalue = '
BULK_LOGGED'
end
else
begin
if databaseproperty(@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 databaseproperty(@dbname, '
IsBulkCopy') = 1
select @alt_optvalue = '
RECMODEL_70BACKCOMP'
else
select @alt_optvalue = '
SIMPLE'
end
else
begin
if databaseproperty(@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
 

dbo.trace_xe_action_map Moved from dbo to sys Schema

This next issue took me quite  a while longer to figure out. 

Kudos to Dan Guzman, SQL Server MVP, (http://www.dbdelta.com) for helping me figure this one out.  Without dragging this out too much, I was getting this error when running the configuration wizard:  Invalid object name ‘dbo.trace_xe_action_map’.

image

This pointed back to system table, dbo.trace_xe_action_map.  From there I found out it, as well as trace_xe_event_map, have been moved to the sys schema and here’s some of the original discussion by Aaron Bertrand and the SQL team.  Well, in case you wondered, it did make it into the final release!

I thought creating a synonym would fix this quickly and easily.  If I had only known what I know now about synonyms!  You have to create them in the context of the database in which they will be called.  What does this mean?  You need to create the synonym for each table the SharePoint Products and Technologies Configuration Wizard will upgrade, as dbo.trace_xe_action_map is referenced within each database context during the process.

I started troubleshooting this first by using SQL Profiler, as I wanted to see what the wizard was doing.  This is what SQL Profiler trapped during the wizard execution.

declare @p3 int
set @p3=NULL
exec sp_executesql N’SELECT @RowCount=COUNT(1) FROM [dbo].[trace_xe_action_map] WITH (NOLOCK)’,N’@RowCount int output’,@RowCount=@p3 output
select @p3

The first database where I fixed the issue was on the AdminContent database. 

use [SharePoint_AdminContent_a974ce72-f4c4-43ff-a3bc-fe08e4e0a553]

go

CREATE SYNONYM [dbo].[trace_xe_action_map] FOR [sys].[trace_xe_action_map];

CREATE SYNONYM [dbo].[trace_xe_event_map] FOR [sys].[trace_xe_event_map];

go

That got me up to task 8 of 9 but then I started getting other errors that were not as well identified.  I was seeing issues such as this one:

The B2B upgrader timer job failed.

clip_image001

Reading some other discussion threads, I invoked psconfig as follows:

psconfig -cmd upgrade -inplace b2b -force -wait

But I continued to get errors.  It was only after looking at the Upgrade text file in the logs folder (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS) that I began to see the source of the additional errors.

image

This is where I noticed the Invalid object name error on dbo.trace_xe_action_map that was occurring upon every database context switch. 

[SPManager] [ERROR] [12/22/2013 8:33:39 PM]: Upgrade [SPContentDatabase Name=WSS_Content_RXSP2007 Parent=SPDatabaseServiceInstance] failed.
[SPManager] [ERROR] [12/22/2013 8:33:39 PM]: Invalid object name 'dbo.trace_xe_action_map'.
[SPManager] [ERROR] [12/22/2013 8:33:39 PM]: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SharePoint.Utilities.SqlSession.ExecuteNonQuery(SqlCommand command)
at Microsoft.SharePoint.Upgrade.SPSqlCommandFactory.GetRowCount(String strTable)
at Microsoft.SharePoint.Upgrade.SPDatabaseWssSequence.LogTableRowCounts()
at Microsoft.SharePoint.Upgrade.SPDatabaseWssSequence.PreUpgrade()
at Microsoft.SharePoint.Upgrade.SPContentDatabaseSequence.PreUpgrade()
at Microsoft.SharePoint.Upgrade.SPManager.Upgrade(Object o, Boolean bRecurse)
When you run psconfig, you see the count of the successful/unsuccessful configuration settings.  This gives you an idea of how many of these errors you need to fix.
 

image

 

So, what I did was create a synonym in the context of each database as follows:

use [SharePoint_AdminContent_a974ce72-f4c4-43ff-a3bc-fe08e4e0a553]
go
CREATE SYNONYM [dbo].[trace_xe_action_map] FOR [sys].[trace_xe_action_map];
CREATE SYNONYM [dbo].[trace_xe_event_map] FOR [sys].[trace_xe_event_map];
go
use [WSS_Content_RXSP2007_MySite]
go
CREATE SYNONYM [dbo].[trace_xe_action_map] FOR [sys].[trace_xe_action_map];
CREATE SYNONYM [dbo].[trace_xe_event_map] FOR [sys].[trace_xe_event_map];
go
use [SharePoint_Config_RXSP2007]
go
CREATE SYNONYM [dbo].[trace_xe_action_map] FOR [sys].[trace_xe_action_map];
CREATE SYNONYM [dbo].[trace_xe_event_map] FOR [sys].[trace_xe_event_map];
go
use [WSS_Content_RXSP2007]
go
CREATE SYNONYM [dbo].[trace_xe_action_map] FOR [sys].[trace_xe_action_map];
CREATE SYNONYM [dbo].[trace_xe_event_map] FOR [sys].[trace_xe_event_map];
go
 

After creating the synonyms in each database, psconfig ran successfully.

clip_image001[5]

 

And more importantly, the configuration wizard completed successfully!

clip_image002

Advertisements

12 Responses to “Installing Microsoft Office SharePoint Server 2007 on SQL Server 2012”


  1. 1 David
    April 7, 2014 at 9:53 am

    Helped me out of jam… Thanks

  2. April 17, 2014 at 10:55 pm

    Thank you heaps for your through troubleshooting, I don’t envy the effort you went through. This has saved me from a huge headache 🙂

  3. April 17, 2014 at 10:56 pm

    Thanks heaps for your troubleshooting efforts, saved me a heap of time

  4. April 17, 2014 at 11:40 pm

    To upgrade to Sharepoint 2010 after your fix you need to write:

    USE [master];
    GRANT EXEC ON dbo.sp_dboption TO PUBLIC;

  5. 5 Ben
    January 7, 2015 at 12:53 pm

    You are a life saver!

  6. 6 howarthcd
    April 28, 2015 at 2:34 pm

    Thanks for the advice. After running SharePoint 2007 on SQL Server 2012 for a while, have you experienced any problems that are considered to be related to the version of SQL Server being used?

  7. 7 Jason Lochan
    November 25, 2015 at 10:40 am

    Pure gold, was able to stand up a 2007 farm on SQL 2014 in a day thanks to you sharing your findings — much SPThanks

  8. 8 Russell Wright
    April 13, 2016 at 3:26 pm

    Sometimes it amazes even me when I go back and read about what I did! The amount of time lost in my life I will never get back…

  9. 12 sergey
    September 4, 2016 at 5:07 am

    Approved with SQL 2014 as well!
    Thanks a lot!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Asif Rehmani’s SharePoint Videos

SharePoint-Videos

Click to access a wealth of SharePoint videos

SharePoint Rx

SharePoint Rx Home

Categories

Posts by Date

December 2013
M T W T F S S
« Jul   Jan »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
Support Wikipedia

%d bloggers like this: