Posted by: Gabe Hilado in Database,SQL Server on January 16th, 2013

I just started using SQL Server Project template in Visual Studio 2012 and my first impression? This is great!

There are several ways we traditionally create databases and manage the schema over time. Sometimes you just go to SQL Server Management Studio and simply create database objects such as tables, views, indices, and stored procedures directly into the database. I use this approach for proof-of-concept type projects or super small projects that I know it will be faster to just design the schema and create the database objects right away using SQL Server Management Studio. Also, the expectation in this approach is that once I’m done with the database, there is very minimal change that I will change the schema again.

That’s the outlier scenario though when it comes to projects. Typically, when we work on application development project where we’re also tasked to design and implement the database too, we create database scripts in order to make deployments not only in production, but in development, test, and staging environments  reproducible also. These database scripts, which contains SQL statements to create tables, views, stored procedures and so forth, typically are updated during the duration of the project, as we add more tables, maybe modify tables, and adjust database objects according to the project requirements.

In my past projects, the initial  database scripts are mostly CREATE in nature. Once the databases are deployed to production, we start adding ALTER statements and drop-if-exist prefixes-before-create statements into the scripts. By doing this, staging our different database environments are still repeatable. But then, you run into the “dependency challenge” with this approach. Say, you updated a stored procedure to take into account new columns in a table because of feature enhancements in the application, you need to remember first to run the alter-table-statement that adds those new columns in the first place. It’s doable but this approach is prone to human error. Sometimes, during a deployment, some SQL scripts that need to be executed very early in the deployment process gets forgotten such that by the time a latter script, which is dependent on the earlier script command which was forgotten, the latter script errors out and fails to execute on the database.

Want to make your life easier managing SQL Server schema? Use the SQL Server Project template in Visual Studio 2012! The template is one of the default VS 2012 templates so it’s already there–no additional downloads. I’ve tested it managing SQL Server 2008 databases and SQL Server 2012 databases and works well with both editions of SQL Server.

Creating a new SQL Server Database Project

To create a new SQL Server Project, select New Project from the menu, then on the dialog, expand Templates, look for SQL Server, and click SQL Server Database Project:

New SQL Server Project

For this exercise and blog post, I use the free downloadable Adventureworks database. Once the project is created, you can right-click the project on the Solution Explorer, click Properties – this will display the Project Settings. By default, “Create script” checkbox is unchecked. I recommend you tick the checkbox so that the SQL files will be generated when you build the project:

Project Properties

If you have created the database and already created tables, views, and stored procedures, you can use the import function. Right-click the project, expand the Import menu, then click Database:

Import DB Menu

The Import Database dialog appears. Select/setup your database connection and adjust settings per your preference. I like my scripts organized by object types (tables, views, etc.), so I confirm that I select Schema\Object Type as my folder structure:

Import DB Dialog

Once you click the “Start” button on the Import Database dialog, the project files will be created. The result of importing Adventureworks database looks like the following. See how the objects are organized by schema and then by object-types? Nice!

After Importing DB

After importing, you’ll probably want to check-in the scripts to your source code repository.

Now, you expect changes to the database are coming. New tables, new columns, updated stored procedures, whatever it is. So how do you proceed updating the database objects? You have 2 options:

  • Update the scripts in Visual Studio 2012 and then apply the changes to the SQL Server databases.
  • Update the SQL Server database and then update the scripts per database changes.

If you prefer updating the scripts first

Go ahead and make changes to the schema by modifying the script source files. In the example below, I rename [HumanResources].[Employee].[BirthDate] to Birth_Date:

Alter Table Employee

Build the project just to make sure there are no syntax errors. If the project builds okay, you can publish the changes to the database. Right-click the project in Solution Explorer, then click “Schema Compare” in the menu:

Schema Compare Menu

The SQL Schema Compare window appears. The project is already selected on the left-side dropdown on the top of the window. On the right-side dropdown, Select Target, and choose the database you want to push the changes to. After selecting the target database on the left-side dropdown, click “Compare” on the toolbar:

Schema Compare Results

The changes will be displayed and highlighted for you. You can review the deltas or changes and if everything is good, click “Update” on the toolbar. You’re updating the database objects per definitions in the scripts.

Go to the database using SQL Server Management Studio just to verify that the changes were published indeed. Now, don’t forget to check-in your scripts to your source-control.

If you prefer modifying the DB objects directly

If you prefer modifying the database schema and objects using SQL Server Management Studio first before updating your scripts, you can do this as well. Modify the DB objects, then just sync up your scripts. In the example below, using SQL Server Management Studio, I modify the Adventureworks ErrorLog table:

Modify DB Table

After making changes directly to the database, go back to VS 2012 and initiate a Schema Compare. Select the target database on the right-side dropdown. Now, you will want to flip this such that the target database is on the left-side dropdown and the SQL Server DB Project is on the right-side dropdown. After you’ve switch the two (by clicking the image button in between the two dropdowns), you can now click the “Compare” button on the toolbar. What you’re effectively doing here is comparing the database to the script and updating the scripts per definitions in the database:

Schema Compare Results - From DB to Scripts

After updating the scripts, don’t forget to check-in to your source-code repository.

Hopefully, I am able to demonstrate how easy it is to use the SQL Server Database Project. Years ago, I remember having to download some “database-diff” tool to analyze differences and mismatches in DB instances. This capability is built-in in SQL Server Database Project via Schema Compare.

If you have additional tips on how to better use/leverage this tool, please feel free to comment!

Posted by: Gabe Hilado in SharePoint,SharePoint Deployment,SQL Server on May 20th, 2011

When you run the SharePoint Products Configuration Wizard for the first time, you get the following error:

Configuration Failed

System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in database ‘master’.

This means that the domain account you specified to connect to the SQL Server does not have permissions to create the SharePoint config database. So go to your SQL Server and add the dbcreator role to your SharePoint Service account.

Add dbcreator Role to SP Account

Please don’t make the SharePoint Service account elevated to domain admin level in AD or SysAdmin in SQL Server! It’s not necessary!

Posted by: Gabe Hilado in SharePoint on June 11th, 2010

Here are some of the other errors that I received while creating my SharePoint 2010 environment in Windows 7.

New-SPConfigurationDatabase: Could not load file or assembly ‘Microsoft.IdentityModel, Version 3.5.0.0. The Power Shell looks like the following when you’re running New-SPConfigurationDatabase to configure your SP database for the first time:

Could not load Microsoft.IdentityModel

Could not load Microsoft.IdentityModel

The fix: Install Microsoft Identity Framework to address this problem.

New-SPConfigurationDatabase: The user does not exist or is not unique. You get to the point in the New-SPConfigurationDatabase command-let that asks you for the Farm Credentials. You didn’t fully qualify the username with the domain or computer name.

New-SPConfigurationDatabase - user does not exist

New-SPConfigurationDatabase - user does not exist

 

To fix this, simply make sure that the user account you are specifying for the Farm Credentials is fully-qualified with the domain-name or, if using local accounts, the computer name. Example: “macbookpro\gabe”.

New-SPConfigurationDatabase :  Unknown error (0×80005000) At line: 1 char:20. + New-SPConfigurationDatabase <<<< + CategoryInfo    : InvalidData

Unknown error when running New-SPConfigurationDatabase

Unknown error when running New-SPConfigurationDatabase

 

This one frustrated the heck out of me. I configured my Macbook Pro already but I was installing SharePoint 2010 on another PC(has Intel Core i7 processor) in my office when I got this message. I thought the second pass-through would be easier. I already know I must follow every-step of the SharePoint 2010 on Windows 7 Development Workstation Guide.  So, how can I possibly still mess it up?? You see, I’d get past the Passphrase question and it would process the command for a while and spit out the error message only at the very end. When I look at my SQL Server, the Config database is created but the “SharePoint Admin Content” database has not been created yet. I manually added the user-account to sysadmin group in SQL. I made the  Windows login Local Admin. I serviced-pack the SQL instance at least twice. Still the InvalidData error message like above. I uninstalled SharePoint 2010 and re-installed it. When I uninstalled the entire SharePoint 2010 (remember, I was aiming for the “Complete” installation option), I reinstalled the second time just using the “Stand-Alone” install. I was thinking, this sucks, having to use SQL Express because I’m going for Stand-Alone install. I finished the Stand-Alone installation. I go ahead and try to configure it and the psconfigui.exe (SP Product Configuration Wizard) and bam—STILL an error! But this time, the error message was more helpful—it told me that IIS 6 is not installed. I look at my installed Windows 7 features and sure enough, IIS 6 wasn’t installed yet! I was pretty sure I ran everything on the checklist and I couldn’t have missed anything. Obviously, I missed something, and this something was the required Windows Features that must be turned on. So I installed II 6 feature on my Windows 7 and the Stand-Alone configuration worked and I saw the Central Admin. If you get this Unknown error (0×80005000), check your installed Windows Features and make sure you have IIS 6 Management Console installed.

IIS 6 Management Consol turned-on

IIS 6 Management Consol turned-on

 

Okay, after going through the stand-alone install and was made to enable IIS6, I uninstalled SharePoint 2010, again! Why did I uninstall it? Because it was installed using Stand-Alone install which uses SQL Server Express.  This final time, I was determined to make the Complete installation (can add servers to farm) work. I installed the SharePoint 2010. I ran Power Shell and ran the New-SPConfigurationDatabase command-let and was able to create the configuration database. Yay!!!

SQL Server after successful New-SPConfigurationDatabase

SQL Server after successful New-SPConfigurationDatabase

Newer Posts »