Using SQL express with WSS 3.0 - Tim O'Malley

I am trying to configure WSS 3.0 to use SQL Express 2005 instead of SQL
embedded that comes with it.

What is the best way to accomplish this?  I've tried reinstalling but
it doesn't ask me for any database details.

Thanks,

Using SQL express with WSS 3.0 - Jerry Rasmussen

Not sure why would want to do this. The SQL that comes with WSS 3.0 does
not have the size limitations of SQL Express. But to each his own.  Here
is what I would do.  Run the sql surface area configuration wizard I
would allow local and remote connections.  I would also load SQL Express
Manager to help trouble shoot any problems that I might have during
the install of WSS 3.0

Using SQL express with WSS 3.0 - Tim O'Malley

I'm using SQL express in my dev environment.   In production we'll be
using SQL Enterprise.

I'd be happy with SQL embedded if I could access it through Management
Studio.  I have some custom tables that need to be inserted into the
DB.  From what I have read, with Embedded Edition, you can't access it
through tools like SQL Management Studio.

Thanks,

Using SQL express with WSS 3.0 - Tim O'Malley

Thanks for the link.   Will this work with WSS 3.0 (2007)?  The article
looks like it was for WSS 2.0 (2003)?

Using SQL express with WSS 3.0 - falloffalo

That's a GREAT article being linked to, wish I'd seen that before I did
it all myself.  This is how I'm running WSS 3.0 as well, several
reasons for doing so:

- Easier database administration from SQL Management Studio
- Stand-alone SQL 2005 makes it possible to be used for other database
applications
- SQL 2005 is integrated with Visual Studio, making application
development easier
- And if/when the database size or memory usage limitations become a
problem, it's a piece of cake to upgrade to SQL 2005 Standard.  Until
then we're saving cash by using only what we need.

Anthony

Using SQL express with WSS 3.0 - furthest

Hello Tim O'Malley,

Here is an article "Migrating from WMSDE to SQL Server 2005"

http://office.microsoft.com/en-us/winsharepointadmin/HA100363461033.aspx

I would suspect that the process should be the same.

Using SQL express with WSS 3.0 - Mike Walsh

I asked a fellow MVP about these points.

He said that

a) you could use SQL Management Studio with W.I.D. (but perhaps not
remotely)

b) yes, of course, you are right (other apps; same db)

c) W.I.D. also integrates with Visual Studio

d) It's just as easy to upgrade to SQL Server 2005 (full) from W.I.D. (which
is embedded SQL Server 2005 express) as it is from SQL Server 2005 express.

On balance and unless you really need the same data base system for
different apps, I would go for the embedded version.

But you are of course entitled to do what you like as long as it works for
you. It just seems to me like a) unnecessary work and b) accepting an
unnecessary size restriction.


Mike Walsh

Using SQL express with WSS 3.0 - Rand

i am having a hard time finding any information on WID whatsoever....

i would like to do a standalone install on 1 windows server 2003 r2
enterprise box with IIS and SQL 2005 standard and WSS 3 on it.... previously
with V2 of WSS, i installed SQL 2000 standard (wss 2 didn't support SQL 2005
for initial install), then installed WSS 2 and WSS 2 created the databases in
SQL 2000 standard. i then found a pretty straight forward document for
upgrading SQL 2000 sharepoint to sql 2005 sharpoint.

however... i set up a test server, installed sql standard 2005, then
installed WSS 3, and WSS 3 completely ignored the SQL 2005 and installed WID.
it seemed with WID that full text search was available, as was sharepoint
usage analysis, which led me to believe that it was a 'special' version of
sql shipping with sharepoint V3... however... i cannot seeem to find ANY
information on WID. you say there isn';t the same limit on database size in
WID as there is in SQL express.... is there a limit at all? is there
somewhere where i can find information on WID? do you know of how i could go
about installing WSS 3 on a single server using SQL 2005 standard, ON THE
SAME BOX?

Thanks
Randy

Using SQL express with WSS 3.0 - Mike Walsh

Lot's of questions so I'll just pick up what seem to be the key ones.

To install on a single server using standard SQL Server 2005, install first
SQL Server 2005 and then when installing WSS 3.0 select the farm (lower)
install option (and be careful what you select on the next page too)

Windows Internal Database has no size limits for the size of the database.
It can however not be used to store the data of any other applications
(apart from "Windows internal" ones).

It does not use Full-Text search (which was what was used in WSS 2.0) but
uses the same kind of search as used in MOSS 2007 but restricted only to a
single site (and to data in the WSS database only)


Mike Walsh
WSS FAQ www.wssv3faq.com / wss.collutions.com
No private questions please (additions to FAQ welcome)

Using SQL express with WSS 3.0 - Rand

so really then, why would anyone actually install WSS 3 with SQL 2005 unless
they were doing a farm install and they wanted SQL on a seperate box? for a
standalone installation, if you had nothing else you wanted to store in the
database other than WSS databases, for what reason do you suspect, someone
would want to use sql 2005 over WID? i am just looking for some cons to using
WID in a standalone environment i guess... :P

Using SQL express with WSS 3.0 - Mike Walsh

I'm tending towards the same feeling as you. Now that unlike WSS 2.0 you do
get sort (and it's the same sort) built-in with the embedded database what's
the point of paying for a "real database system" if WSS 3.0 is all you're
going to use it for.

I heard some explanations once from MS which didn't really convince me (and
I've forgotten what they were); there's also talk of you not being able to
connect remotely to the "windows internal database" (which I can neither
confirm or deny) and I suppose that you don't get any tools with it as you
would with "proper" SQL Server 2005.

If anyone who has more could contribute to this we'd both (I hope I can
speak for you too) be grateful.

Mike Walsh

Using SQL express with WSS 3.0 - callahan

There is practically no direct and practical information about SQL's
embedded edition (the WID that gets installed with WSS v3).

According to legend, it doesn't have the 4gb limit to size that express has,
it seems to allow unlimited concurrent connections (like WMSDE and Express)
and it doesn't seem able to handle remote access (I guess kind of like
WMSDE, because as far as I know Express can be accessed remotely <please
correct me if I am wrong>).

It's interesting, WMSDE (the embedded edition of MSDE) doesn't have some of
MSDE's limitations.  But, when SQL Express came out, it *lost* some of the
abilities WMSDE has, like supporting 2GB ram or 2 processors.

I really wish there were some real, practical, information about SQL's
embedded edition (the WID), instead of the hints and rumors we get.  This is
frustrating.

-callahan

Using SQL express with WSS 3.0 - Mike Walsh

It's not just a legend. It is 100% certain that WID for WSS doesn't have the
4GB limit but WID for MOSS does.

It's not surprising that SQL Express doesn't have the abilities WMSDE hs.
You have to compare SQL Express with MSDE 2.0 not with WMSDE.

Mike Walsh

Using SQL express with WSS 3.0 - Rand

i whole-heartedly agree with you callahan; it is very frustrating not having
any information about WID whatsoever...

1) how much ram does it permit?
2) are there limits to the number of concurrent connections?
3) are there any limitations to WID in respect to sharepoint functionality,
as compared to SQL 2005 standard?
4) what the hell is WID? :P

Using SQL express with WSS 3.0 - Rand

here's one for ya mikey...

do you know if it is possible (and if so, what is the command...) to MOVE
the default location of the WID database? my server has 2 partitions, windows
(small) and a storage (large), and i'd like to have the database on the
STORAGE partition....

Randy

Using SQL express with WSS 3.0 - callahan

Randy,

I *think* I can answer a few of your questions.

To start, if you use SSEE with WSUS or WSS, then it's the WID, get it? ; )
In otherwords, SQL server 2005 Embedded Edition *is* a Windows Internal
Database when used "embedded" with programs MS produces that need a database
to run where there may be none available.  You can't get SSEE any other way.

I think, like WMSDE, there isn't the concurrent connection limit that there
was MSDE (therefore you are not limited to 5 connections concurrently).  I
think they might've done away with that.  I am not absolutely certain, but I
think it's true (and mind you, I only work with WSS 2/3, which uses
WMSDE/SSEE and doesn't have the limitations that come with the sharepoint
server's embedded database version) because nowhere have I found any comment
about there being a performance hit due to a concurrence limit.  Also, in
all the documentation I have been able to find about SQL 2005, there is
absolutely no governer (the thing that limits concurrent connections), and I
am pretty sure that is the same in SSEE.

I am on the fence about supported RAM and CPU.  WMSDE could support 2 GB RAM
and 2 CPUs, however, SQL 2005 Express can only support 1GB of RAM and 1 CPU.
So which way did SSEE go?  Did it, in order to give MS's free stuff more
power, go with the WMSDE capabilities for SSEE, or did it use SQL Express's?
I don't know and that could matter down the road in terms of performance
(adding RAM to your server won't matter, having that extra CPU won't matter
either).

As far as Sharepoint, the biggest problem with SSEE (or WID) is the
performance limitations impressed by the potential of size limitation (which
apparently is a non-issue), CPU and RAM limits, lack of management tools,
and lack of remote access.  You cannot support a server farm install with
SSEE because it cannot be connected to by another computer.  However (but I
have never had time to try this), you can use SQL 2005 Express for a server
farm install.  I think the lack of remote access support is a deliberate
disabling of the engine on the part of MS.

With WSS 2 there was a big problem with choosing to use the embedded WMSDE
database, and that was search.  With WSS 2, search was not powered by
sharepoint itself (like it was with SPS), it was powered by the SQL content
databases it was using.  And, wouldn't you know it, MS made sure that WMSDE
could not support full text indexing.  Therefore, if you did a typical
install of WSS 2, you did not get a search field in the upper right corner
of any page on any site, ever.

That shortcoming no longer exists with WSS 3, because it has been given
sharepoint search (and other stuff) as a handmedown from SPS 2003 while SPS
was on it's way to becoming the great and all powerful MOSS.

Finally, I don't have a carved in stone list of differences between SSEE and
SQL Express.  Man oh man, I wish I did.

HTH,
-callahan

Using SQL express with WSS 3.0 - Mike Walsh

It is indeed the same as WMSDE in that it doesn't have any concurrent
connection limits.

But supported RAM and supported number of processors are new questions to me
and I'll try to find out about them.

Mike Walsh

Using SQL express with WSS 3.0 - Rand

here's one for ya mikey...

do you know if it is possible (and if so, what is the command...) to MOVE
the default location of the WID database? my server has 2 partitions, windows
(small) and a storage (large), and i'd like to have the database on the
STORAGE partition....

Randy

Using SQL express with WSS 3.0 - callahan

LOL.  I asked that question myself.  I thought I had it when I read a help
file saying that you could move it by indicating where the index files went,
but chances are good that's wrong.  Mike's looking into it. : )

-callahan

Using SQL express with WSS 3.0 - Rand

Does anyone know if you change the physical disk location where WID stores
the databases?

do you know if it is possible (and if so, what is the command...) to MOVE
the default location of the WID database? my server has 2 partitions, windows
(small) and a storage (large), and i'd like to have the database on the
STORAGE partition....

Using SQL express with WSS 3.0 - randy.oldha

MIKEY!

i've been abandoned! :P

Callahan says you were looking into this one...

maybe Microsoft has silenced you because you now know too much about
WID... :D

Using SQL express with WSS 3.0 - Mike Walsh

No, I haven't abandoned anyone. I've asked the question twice now - I have
the feeling most of them were away for a loong Christmas break.

I don't want to ask too often, but I've just asked another similar but
easier question which I hope to get an answer to and then I can post this
one as a follow-up to that.

(<grin> they haven't sent me a "loaner" Ferrari portable if that's what you
were wondering </grin>

Mike Walsh
WSS FAQ www.wssv3faq.com / wss.collutions.com
No private questions please (additions to FAQ welcome)

Using SQL express with WSS 3.0 - Rand

thanks mikey! i appreciate it...

a ferari for xmas eh?

Using SQL express with WSS 3.0 - Mike Walsh

Do me a favour and drop the "Mikey"

(The Ferrari I'm writing this on I bought myself a year and a bit ago. The
car I've crossed off my list.)

Mike Walsh

Using SQL express with WSS 3.0 - Rand

Sure thing Mike.

I say keep the Ferrari car on the list... you never know... some rich
philanthropist might see it and think: "this man needs a Ferrari!"

Thanks again for helping me track down an answer for my quandry

Using SQL express with WSS 3.0 - OakTree8

I've got 2 problems with WID that I havent been able to figure out.

1)  How to move it to somewhere other than the C drive.  I need to put
it on the D Drive.   The one article on microsoft support about how to
move the database was for an older version and the osql commands dont
even install now, so that article doesnt work.

2)  I downloaded the Management Studio as someone suggested but cant
connect locally or remotely.   It sees the database engine and I try to
connect using windows authentication using both local and domain admin
and the user it was installed with but I get a connection error talking
about named pipes.

Any ideas how to get that WID off the c Drive and onto the d drive?

Using SQL express with WSS 3.0 - Rand

well; i did some fiddling today to see if i installed wss 3 on a server and
let it install WID, if i could UPGRADE to sql 2005 standard. the long and
short of it was NO, i couldn't upgrade the NAMED instance. i received an
error message that essentially indicated that i could not upgrade the named
instance since the name was 'reserved'... likely because the MICROSFT##SSEE
name (or whatever it was called) is reserved for wss v 3...ergo it will not
allow you to have a sql 2005 standard instance named the same thing...


also, after sql 2005 std was installed, i tried to connect to the
MICROSOFT##SSEE instance and received an error message: "An error has
occurred while establishing a connection to the server. when connecting to
SQL Server 2005, this failure may be caused by te fact that under the default
settings SQL server does not allow remote connections. (provider: SQL NETwork
interfaces, error 26- Error Locating Server/Instance specified) (Microsoft
Server)".... so it seems that the MICROSOFT##SSEE instance of sql installed
by WID DOES NOT permit connections by the sql 2005 server management
studio....

so still... anyone have any idea if you can change the location of the
database files in WID? and if so, what are the commands to do so?

Using SQL express with WSS 3.0 - maeltor

Hi everyone,

I have some (hopefully) useful information for you about moving the
database (I just did it myself).

The installation i'm using is using the WID (Embedded SQL, it looks
like its using WMSDE but I can't tell because i'm not a full time
database admin.
The way I got this to work is:

Go into Sharepoint 3.0 Central Administration -> Operations -> Backup
& Restore.  Perform a FULL Farm backup (its pretty self explanatory).
Once its completed with no errors, select "Restore from Backup"

Select the Backup Location (where you had the backup operation place
the backup content files), select a full Restore Process and select
the full farm.  There is an option on the next screen for selecting a
location, (you have to also change the database names - both of them,
or the restore will fail).

Depending on the size of your database it may take a while.  I did a
quick reboot when it was done just to make sure Sharepoint started up
using the new database locations.  Seemed to work fine for me.


Moving on:  This topic has been discussed at length here regarding
what is better to use (WID, WMSDE or SSEE2005).  Personally I just
want to know if there is a way of MANAGING the database that comes
with WSS 3.0 Stand Alone install.  SSMSEE cannot access the database.
I get the same error as Randy does above.

Using SQL express with WSS 3.0 - Rand

thanks big guy! so that was moving WID to SQL 2005 std?

Using SQL express with WSS 3.0 - maeltor

Actually, that was JUST moving the location of the database files to a
different hard drive partition.  I'm not sure how to upgrade it to
2005 standard, as I am still having the problems of connecting to it
with SQL Management Studio Express.

Can Mike shed any light on that?

-Josh

Using SQL express with WSS 3.0 - Jerry Rasmussen

Search for articles on connecting to SQL express.  You will need to run
the surface configuration utility.

Using SQL express with WSS 3.0 - maeltor

I've seen this (the surface utility), but it appears that WSS doesn't
use SQL Express??  Is that or is that not true?  I've read and heard
conflicting reports.

Using SQL express with WSS 3.0 - Jerry Rasmussen

In a way you are right its WID (Windows Internal Database)  But this
database is a modified version of SQL Express.  This is just a named
instance of a SQL database servername\officeserver.  You should be able
to connect using SQL Management Studio

Using SQL express with WSS 3.0 - maeltor

I figured i should be able to connect but like Randy, i get the
following error:

when connecting to SQL Server 2005, this failure may be caused by te
fact that under the default
settings SQL server does not allow remote connections. (provider: SQL
NETwork
interfaces, error 26- Error Locating Server/Instance specified)
(Microsoft
Server)".


Looks like WID is totally restricted to shared memory instance or
something.  TCP/IP is surely NOT enabled on the instance.  Is there
another way.  I've read that you can connect with named pipes, but how
the hell do you find out what the named pipe is!?

-Josh

Using SQL express with WSS 3.0 - Garry

Josh,



In terms of upgrading to SQL 2005 i'm not sure you can.



I have however, moved the content database from SSEE (WID) to SQL 2005.



I used STSADM to create a DAT file backup of all sites.



C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\STSADM.EXE" -o backup -url http://server_name/path/site -filename d:\backups\backup.dat



I then installed SQL 2005 on the server and created a database called SQL_WSS_Content (The content database in SSEE/WID is WSS_Content)



Then in the Central Administration of WSS, i deleted the content database (SSEE/WIS), pointed the contenc database to the SQL 2005 database then used STSADM to restore the DAT.



C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\STSADM.exe -o restore -url http://server_name/path/site -filename d:\backups\backup.dat –overwrite



This appears to have worked for me, i have it like this in my test environment currently.



Garry