Tag Archive: database


This is information that exists all over the place- the problem is that it is spread around forum comments and the solutions are as plentiful as the questions. I am going to explain how to create connections strings in an ASP.NET web application. The app is hosted on IIS 7.5, Server 2008 R2, and created in Microsoft Visual Web Designer 2010 Express (VWD). For the back-end I use SQL 2008 R2 Standard. VWD uses SQL 2008 Express natively, but I am not going to downgrade for this app.

There is also a little issue of server architecture. I was running into all sorts of problems with IIS and VWD being x64, while the connections drivers were running as x32.

A couple of key points.

  • In order for your application to connect to a database of any sort, it needs a connection string, which is a bit of code inserted into the web.config file in the root of your site.
  • The connections string tells the app: the source of the data, the driver to use, and the data location.
  • Each connection to a database needs to use a specific driver.

The first situation in which you need a connection string is for your app to contain membership information. Most of this stuff is done through wizard-like windows in VWD, but you will often need to change the default settings. This database is created by default when you Configure ASP.NET from within VWD. But I would rather create my own database, and hook up my application to it.

So I first create my database. Open up SQL Server Management Studio. connect to the instance you wish to have your app use. You can create a new instance, but I had an old one not being used, named MyServer\SQLEXPRESS. Do not be confused- this is not actually and Express database, just the name of the instance.

Connect to the instance, use Windows Authentication– this is of course your choice.

Connect to SQL Instance

Connect to SQL Instance

I’m going to glaze over the rest, but here’s what has to happen. You need to create a database, the default settings are alright. You need to grant the account that runs your application pool access to the database. My apppool runs under its own identity, and that identity needs Connect, Read, Select, Update, and Delete rights on the database.

For your ASP.NET security database, it needs to be prepared with a tool for ASP.NET to access it. Make sure you run the right tool, there are many.

C:\Windows\Micrososft.NET has a Framework and a Framework64 folder. Choose the one which is the same architecture as your OS.

So C:\Windows\Micrososft.NET\Framework64\ and then choose the folder that corresponds to the app Framework Version. This can be found many different ways, but if you right-click on the address of your app in solution explorer, and select property pages, you can get an idea.

Framework

Framework

 

So  C:\Windows\Microsoft.NET\Framework64\v4.0.30319\ and click on the application named aspnet_regsql.exe. This will run you through a wizard that prepares the database to hold site membership information.

When you use the wizard in your ASP.NET configuration, it will create all the required connections strings and providers for you, I had to tweak mine a bit.

Here is an example of the connection string I used.

<add name=ApplicationServices connectionString=data source=.\SQLEXPRESS;Integrated Security=True;Initial Catalog=aspnet; providerName=System.Data.SqlClient/>

 The name is only the name of the connection, this san be whatever you wish. The connectionstring is important. this has more arguments such as username, password, security, etc.

 
Important: data source=.\SQLEXPRESS;
This is the source instance. .\ means the local box. You could also do:
 
localhost\instancename
servername\instancename
remoteservername\instancename
  
Then there is Initial catalog=aspnet
This is the database name that this connection uses. There are other ways to format this, but Initial catalog=databasename without prefix works the best for my app.
With the ASP.NET membership security comes some other entries in your web.config, and these need to point at the connectionstrings NAME, in this case AplicationSecurity.
Here are two more examples of connection strings:
<add name=TheDatabaseSQLConnectionString connectionString=Data Source=******MEMBER\SQLEXPRESS;Initial Catalog=TheDatabaseSQL;Integrated Security=True providerName=System.Data.SqlClient;

/> 

 

 <add name=ContactSQLConnectionString connectionString=Data Source=******MEMBER\SQLEXPRESS;Initial Catalog=ContactSQL;Integrated Security=True providerName=System.Data.SqlClient/>

<add name=ContactConnectionString connectionString=Data Source=C:\TheDatabase\thedatabase2\App_Data\Contact.mdb;User Id=Admin;Password=; providerName=Microsoft.Jet.OLEDB.4.0;Data/>

You will notice that the data sources on the second one points to a file location, .mdb. this is because it is a Microsoft Access database, which is not hosted, only a file. you also see the provider name is actually the driver that is used for the connection, Jet in this instance.

You can get these connections strings from inside of VWD.

Click Tools>Connect to Database.

Add Data Connection

Add Data Connection

 

The Data Source is the driver you will use for your connection. I use SQL Server and Microsoft Access.
Click the drop down box and select the server\instancename where your database is located.
 
Select your authentication, mine is Windows.
Select or enter database name is used mostly with SQL databases, pick the database you created or have for the link here. With Access,  you can use Attach a Database file.
 
Now Test Connection, if everything is configured and set up, you should get a success message.
Now click Advanced.
 
There are more things to customize here. In the bottom is a box- this is your connectionstring. You can copy this information and paste it into your web.config, then edit the formatting to match the examples above.
Advanced Properties

Advanced Properties

With these connectionstrings in your web.config, you can drag AccessDatasource and SqlDataSource controls from the VWD toolbox onto your application, and then point them at the correct databases.
 

<asp:SqlDataSource ID=”ListDataSource” runat=”server”ConnectionString=”<%$ ConnectionStrings:ContactSQLConnectionString %>SelectCommand=”select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ‘Contact'”></asp:SqlDataSource>

 

 

<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server”

ConnectionString=”<%$ ConnectionStrings:ContactSQLConnectionString %>

SelectCommand=”SELECT * FROM [Contact]”></asp:SqlDataSource>

 

Note: For Access database files, make sure to add them into your App_Data folder in the application, then point to them there.There is another thing to mess around with when talking about application connections. Well drivers and all that- but I won’t go into that here.

I was having one hell of a time getting my application to talk to my databases. that is because when you design in IIS 7.5 x64, and VWD x64, your application is ment for x64 machines. It does not talk well to x32 drivers (in theory it should, with Microsoft’s backwards compatible drivers). So I changed my application to x32. This can be done using the Build>Configuration Manager… menu in VWD. This can also be set on your application pools in IIS. I changed the apppool serving my web application to support x32, and everything is working great with my older databases and .JET drivers. Right click on your application pool, select Advanced Properties, then change the value for enable 32-bit Applications to True.

Enable 32-bit Applications

Enable 32-bit Applications

Advertisements

Running the SBS 2008 BPA, you receive a warning item that states:

The log file for the Windows SharePoint Services configuration database is larger than 1 GB in size. For information about how to reduce the size of the log file, see the Knowledge Base at http://go.microsoft.com/fwlink/?LinkId=159745.

SQL Config Log Warning in BPA

SQL Config Log Warning in BPA

If you follow the link, Microsoft explains how to make a SQL script that will trim your database. My problem was that even after trimming the database, it was still over 1Gb in size- 1.4Gb actually. Let’s solve this.

This is not a permanent solution, but rather a solution to temporarily shrink the log files. They will eventually build back up- I plan to execute this plan when ever I get the warning.

Also note that doing this process will make the database temporarily “Full Recovery” only. which means that if you use differential backup and recovery, after this process takes place you can only do a full recovery untill the log files build back up over transactions.

Perform a full server backup.

Now check the size of the database.

Log into your Microsoft##SSEE Database (Windows Internal Database).

By entering \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query as your server name. Select Windows Authentication. Click Connect.

Connect to Microsoft##SSEE

Connect to Microsoft##SSEE

You will see several databases- we will be working with the one titled Sharepoint_Config_#########.

Databases

Databases

Right click on that database and select Properties. You will see two numbers for Size and Space Available. Look at the size. Mine was 1449 Mb. I ran the Microsoft suggested SQL script, and it was still the same size. So here is what to do.

Under the options tab of the database properties, change the Recovery Model dropdown from Full to Simple. This tells SQL to truncate the log files, meaning only a full recovery is available.

Recovery Model: Simple

Recovery Model: Simple

Click Ok.

Now Run the logshrink.sql to trim the database. You can do this through a command prompt by running command:

sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E -i
 
-or-
 
You can double-click logshrink.sql. It will ask you to log in, use the same information as above. Above the window that opens, click Execute.
Execute Script

Execute Script

 

Now go back into the Properties of the config database, click Options, and change Recovery Model back to Full.

Trimmed Database

Trimmed Database

You should see your file size WAY smaller now, happy shrinking.

I have been tasked with redesigning my company’s web site. Looking at it, it really is poorly designed, hard to navigate, and a little bit lackluster in graphics and design. While I am no designer, I do have a skill with the web, an objective view of the site, and the drive to fix this. I have been making changes to the live site, testing out templates, changing graphics. This is a really lame thing to do to a live site.

This post is long and can be complex. If you get stuck on a certain part- please post a comment. I will get back to you within an hour or two.

We run Hostmonster.com web hosting.

This runs a Joomla! 1.5.15 website.

All files are transferred via FTP to Hostmonster.com.

All configurations take place in the Administrator section of Joomla!

I had the epiphany that it is pretty dangerous to mess with a live site, so I decided it would be in my best interest to download a copy of the site to my PC, edit it, then replace the current site when it is done. To do this I need a copy of the web server at Hostmonster.com. They use a LAMP, which is Linux-Apache-MySQL-PHP. Now I could use VirtualBox to install Ubuntu on my laptop, and run a LAMP from there. the problem with that is then I would have to configure the virtual OS to communicate via my network, which in my experience can be a pain in the butt.

So instead, I will use a WAMP server. WAMP Server 2 has been released while it is not the recommended program levels listed by Joomla!, it is what I will use.

First, uninstall IIS if it is on your computer. My laptop did have it installed, so go to Add/Remove Programs, then turn Windows Features On or Off. Uncheck Internet Information Services, uninstall, and reboot.

Now, go and download WAMP Server 2 from here.

Once you download the file, run it, and click-through the install steps. I put mine on the D:\ drive because it has good space. I also left all of the install options at default values, such as Browser Choice, SMTP Server, and Email Address. This may not be what you want personally, but for me it works. There is a great tutorial with pictures listed here– Thanks TeamTutorials!

A good way to see if your server is online is to left-click the icon in your tray, and then select Localhost. If you get a page looking like this one, WAMP Server 2 is configured and running.

WAMP Server 2

WAMP Server 2

Keep in mind, that I have very little experience with Apache, PHP, MySQL including setup and install of a web site. In the past I used IIS 5, 6, and 7, SQL Server Express 2005, and Standard 2006. As much as I would love to copy this website using those technologies, the migration would be more trouble than it is worth.

So, now let’s get Joomla up and running.

Go to the Joomla! website and download the package, which can be found here. They only have the full install for 1.5.22 available, though there are updates for 1.5.15. I will have to deal with installing the newest version and see if it works- if it doesn’t I can always start over.

Upon further exploration, they do have the full package of 1.5.15 available here, so I downloaded that instead. Note: Make sure you download the release.ZIP file, which is appropriate for Windows installations.

I also went here, and downloaded the Installation Manual PDF and printed it.

Before we start the Joomla! install, you can double-check that MySQL, XML, and Zlib functionality is enabled by checking the PHP.ini file, which is located at: %installdir%/camp/bin/php/php5.3.0

Furthermore, lets check the installation using a PHP script. Open Notepad (Start>Run>Notepad)m and type the following into the text file.

<?php
//Show all information
phpinfo();
?>

Save this file and name it as phpinfo.php, and put it in the root of your site, which is Localhost at this time. This is located at: %installdir%/wamp/www

So save the file phpinfo.php in that directory, which will only have one file in it, index.php. Open up the web browser you used for the WAMP Server 2 install, and type http://localhost/phpinfo.php in the address bar. It should display a page like this:

PHPInfo.PHP Output

PHPInfo.PHP Output

After you check this output- which contains information about your system and WAMP, MAKE SURE TO DELETE THE PHPINFO.PHP FILE!

 Now we unzip the Joomla! zip file we downloaded from their site. I extracted it to a folder on my desktop for ease of moving. I use WinRAR, there is also WinZIP, 7-ZIP, Windows built-in, and many more.

Unzipping

Unzipping

Before beginning the Joomla! install, which will add and edit files to the WAMP Server 2 installation, I would like a backup in case things go wrong, so that I can start over. I will let you choose how to back them up, I simply created a restore point of that drive, as well as copied the WAMP folder to a remote location. Overkill maybe, but we will see.

Since we are doing a Localhost install, we simply need to copy the unzipped Joomla! files into our web root, which is located at: %installdir%/wamp/www/

You will get a pop-up asking if you want to overwrite index.php– select copy and replace.

Copy and Replace

Copy and Replace

Now navigate to http://localhost, or click the WAMP server icon from your task bar and select Localhost.

Select your language and click next.

Make sure a green Yes is displayed next to all items of the pre-installation check. If any of them say no (They shouldn’t if you follow these instructions), you should stop here and correct the problem by installing a supported version of the problem, or by Googling the exact problem.

Recommended settings are nice, and typically I would follow them. In this instance, I am going to leave them alone- which is with two red items. They are for Display Errors, and Output buffering- both of which I want.

Joomla! Checks

Joomla! Checks

Hit next a few times untill you get to the Database Configuration screen.

Database type is MySQL.

Host name is Localhost.

If you did the default install the user name is root.

There is no default password. I am not a fan of not having a password, so I am going to add-on. Click the wamp icon in your tray. Click MySQL>MySQL Console.

This opens up a DOS looking box asking for a password. Hit enter. You are now at the MySQL command prompt. Use this command to set the root password.

MySQL Password

MySQL Password

SET PASSWORD = PASSWORD(‘password‘);

The italicized text is your actual password. Hit enter, and exit out of the MySQL Console. Back in your Joomla! Installation, enter the password you just set in the password field.

At this point I realize I am going to be restoring a backup of my already configured remote web site, which has a user name and password, a database name, etc. I went to my hostmonster account and clicked MySQL Databases from the control panel, and found out my information is:

Manage User Privileges

User: ********_jml01
Database: ********_jml01

Since I have no ide what is going on with MySQL, I am going to use the default set up, root, with my password, and default database. Ill remember this spot if I run into problems later.

After a few tries of not being able to create or connect to any database, I checked MySQL and found out that there was no database. At the MySQL prompt type SELECT DATABASE(); and it will return a value of NULL.

Click on the wamp icon in your tray, and select PHPMyAdmin

MyPHPAdmin

MyPHPAdmin

Enter the database name for Create New Database. In this case I named it the same name that the one on my Hostmonster.com server is named.

Back on the Joomla! install, enter that database name and hit next. I entered the site name of my live site, which is found under Global Configuration on the site. I set up my user name/email and password. I also did not select migrate or local files. Once you enter the top information just hit next.

You now have to remove the installation directory from the %installdir%/wamp/www/ directory. Just delete it.

Now, enter http://localhost/administrator in your web browser.

Login with the user name admin, and whatever password you just entered several seconds ago.

Alright, this is where things got confusing for me. I have working backups of my live site using Hostmonster.com’s backup utility, which is in the control panel. This is not what we need, or at least what i need.

What we will do now is download two packages.

Akeeba Backup  3.1.4which needs to be installed of both live and local Joomla! sites, and

Akeeba Kickstart 3.1.5, which needs to be installed only on the local Joomla! site.

This will take different setup on both the live and localhost sites.

Live site:

Go to your sites administration, click Extensions>Install/Uninstall.

Akeeba Install

Akeeba Install

Click browse, and select the file. I downloaded this file to the computer I am installingmy Joomla! local site to. Now click upload and install. You will get a success page.

Might as well start the backup now. Go to Components>Akeeba Backup, and select backup now.

Akeeba Backup

Akeeba Backup

 

Now on your Local server (PC):

Install it the same way, except we don’t need to upload it to Hostmonster.com. Go to your localhost administrator settings, Extensions>Install/Uninstall. Select the file from your local PC, and install it. Now extract the kickstart zip. You will be looking at some files. Select them all drop drop them into the root of your site on localhost, or %installdir%/wamp/www/

Now we need to get the Akeeba backup onto our localhost. I downloaded vie the web browser- you should not do this, it may corrupt the file. The reason I did this was because I could not find the backup vie FTP on my site.

Once you have the backup file, put it in the same directory of your localhost that you put the kickstart files into, which is the root directory WWW.

You will get a bar like this one.

Akeeba Kickstart

Akeeba Kickstart

Once this completes, you will se a green button, click it to run the installer and restore.

You can check over the things on this page, specifically check the directories tab near the bottom to make sure your directories are correct.

Akeeba Backup Installer

Akeeba Backup Installer

Click Next.

Set Connection Parameters to match what you selected when setting up Joomla!. User root, the password, and the database. Mine is ********_jml01.

Click next, leave the default values alone. I entered my sites public address, and did not force overwrite temp directory placement. Hit finish, close the window back to Akeeba Kickstart, and select clean up now.

Click view your site’s front end- there is your copy!

So running the SBS 2008 BPA, I received a low disk space error, less than 15%. Now I get these errors often, and I have moved everything I could off of the drive, including Sharepoint Content, Redirected Folders, Exchange Databases, Installed Programs such as Microsoft Office.

BPA

BPA

Man, my space IS low, 9 Gb out of 60 Gb free. Let me clean out what I can. I empty all the temp folders, delete some empty folders, some setup logs… Wow, I actually recovered 10 Mb of space. Great.

Then I thought about Windows Server Update Services- I know they keep content somewhere (If you have them set to download and store content, which I do). Drats, that is on D:\ as well. (If you need to move WSUS CONTENT, this is not your post).

What about the database files themselves? I go to C:\WSUS\SUSDB\UpdateServicesDbFiles\ and there they are. Two files:

SUSDB.mdf

SUSDB_log.ldf

*** Stop Update Services Service in Services.mmc ***

Having worked with databases before, I know it is not as simple as dragging and dropping the files. So lets work in SQL.

Goto Start>All Programs>Microsoft SQL Server 2005> and select SQL Server Manager Express.

The program opens, and gives you the Object Explorer. You need to connect to a database instance to work with the database. WSUS uses Windows Internal Database, so let’s connect to that one. You can’t log in with sa, or with Windows Auth even if you are an admin- so enter this in the server name:

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

Leave it on Windows Auth, and hit connect. Expand Databases, and your looking at quite a few of them, mostly related to Sharepoint. The one we want is SUSDB.

First, and always, BACK IT UP. This can be done by right clicking the SUSDB and selecting Tasks>Back Up. Now you will have to choose your location, medium type, and file name yourself. For me, I picked a removable HDD (A:/) and named the backup…

Backup

Backup

SUSDB.bak

Let that execute, it might take some time (mine took roughly 7 minutes). Once you get the success message, it’s time to move this DB. Since databases have active connections, and moving the files with these connections can break the the entire internet, lets DETACH the database before moving it.

On a side note this can all be done via command line and sqlcmd. I am not comfortable with the language so I just use the GUI.

Go back to SQLMSE and right click the SUSDB again. This time click Tasks>Detach.

Detach

Detach

You get a screen with one line and some check boxes. You can change some of the boxes depending upon your needs, but for this one, we will select Drop Connections and Keep Full Text Catalog, which is selected by default. As you can see the DB has current connections (such as SUS clients).

Drop Connections

Drop Connections

This will detach the DB. Go back to Windows Explorer, and navigate to the C: drive. Grab the entire WSUS folder, and move it to your target drive. I moved mine to D:\WSUS, which is where my catalog is as well. Might want to give it it’s own directory to be safe.

Now we need to re-attach the database. Go back to SQLSME and right-click on instance, in this case, \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query (SQL Server 9.0.4035 – Domain\UserName).

Click Tasks>Attach.

Attach

Attach

Click Add, and navigate to your new database location.

Choose Location

Choose Location

Click ok. Let it work, it will gray out and take a bit.

Attaching

Attaching

Once it is complete, double-check the database file location by right-clicking SUSDB and selecting properties. Select Files on the left, and look at the Path. You should see your new database path listed there.

Path

Path

Your done. Close everything out, and double-check both the SBS Console and the WSUS Console to make sure everything is synchronized and working. That just recovered almost 4 Gb of space on my otherwise picked-clean C: drive.

*** Start Update Services Service in Services.mmc ***

Running the Exchange 2007 BPA today brought back a few errors, let’s go about setting them right. the first is Max Database Cache Size.

Exchange BPA Warning

Exchange BPA Warning

As you can see there are other errors, for other posts. So, if you click the Tell me more about this link, it takes you to the MS Technet post here. They ramble on and on describing the problem and defaults, etc. Forget that, let’s just fix it (though I of course read it all).

MS Technet says “To better allow for cache growth, we recommend that the DatabaseMaxCacheSize value be increased from 128 MB to 512 MB on transport servers that have 4 GB or more of RAM.”

Well, I have 8 GB of RAM, and though I do not experience heavy message loads, I feel like the cache size should be higher than a low 128Mb (134 really).

MS Technet then says “To improve performance by using version buckets and to allow for increased database cache growth, increase the DatabaseMaxCacheSize value from 128 MB to 512 MB on Hub Transport or Edge Transport servers that have 4 GB or more of RAM.”

We will start by editing an Exchange config file with a value for DatabaseMaxCacheSize.

Start>Run and enter %ProgramFiles%\Microsoft\Exchange Server\Bin

Find the file EdgeTransport.exe.config and right click it, selecting edit (Use Notepad). If Edit is not an option, click Open With and select Notepad.

NOTE: I like to backup all system files before I edit them. I have a spceial folder that I keep edited files in case I need them. In this case, I copy EdgeTransport.exe.config and place it in my backup folder (removable HDD) and add .old to it.

On the page displayed, about half way down is a line:     <add key=”DatabaseMaxCacheSize” value=”134217728″ />

This is our value. Replace the 134217728 with 536870912.

Exit the EdgeTransport.exe.config and make sure you save it on the way out.

MS Technet then tells us to stop the Exchange Transport service and restart it by using a command prompt and net stop msexchangetransport && net start msexchangetransport

I would rather do the GUI, so I click Start>Run and type in Services.msc

Right click Microsoft Exchange Transport and click Restart.

Restart Transport

Restart Transport

Thats it. Rerun the BPA and the warning is gone.

If you notice stability issues with Exchange, revert this setting back to the old setting by editing, or restore the .old file.

%d bloggers like this: