Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1125
  • Last Modified:

SharePoint & SQL Express Question

I have the free version of SharePoint 2010 & it is using the free database that is installed on the same server with the 4GB size limit. I just installed SQl Express 2008 R2 because it has a 10GB limitation. I have backed up & restored the DB to the SQL Express Server via the MS SQL Server Mgt Studio.

My question is, how do I go into SharePoint Central Admin & point to the SQL Express server instead of pointing to the DB that was installed by default when I installed SharePoint Foundations 2010?
0
wantabe2
Asked:
wantabe2
  • 12
  • 12
  • 2
1 Solution
 
QPRCommented:
http://drekendrop.blogspot.co.nz/2011/03/sharepoint-2010-specify-default.html?m=1

Also remember to create your SQL related logins on thenew DB server
0
 
Justin SmithSr. System EngineerCommented:
Have you actually tried the approach in the link above?  I'm 99% sure this only sets the database to create new databases on going forward.

So I assume you installed R2 as a new instance, rather than just upgrading your current SQL instance?  If so, the easiest thing to do is probably create a SQL alias with the name of your 2008 instance, and point it to your R2 instance.

http://blog.falchionconsulting.com/index.php/2008/06/moving-databases-the-easy-way/
0
 
QPRCommented:
You are right Ach1lles, I was answering the question about specifying the database server in central admin but I didn't take the whole question into consideration.
Specifying an alias of the previous server name is the easiest way to get up and running.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
wantabe2Author Commented:
ok, thanks to both...I did install SQL Express on the same sevrer seperate. Then I went unto the mgt studio & created a new tables & named it them the same as the live. I then backed up each live table in the DB & then restored it to the other...I hope this makes sense. So now I "think" Im ready to do as yo mentioned above...i think
0
 
Justin SmithSr. System EngineerCommented:
Just make sure you double check permissions on the new databases.  The farm account should be a dbo on all databases.  The web app pool account should be a dbo on content databases.
0
 
wantabe2Author Commented:
Permissions are good...

You mentioned:

" If so, the easiest thing to do is probably create a SQL alias with the name of your 2008 instance, and point it to your R2 instance."

The current live alias is SERVERNAME/SHAREPOINT
 
The new instance is SERVERNAME/SQLEXPRESS

If I go into SQL Express I see no alias listed with the old/current live DB name...Is there none there by default?
0
 
Justin SmithSr. System EngineerCommented:
Correct. None by default
0
 
wantabe2Author Commented:
Ok, I have the new instance created with the name of the old server which is SERVERNAME/SHAREPOINT

I went into Central Admin>Application Mgt>Specify the default server & changed the Database Server to the the SQLEXPRESS instance

I rebooted the server & check & in central admin>system settings>mgt servers in this farm, it still shows the configuration databse pointed to the old instance....
0
 
Justin SmithSr. System EngineerCommented:
Just forget about the "default server" in Central Admin.  That doesn't really mean anything for you.

These are the steps you need to do, let me know what has or hasn't been done.

Old database server: servername\sharepoint
New database server: servername\sqlexpress

1. Create SQL Alias called servername\sharepoint. This should be of type TCP\IP and should point to servername\sqlexpress

2. Verify farm account has dbo permissions to the configuration database on the \sqlexpress instance

3. Verify farm account has dbo permissions to the content databases on the \sqlexpress instance

4. Verify the web app pool account has dbo permissions to the content databases on \sqlexpress
0
 
Justin SmithSr. System EngineerCommented:
AFter you do this, nothing has to be done within SharePoint.  SharePoint will still think servername\sharepoint is it's database server.  On the backend, all requests made to servername\sharepoint will be going to the new servername\sqlexpress because of the alias.
0
 
wantabe2Author Commented:
Ok, I've done everything you mentioned except steps 2, 3, and 4. I didn't do those steps becauseI don't see a web app pool account or farm account listed.

Pointing it to the new instance worked because I stopped getting the event ID saying the database had exceeded the 4GB limit so that part of it is working.

When I first created the instance & rebooted, all worked fine a few minutes then now I can't get access to the site or central admin. I'm thinking it has somethign to do with steps 2, 3, and 4....any more advice & thanks you so much for getting me this far.
0
 
Justin SmithSr. System EngineerCommented:
"When I first created the instance & rebooted, all worked fine"......that doesnt make any sense to me.  

Please post a screen shot showing your SQL alias's.  Also post a screen shot showing SQL Configuration Manager (Start - Programs - SQL - Config manager)
0
 
wantabe2Author Commented:
Here you go. I also attached the event viewer so you could see the errors. The conf mgr is where I created the aliases
config-mgr.JPG
event-log.JPG
0
 
Justin SmithSr. System EngineerCommented:
Create the same alias under the 32 bit node as well.

Click on the Services node in Config Manager and post a screen shot.
0
 
wantabe2Author Commented:
Done, here's the requested screenshot:
services.JPG
0
 
Justin SmithSr. System EngineerCommented:
Did you set a static port on the alias?  Do you know what port each instance is using?

You might try starting the SQL Browser service, then seeing if you can access the site.
0
 
wantabe2Author Commented:
I didn't set a static port but I do know the central admin is using port 32712 because when I try to open it I get a login box & when I type in the UN & PW I get the HTTP 500 Internal error page error.
0
 
Justin SmithSr. System EngineerCommented:
CA port is irrelevant.  We are talking about the SQL port.
0
 
Justin SmithSr. System EngineerCommented:
If you decide you ever need remote assistance, I'm available for hire.
0
 
wantabe2Author Commented:
thanks, I removed the alias & all works fine now.....I'll try to figure out what SQL port it is using & see if I can assign a static port & see what happens.
0
 
wantabe2Author Commented:
I removed the alias & re-created it. All worked fine for a few minutes. I cleared the event log & a few seconds before me not being abel to access CA or my SharePoint site I started getting event ID 5586 in the event log. It says:

A network-related or instance specific error occured while establishing a connection to SQL server. The server was not found or was not accessible. Verify that the instance name is correct & that SQL Server is configured to allow remote connecitions. (provider:SQL Network Interface, error 28 -Server doesn't support requested protocol)
0
 
Justin SmithSr. System EngineerCommented:
Removing the alias just points back to your original 2008 instance.  Just so you know.
0
 
Justin SmithSr. System EngineerCommented:
I'm about out of recommendations, without seeing the environment.  I've used the Alias many times and it is a proven solution.
0
 
wantabe2Author Commented:
One more thing & I'll stop. Maybe this will make sense...The only difference now is, with the aliases re-created, AND the broser service started I can access the SP site but can't access the CA....hopefully that'll help
0
 
wantabe2Author Commented:
never mind, the site just went down again so scratch that...
0
 
wantabe2Author Commented:
I fixed it!!! The issue was I did not have Name Pipes or TCP/IP enabled for the SQL Server Network Configuration. I could not have gotten this far without the help from achilles. Thanks again.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 12
  • 12
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now