SQL Server, Configure Database Mail

Description

This post shows how to configure SQL Server Database Mail in SQL Server 2012.

Solution

Open SQL Server Management Studio.  Expand Management.  Right-click on Database Mail and select Configure Database Mail.

Click Next on the Database Mail Configuration Wizard Welcome Screen.

Choose "Set up Database Mail" and click Next.

Database Mail is disabled by default.  When prompted, click "Yes" to enable the Database Mail feature.

Create a new Database Mail Account.

Complete the "New Profile" screen and click Next.

Mark the Profile "Public" and click Next.

Complete the "Configure System Parameters" screen and click Next.

Click Finish to complete the Database Mail Configuration Wizard.

Verify all configurations are successful and click Close.

References

Microsoft TechNet (2008).  Database Mail.  Retrieved December 17, 2013 from http://technet.microsoft.com/en-us/library/ms175887%28v=sql.105%29.aspx

Chart of PMBOK(R) Fifth Edition Knowledge Areas and Process Groups

Description

This chart illustrates the Project Management Knowledge Areas and Process Groups as defined by the Project Management Institute (PMI.org) in the PMBOK(R) Guide Fifth Edition which was published in January, 2013.

(PMI, 2013)


References

Project Management Institute (January, 2013). A Guide to the Project Management Body of Knowledge (PMBOK Guide) Fifth Edition. Newtown Square, PA: Project Management Institute.

PowerShell, New-AdUser Cmdlet for Creating SQL/SharePoint Service Accounts

Description

I think the degree to which you seek to automate various administrative tasks during a SQL Server / SharePoint deployment depends on a variety of things; of which your immediate time and value of reuse-ability in time invested are certainly at the top of the list.  I recently needed to create about ten AD service accounts for a new deployment.  While I could have fully automated the process with a PowerShell script that would read from an Excel file (Microsoft Script Center, 2013), all I really wanted to do was simplify the process of creating AD users, inline, using PowerShell.

Solution

The New-ADUser cmdlet has several parameters.  The example below creates one AD User Account called SvcSampleUser01 with parameters values that make sense for my purposes.  Of course, each environment will differ and so this example may need to be modified accordingly.

New-ADUser –SamAccountName “SvcSampleUser01” -UserPrincipalName "SvcSampleUser01@domain.com” -GivenName “” -Surname “” -Name "SvcSampleUser01" -DisplayName “SvcSampleUser01” -Description "Service Account, SharePoint Farm 01, SharePoint Sample Service Account." -Enabled $true -PasswordNeverExpires $true -CannotChangePassword $true -ChangePasswordAtLogon $false -path “OU=LowOU, OU=MidOU, OU=HighOU, DC=DOMAINNAME, DC=com” -AccountPassword (Read-Host -AsSecureString "Specify Account Password, Enter")

One thing to take note of is the -path parameter.  Most of the examples on the Web only have a single OU listed.  In cases where the target OU is nested, then you list the OU's sequentially from the lowest to the highest as shown in the example.

Also, note that this cmldet will prompt for the password to be typed in.

Using this cmdlet inline in a PowerShell window will be faster than using the AD user interface.  However, this may be slower than using a fully automated approach with an Excel input, depending on how many accounts need to be created.


References

Microsoft Script Center (2013).  PowerShell: Create Active Directory Users Based On Excel Input.  Retrieved December 2, 2013 from http://gallery.technet.microsoft.com/scriptcenter/PowerShell-Create-Active-7e6a3978.

Microsoft TechNet (2013).  Active Directory Cmdlets in Windows PowerShell.  Retrieved December 2, 2013 from http://technet.microsoft.com/en-us/library/ee617195.aspx.

Microsoft TechNet (2013).  New-ADUser.  Retrieved December 2, 2013 from http://technet.microsoft.com/en-us/library/ee617253.aspx.

SQL Server Reporting Services (SSRS) Report Pagination InteractiveSize

Description


When viewing a deployed, SQL Server Reporting Services (SSRS) report, the default pagination does not provide a pleasant user experience because viewing more than a handful of rows of report data requires that the user page right and page left.

This characteristic is a result of the default InteractiveSize Properties of the report.  TechNet describes InteractiveSize Properties as, "InteractiveHeight and InteractiveWidth are used by the HTML rendering extension to provide the equivalent of PageHeight and PageWidth. Because the HTML rendering extension dynamically resizes a report to accommodate drilldown, drillthrough, and show/hide features, the report server uses different properties to support pagination on dynamic pages." (Microsoft TechNet, 2013)

Solution

To display all rows of data on the first page of the SSRS report, set InteractiveSize properties (Width and Height) to 0.

After updating the properties, Save the report, then Deploy the report.

Refresh the report in the browser and now all of the report data will display on the page. If viewing the report in SharePoint using a Report Viewer Web Part, then you may need to adjust the Height and Width of the Report Viewer Web Part to eliminate any scroll bars that appear as a result of the data being displayed on one page.

References

Microsoft TechNet (2013).  Controlling Report Pagination.  Retrieved November 29, 2013 from http://technet.microsoft.com/en-us/library/ms156282%28v=sql.90%29.aspx

SPS NH: Search Driven Design Patterns for SharePoint 2013

Event:
SharePoint Saturday NH
Saturday, September 21, 2013

Session Title:
Search Driven Design Patterns for SharePoint 2013

Abstract:
First, we will discuss SharePoint portal design flaws, related deficiencies, and challenges that exist with SharePoint in the enterprise.  We will discuss the Search platform and redefine what it means to us and how we leverage it.  We will take a tour of a Search Driven portal and review the configuration of Managed Properties, Result Sources, Query Rules, Search Results Web Parts, Display Templates, Search Boxes, and Search Pages.  Next, we will construct Search Driven components using creative techniques and reflect on the benefits of the overall approach that we followed, comparing and contrasting with other design patterns.  Finally, we will take a step back and discuss the future of Search and how to align SharePoint Search with the overarching Enterprise Architecture strategy.

This session provides practical guidance and best practices for SharePoint 2013 and SharePoint 2010 portal design, content management design, and search configuration based on real world lessons learned.  The demonstrations and technical deep dives showcase Search Driven Design Patterns in motion while providing instructions for how to build a Search Driven portal.

Slides:

Search Driven Design Patterns for SharePoint 2013.pdf

Windows Server 2012, Windows 8, Product Key and Activate

Description

The "Change product key" link is not visible in Windows Server 2012 or Windows Server 2008 and the license is not activated.


Solution

Enter a Product Key and Activate Windows.

1)  All Apps, Run, slui.exe 0x3, click OK

2)  Type Product Key, click Activate


Reference

Microsoft Support (2013).  "Change product key" link is not available in Windows 8 or in Windows Server 2012.  Retrieved August 17, 2013 from http://support.microsoft.com/kb/2750773

TechNet (2013).  Slmgr.vbs Options.  Retrieved August 17, 2013 from http://technet.microsoft.com/en-us/library/ff793433.aspx

Error: SQL Server: Unable to Shrink Transaction Log, "Could not locate file for database..."

Description

You attempt to shrink a transaction log file associated with a SQL Server database, but you receive in error:

"Could not locate file 'Transaction Log Name' for database
'Database Name' in sys.database_files. The file either does not exist, or was dropped."



Solution


1)  Run SP_HelpFile to get the correct name of the transaction log file.

USE Database Name
EXEC sp_helpfile

(TechNet, 2013)

This will return the following information about the database files, including the associated transation log(s):  Name, FileId, FileName, FileGroup, Size, MaxSize, Growth, Usage



2)  Run the Transact-SQL statement again, using the correct Transaction Log Name.  You can find this in the Name column of the SP_HelpFile query results set.

USE [Database Name]
 GO
ALTER DATABASE Database Name SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(Transaction Log Name, 1)
ALTER DATABASE Database Name SET RECOVERY FULL WITH NO_WAIT
 GO

(TechNet, 2013)



3)  Verify that there are configured, scheduled, and enabled SQL Server Backup jobs in place for:

a) Full Database Backup
b) Transaction Log Backup *Important for Full Recovery Model*



Reference

TechNet (2013).  Backup Under the Full Recovery Model.  Retrieved August 11, 2013 from http://technet.microsoft.com/en-us/library/ms190217%28v=sql.105%29.aspx

TechNet (2013).  DBCC SHRINKFILE (Transact-SQL).  Retrieved August 11, 2013 from http://technet.microsoft.com/en-us/library/ms189493.aspx

TechNet (2013).  Recovery Models and Transaction Log Management.  Retrieved August 11, 2013 from http://technet.microsoft.com/en-us/library/ms366344%28v=sql.105%29.aspx

TechNet (2013).  sp_helpfile.  Retrieved August 11, 2013 from http://technet.microsoft.com/en-us/library/aa933456%28v=sql.80%29.aspx

Installing .NET framework (Netfx3) on Windows Server 2012 using PowerShell

Description

The purpose of this post is to provide instructions for installing .NET framework (NetFx3) on Windows Server 2012 using PowerShell.

.NET Framework is a requirement for SQL Server 2012.  If this has not been installed prior to a SQL Server 2012 installation, then an error message will surface during the installation process.  It is recommended to run the PowerShell command for installing Netfx3 prior to beginning the SQL Server installation.


Solution

1.  Make the Windows Server 2012 media (DVD) available.  For this example, I mounted a DVD drive on drive letter Z.

2.  Open Windows PowerShell as an Administrator.

3.  Run the following PowerShell command, replacing the source details with those specific to your environment.


PS C:\Windows\system32> dism /online /enable-feature /all /featurename:NetFx3 /source:z:\sources\sxs

Reference

Microsoft TechNet (February, 2012).  DISM - Deployment Image Servicing and Management Technical Reference.  Retrieved July 29, 2013 from http://technet.microsoft.com/en-us/library/hh824821.aspx

Microsoft TechNet (February, 2012).  Use DISM in Windows PowerShell.  Retrieved July 29, 2013 from http://technet.microsoft.com/en-us/library/hh825010.aspx

Verbeeck, K. (April, 2013).  Error while enabling Windows Feature: Netfx3.  Retrieved July 29, 2013 from http://www.sqlservercentral.com/blogs/koen-verbeeck/2013/04/29/error-while-enabling-windows-feature-netfx3

SQL Server 2012: Find SQL Server Collation

Description

How to retrieve collation information for SQL Server 2012.


Solution

During the set up of SQL Server 2012, the default collations are as follows (as shown below):


Database Engine:  SQL_Latin1_General_CP1_CI_AS

Analysis Services:  Latin1_General_CI_AS

To retrieve the collation of the Database Engine for an existing SQL Server instance:

SELECT CONVERT (varchar, SERVERPROPERTY('collation'));

 
To retrieve  the collation of databases attached to the SQL Server instance:

SELECT name, collation_name FROM sys.databases;

(MSDN, 2012)



Reference

MSDN (2012).  View Collation Information.  Retrieved July 18, 2013 from http://msdn.microsoft.com/en-us/library/hh230914.aspx

Issue: Windows Server 2012, JavaScript Blocked on Internet Explorer

Description

On a Windows Server 2012 system, JavaScript is being blocked in Internet Explorer.

Solution

1. Open Server Manager.


2.  Click "Local Server"

3.  In the Properties section, locate "IE Enhanced Security Configuration."  It is currently set to "On."  Click "On" to open a dialog window.

4.  Change the "Internet Explorer Enhanced Security Configuration" settings to Off as needed.  Click OK and close Server Manager.

SPS NYC: Search Driven Design Patterns for SharePoint 2013

Event:
SharePoint Saturday NYC
Saturday, July 27, 2013

Session Title:
Search Driven Design Patterns for SharePoint 2013

Abstract:
First, we will step back and examine some SharePoint related deficiencies, common portal design flaws, and challenges that exist in the enterprise.  Next, we will align ourselves with an overarching Enterprise Architecture strategy and redefine the role of SharePoint Search in the organization.  From there, we will design a SharePoint 2013 portal that is optimized and ready for Search Driven design patterns.  Finally, we will construct Search Driven components using creative techniques and reflect on the benefits of the overall approach that we followed.

Slides:

Search Driven Design Patterns for SharePoint 2013.pdf

Blindly Diagnosing a RAM Issue affecting SharePoint 2010 W3Wp.exe and SQL Server 2005 Enterprise Edition

I received limited information about an issue.  Here are some ideas.

Description

A SharePoint Server 2010 Farm with a SQL Server 2005 Enterprise Edition database server has RAM issues which are affecting affecting both the Web front end (w3wp.exe) and SQL Server.

Would be Helpful to Know

Is it 32-bit or 64-bit? How much physical RAM is assigned to each server role? What version and patch level is the OS?  What version and patch level is SQL Server? How is SharePoint being used?  How many users?  Are there custom Web parts? Custom Web pages? What are the SQL Server performance counters reading?  When does a memory spike occur?  How long does it last?  When did it start?  What recently changed in the environment?  Are the systems physical or virtual?  What is the server farm topology?

Possible Causes

Need a Service Pack/Patch for OS:  self-explanatory, but need the current information to know if this is a possibility.

Antivirus:  Investigate antivirus software running on the servers.  IIS Forums reported that McAfee Enterprise V8  had a buffer overflow protection feature that was causing problems with w3wp.exe (IIS Forums, 2008).

Incorrectly disposed objects:  Unmanaged memory can lead to memory problems (MSDN, 2010).

Scheduled jobs:  SQL Server maintenance plans, SQL agent jobs (e.g. backups), SharePoint full or incremental crawl schedules can create noticeable degradation while running.

On the SQL Server side of things:  Resource bottlenecks, TempDB bottlenecks, or a slow running user query (less likely in this situation) (MSDN, 2010).  Query the sys.dm_os_memory_clerks and sys.dm_exec_query_memory_grants DMVs to get an idea how SQL Server is utilizing memory.

Reference

IIS Forums(July, 2008).  W3wp.exe memory usage is out of control.  Retrieved March 10, 2013 from http://forums.iis.net/t/1150494.aspx

Microsoft TechNet (2005).  Troubleshooting Performance Problems in SQL Server 2005.  Retrieved March 10, 2013 from http://technet.microsoft.com/library/Cc96654

MSDN (2010).  Disposing Objects.  Retrieved March 10, 2013 from http://msdn.microsoft.com/en-us/library/ee557362.aspx 

MSDN (2010).  SharePoint Dispose Checker Tool.  Retrieved March 10, 2013 from http://archive.msdn.microsoft.com/SPDisposeCheck

SPS Boston: Search Driven Design Patterns for SharePoint 2013

Event:
SharePoint Saturday Boston
Saturday, April 27, 2013

Session Title:
Search Driven Design Patterns for SharePoint 2013

Abstract:
First, we will step back and examine some SharePoint related deficiencies, common portal design flaws, and challenges that exist in the enterprise.  Next, we will align ourselves with an overarching Enterprise Architecture strategy and redefine the role of SharePoint Search in the organization.  From there, we will design a SharePoint 2013 portal that is optimized and ready for Search Driven design patterns.  Finally, we will construct Search Driven components using creative techniques and reflect on the benefits of the overall approach that we followed.

Slides:

Search Driven Design Patterns for SharePoint 2013.pdf