Category Archives: SQL

How to execute a stored procedure within C# program – with Parameter, using Loop

csharp

Here is a sample :

using (SqlConnection conn = new SqlConnection(ConnectionString) {
    conn.Open();

    // 1.  create a command object identifying the stored procedure
    SqlCommand cmd  = new SqlCommand("CustOrder", conn);

    // 2. set the command object so it knows to execute a stored procedure
    cmd.CommandType = CommandType.StoredProcedure;

    // 3. add parameter to command, which will be passed to the stored procedure
    cmd.Parameters.Add(new SqlParameter("@CusID", custId));

    // execute the command
    using (SqlDataReader rdr = cmd.ExecuteReader()) {
        // iterate through results, printing each to console
        while (rdr.Read())
        {
            Console.WriteLine("Product: {0,-35} Total: {1,2}",rdr["ProductName"],rdr["Total"]);
        }
    }
}

Workaround: SharePoint 2013 Preview prerequisite installer doesn’t install the Microsoft SQL Server 2008 R2 SP1 Native Client

SharePoint-2013

If you install SharePoint 2013 Preview by running the prerequisite installer (Microsoft SharePoint Products Preparation Tool) and you see this line in the log file:

Error: The tool was unable to install Microsoft SQL Server 2008 R2 SP1 Native Client.

Use these steps to work around the problem:

  • Go to http://go.microsoft.com/fwlink/p/?LinkId=262370 and manually download and install the Microsoft SQL Server 2008 R2 SP1 Native Client (1033\x64\sqlncli.msi).

    Important! Do not download and use 1033\IA64\sqlncli.msi or 1033\x86\sqlncli.msi. These versions are not supported for this preview release.

  • Run the Microsoft SharePoint Products Preparation Tool again.

Starting/Running SQL SERVER 2012 (SQL Permissions) for SharePoint 2013

SQL Server

You need to give the SharePoint Account that you plan to install and configure SharePoint with some elevated rights in SQL. Typically we recommend you use a dedicated account [Domain]\[SharePoint Admin] and this account will need the following roles on the SQL Server.

  • DB_Creator
  • Security_Admin
  • Public

From your newly installed SQL Server click Start > All Programs > Microsoft SQL Server 2012 > SQL Server Management 2012.

Microsoft SQL SERVER 2012 Start 1

After waiting this loading user settings screen will appear:

Microsoft SQL SERVER 2012 Start 2

Wait for some time then the following screen appears:

Microsoft SQL SERVER 2012 Start 3

Login as [Domain]\[SharePoint Admin] by selecting “Windows Authentication”:

Microsoft SQL SERVER 2012 Start 4

Microsoft SQL SERVER 2012 Start 5

From Object Explorer expand-out Security.

Under Security expand out Logins.

In case you don’t have the User

  • Right click on Logins.
  • From the menu choose New Login.
  • For Login name: enter [Domain]\[SharePoint Admin]  or whatever account you will be logged into SharePoint as when you do the install.

Microsoft SQL SERVER 2012 Start 6

After right click, select Properties. The following screen will come:

Microsoft SQL SERVER 2012 Start 7

On the left, under select a page click Server Roles.

Check the box for dbcreator and securityadmin. Also, leave public selected.

Microsoft SQL SERVER 2012 Start 8

Click OK. Now your [Domain]\[SharePoint Admin] is ready to install sharepoint on the server.

Max Degree of Parallelism for SQL SERVER 2012 (before installing Sharepoint 2013)

SQL Server

Now that you have SQL Server all installed there is one more configuration change you need to do in order to make SharePoint happy. You need to change the max degree of parallelism. Don’t ask me what that is. Something about number of processors and how SQL uses them. Unfortunately SQL Server defaults to 0 and SharePoint 2013 necessitates, demands, forces, requires, and otherwise really wants you to set it to 1. So make the change. If you are running SharePoint 2010 this change is not required but is recommended.

Now if you look at the link I gave you saw a bunch of fancy SQL to change it. Barf! Let’s change it the easy way, with a mouse.

From your newly installed SQL Server click Start > All Programs > Microsoft SQL Server 2012 > SQL Server Management 2012.

On the Connect to Server screen click Connect. If for some reason the Server name: field is blank you would just type in the name of the server.

Max Degree of Parallelism 1

At the top of the Object Explorer window you see your server. Right click on it.

Max Degree of Parallelism 2

From the menu that appears click Properties.

Max Degree of Parallelism 3

In the Select a page section click Advanced.

Max Degree of Parallelism 4

Scroll to the bottom and change Max Degree of Parallelism from 0 to 1.

Max Degree of Parallelism 5

Click OK.

That does it. No need to reboot or anything else to make the change take effect.

Installing SQL SERVER 2012 (for SharePoint 2013)

SQL Server

To install SQL SERVER 2012 we are using the following Setup file:

#####_SQLServer2012EnterpriseEditionwithSP1_ServerCAL(With_#########)_x64

Right Click and Mount, Run “setup” Application file as Administrator:

Note: We are logged in as [SharePoint Admin] for all installations.

This Screen will Show up:

Microsoft SQL SERVER 2012 Installation 1

Click “Installation” on the left side, the following screen will come:

Microsoft SQL SERVER 2012 Installation 2

Click “New SQL Server stand-alone installation or add features to an existing installation”, the following screen will come:

Microsoft SQL SERVER 2012 Installation 3

Click “Show Details” to view details, Click “OK” after verifying the setup support rules. In case there is a problem resolve it and Re-run the Setup. Now the following screen appears:

Microsoft SQL SERVER 2012 Installation 4

Wait for some time, the following screen will come:

Microsoft SQL SERVER 2012 Installation 5

In the product key section, Select “Enter the Product Key” which should be 25 character key. Click next, the following screen will come:

Microsoft SQL SERVER 2012 Installation 6

In the license Terms section, Select “I accept the license terms” and Select Send feature usage data to Microsoft….. Click next, the following screen will come:

Microsoft SQL SERVER 2012 Installation 7

Click Next to “Include SQL Server product updates”:

Microsoft SQL SERVER 2012 Installation 8

After the above screen, a prompt as shown below will appear for restarting the system:

Microsoft SQL SERVER 2012 Installation 9

After restart run the setup again and reach to the below screen:

Microsoft SQL SERVER 2012 Installation 10

When the Setup Support Rules screen pops up review any errors or warnings you get. If nothing bad has happened the Next button will be available to click. Click Next to view the following screen:

Microsoft SQL SERVER 2012 Installation 11

For Setup Role select SQL Server Feature Installation and click next.

Microsoft SQL SERVER 2012 Installation 12

On the Feature Selection screen this is where you need to be smart. In order to make SharePoint run you only need to select one check box, Database Engine Services. I would highly recommend you also check Management Tools – Basic and Management Tools – Complete. Click Next to view the following screen:

Microsoft SQL SERVER 2012 Installation 13

Wait for some time, the following screen will come:

Microsoft SQL SERVER 2012 Installation 14

Now when you read all of these awesome features you might be thinking “I want to kick the tires of Reporting Services – SharePoint” or some other random feature. That is great, tire kicking is fun and important but if you are reading the blog post to get SQL Server installed correctly for SharePoint then you probably aren’t ready to start randomly installing features. Even if you were ready to install them you would still most likely come back and do them after SharePoint was up and running, not before. So let’s ignore them for now and click next.

On the Installation Rules screen SQL will make some checks. In this case my Server did not have the Microsoft .NET Features installed. You will need to manually add the feature now. While you do go ahead and leave this SQL window open.

To add the .NET Windows Server feature click on Start > All Programs > Administrative Tools > Server Manager.

From the right side of the screen click on Features.

Over on the left side of the screen click on Add Features.

Check the box for .NET Framework 3.5.1 Features.

When you check the box a window for Add Features Wizard will appear telling you the additional required roles. Click Add Required Role Services.

Click Next.

At the Web Server (IIS) screen click Next.

Accept all of the defaults and click Next.

At the Confirm Installation Selections click Install.

At the Installation Results screen make sure everything was successful and then click Close.

Jump back over to your SQL Installation Rules screen and click the Re-run button.

If the tests are Passed click Next to continue.

After Installation Rules the Following screen appears:

Microsoft SQL SERVER 2012 Installation 15

Assuming this is the only install of SQL Server on this server then you are going to want to take all of the default settings for the Instance Configuration screen, but in my Case I have updated the name to be my server, Click Next.

Microsoft SQL SERVER 2012 Installation 16

Wait for some time, the following screen will come:

Microsoft SQL SERVER 2012 Installation 17

At the Disk Space Requirements screen click next.

Microsoft SQL SERVER 2012 Installation 18

For the Server Configuration screen it is asking you what accounts you want to run SQL Server as. The only service you are worried about right now is the SQL Server Database Engine. This service should always be run as a domain account not a local account. Next to SQL Server Database engine click on MANAMA1\spadmin and a drop down arrow will appear.

Click <<Browse…>>.

Select your SQL account you want to use and click OK.

Enter the account password and keep the startup type as Automatic click next.

Microsoft SQL SERVER 2012 Installation 19

Wait for some time, the following screen will come:

Microsoft SQL SERVER 2012 Installation 20

On the Database Engine Configuration screen there are lots of changes you could make and over time you will learn about these options but for the purpose this guide you will except all of the defaults. You only need to click Add Current User and it is shown as.

Microsoft SQL SERVER 2012 Installation 21

If you were building a production capable SQL Server best practice 101 would be to store your data and log files on different volumes. By default SQL Server will store everything on the C: drive. If you want change that behavior take a gander at the Data Directories tab.

Once you are ready click next the following screen appears:

Microsoft SQL SERVER 2012 Installation 22For the Error Reporting screen select Send Windows and SQL Server Error Reports…Click next the following screen appears:

Microsoft SQL SERVER 2012 Installation 23

At the Installation Configuration Rules screen click next.

Microsoft SQL SERVER 2012 Installation 24

You are ready to Install, so click Install.

Microsoft SQL SERVER 2012 Installation 25

Wait for some time, the following screen will come:

Microsoft SQL SERVER 2012 Installation 27 After Restart

When the installation finishes you may be prompted to restart. This is not directly because of SQL Server but instead because of other recent installs you have done that you haven’t rebooted since. In my case because I added the .NET feature. When SQL is all done click close. If you have any other open windows at this point you can close them as well. You are all done.

If you got the pop up screen to do a reboot it will not automatically happen, you will need to reboot on your own.

Microsoft SQL SERVER 2012 Installation 27 After Restart

Note: The Above error is due to Installation of .Net Framework 3.5, after resolving this when you run the setup again you receive the following screen:

Microsoft SQL SERVER 2012 Installation After Installing NetFx3

Now the SQL SERVER 2012 is successfully installed.

Now do what is mention in this post: Max Degree of Parallelism for SQL SERVER 2012 (before installing Sharepoint 2013)

Now do what is mention in this post: Starting/Running SQL SERVER 2012 (SQL Permissions) for SharePoint 2013

Now you have your whole SQL Server ready, so you can install SharePoint 2013.

Determine your SQL Server Version, Service Pack, and Edition

SQL Server

To determine the version of SQL Server, you can use any of the following methods:

Method 1

Connect to the server by using Object Explorer in SQL Server Management Studio. After Object Explorer is connected, it will show the version information in parentheses, together with the user name that is used to connect to the specific instance of SQL Server.

Method 2

Connect to the instance of SQL Server, and then run the following query:

Select @@version

An example of the output of this query is as follows:

Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (X64)   Mar 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Method 3

Connect to the instance of SQL Server, and then run the following query:

SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

Note This query works with any instance of SQL Server 2000 or later

The following results are returned:

  • The product version (for example, 10.0.1600.22)
  • The product level (for example, RTM)
  • The edition (for example, Enterprise)

For example, the results resemble the following:

10.0.1600.22 RTM Enterprise Edition

Note The SERVERPROPERTY function returns individual properties that relate to the version information, although the @@VERSION function combines the output into one string. If your application requires individual property strings, you can use the SERVERPROPERTY function to return them instead of parsing the @@VERSION results.

VERSION INFORMATION

SQL Server 2012 version information

The following table lists the major releases of SQL Server 2012:

Release Product Version
SQL Server 2012 Service Pack 1 11.00.3000.00
SQL Server 2012 RTM 11.00.2100.60

SQL Server 2008 R2 version information

The following table lists the major releases of SQL Server 2008 R2:

Release Product version
SQL Server 2008 R2 Service Pack 2 10.50.4000.0
SQL Server 2008 R2 Service Pack 1 10.50.2500.0
SQL Server 2008 R2 RTM 10.50.1600.1

SQL Server 2008 version information

The following table lists the major releases of SQL Server 2008:

Release Product version
SQL Server 2008 Service Pack 3 10.00.5500.00
SQL Server 2008 Service Pack 2 10.00.4000.00
SQL Server 2008 Service Pack 1 10.00.2531.00
SQL Server 2008 RTM 10.00.1600.22

SQL Server 2005 version information

The following table lists the major releases of SQL Server 2005:

Release Product version
SQL Server 2005 Service Pack 4 9.00.5000.00
SQL Server 2005 Service Pack 3 9.00.4035
SQL Server 2005 Service Pack 2 9.00.3042
SQL Server 2005 Service Pack 1 9.00.2047
SQL Server 2005 RTM 9.00.1399

SQL Server 2000 version information

The following table lists version number of the Sqlservr.exe file:

Release Product version
SQL Server 2000 Service Pack 4 8.00.2039
SQL Server 2000 Service Pack 3 8.00.760
SQL Server 2000 Service Pack 3 8.00.760
SQL Server 2000 Service Pack 2 8.00.534
SQL Server 2000 Service Pack 1 8.00.384
SQL Server 2000 RTM 8.00.194

For more information, please refer to Microsoft KB321185 – How To Identify your SQL Server Service Pack Version and Edition.

Ref : lumension

New Best Practices for SharePoint 2013 Farm Design – Streamlined Topology

Microsoft some time back released “Streamlined Topology for SharePoint 2013”, new way to build & configure SharePoint 2013 farm. It’s really nice to see official documentation on new approach which I had first heard at SPC12 during SPC119 “Designing Your SharePoint Server 2013 Enterprise Deployment” Session. In that session Luca Bandinelli delivered prescriptive guidance to build SharePoint 2013 On-Premises farm similar to SharePoint Online based on Microsoft’s lessons learned and best practices while maintaining and building their own SharePoint online data centers.

As far as Physical Topology, We have three tiered approach since MOSS 2007 days. In MOSS 2007, we had Web Tier, Application Tier (Central Admin, Shared Service Providers – Search, Excel, Profile Import), and Database Tier. In SharePoint 2010, there wasn’t much changed and we had almost same 3-tier topology (except Application Tier dedicated for Service Applications instead of SSP) but dedicated servers can be added in application tiers for high preformat service applications like Search or PerformancePoint etc.

With SharePoint 2013, we had lot more service applications and many of these service applications can be grouped in similar groups either based on their CPU and RAM needs or either based on their latency, throughput, or workloads/resource utilization to optimize system resources and maximize performance for users. Even though we can get away with traditional 3-tier topology approach in SharePoint 2013, there are some new services may require additional tier and dedicated attention on Application tier. All the windows & WCF services can be divided into – very low, low, and high tolerant latency and this may require us dividing up application tier in multiple tiers for each type of latency tolerant service applications.

As shown in the diagram below, Microsoft provides us alternative farm design topology by redefining traditional web and application tier into multiple tiers.sp2013-traditional-to-streamlined-model1

sp2013-server-roles

Traditional webtier is redefined as Caching and Request Processing tier which would group similar web front end servers forend user request processing along with new service applications like Request Management and Distributed Cache which would require very low latency but very high throughout. Request Management is disabled by default and Distributed Cache is enabled by default. Since Request Manager is CPU intensive and Distributed Cache is memory intensive, both of these services can share same server without any major performance hit.

  • Traditional Application tier is divided into two optimized tiers – Front End Servers and Batch Processing Servers.
    • Front-End Servers would group similar service applications which would serve user requests with low latency, low resource utilization, and optimized for faster performance and response time. Services like Central Administration, Managed Metadata, User Profile, App Management, Search Query Role, and Business Data Connectivity are ideal for Front End Servers.
    • Batch Processing Servers would group similar service applications which would typically require long running back ground processes, high latency, and high resource utilization, and optimized for higher workload by maximizing system resources. Services like User Profile Sync, Work Management, Search Crawl and Index Role, Workflow, Machine Translation etc. are ideal for Batch Processing Servers. For large scale farms, Batch processing tier can be divided further into specialized load servers for services like Search, PerformancePoint, or Excel Services which can cause high spikes in performance during peak time.
    • Database tier stays same in both traditional and streamlined model. These servers can be either clustered, mirrored, or configured with Always On.

 

Ok, So, What’s your take on this new Model..

Having said that, my take on this new approach is what I used to say while designing SharePoint 2010 topologies. Even though you would ideally love to plan for 4-5 tier topology, it may not be possible in real world due to possible hardware funding issues. You are looking at nearly 10 high performing virtual machines or physical hardware, which may be daunting to get through budget approval  process.

Depending on your situation, number of users, and size of farm, you may get away with running traditional three tiered approach as long as they have enough hardware resources like RAM and CPU allocated. With the traditional 3-tier approach, you can run Distributed Cache and Request Management on Web Servers, Central Admin and all the Service applications in Application tier as initial farm design and plan to scale out or add more dedicated servers for specific workloads like Search as needed.

Resources