How to create maintenance jobs for SQL Server instances running on SQL Server Express Edition?

SQL Server Expression edition has the best pricing model of all editions of SQL Server- it is free! But, there are catches. It has the limitations of lack of the SQL Server Agent service, 10GB database size limit, and 1GB max memory. To see a full list of features supported, you can visit http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx.

While it’s rare to see SQL Server Express Edition installation on enterprise production systems, they do happen. As a DBA, you will need to ensure the server maintenance jobs are accounted for and enabled for the SQL Server Express instances. But, without SQL Server Agent, how can you accomplish such task? Have no fear, my friends. It is not Mission Impossible.


Here’s an overview:

  1. Create the required stored procedures.
  2. Test your SQLCMD commands that you wish to execute and then save it as a batch file.
  3. Schedule the batch file to run using Task Scheduler.

Implementation steps:

  1. In this example, we will test cycling the SQL Server Error Log using SQLCMD.  Open the Command Prompt and then test running the following command:
    sqlcmd -S <i>SERVERNAME</i> -Q "EXEC master.sys.sp_cycle_errorlog"

  2. If step 1 is successful, copy and paste the command into a text file and then save it as a batch file. Eg.: CyclingSQLErrorLog.bat.

  3. Open Task Scheduler (Windows -> Administrative Tools -> Task Scheduler).

    Task Scheduler


  4. On the Actions Pane and under Task Scheduler, click “Create Basic Task…”
    Create Basic Task

  5. Name the task and then click on the “Next” button:
    Name the Task

  6. On the “Trigger” section, set the schedule and then click the “Next” button to continue:
    Set the Schedule

  7. On the “Actions” section, select the default option “Start a program” and then click the “Next” button to continue:
    Action

  8. Click on the “Browse” button and then navigate to the location where you had saved the batch file. Select the batch file and then click the button “Open” to select it. Click the “Next” button to continue.
    Browse to select the batch file

  9. On the Finish section, review the options you had selected and click the “Finish” button to complete.

  10. On the Active Tasks section, you will find the new task you had just created. Double-click to open it.
    Active Tasks

  11. On the Action Pane located on the left hand side, click on the “Properties” link:
    Properties
  12. On the task scheduler properties dialog box and on the “General” tab, click on the “Change User or Group…” button to specify the dedicated domain user with permission to run the scheduled task:
    Change Users or Group
  13. You can test running the task by right-click it and select “Run”:
    Run the scheduled task

Note:

  1. In the above example, we have only tested running the simple task to cycle the SQL Server Error Log. The same concept can apply to database backup, index maintenance, and update statistics maintenance jobs.

  2. To view all the options for SQLCMD, go to http://msdn.microsoft.com/en-us/library/ms162773.aspx.


REFERENCES:
Features Supported by the Editions of SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx

How to schedule and automate backups of SQL Server databases in SQL Server Express
http://support.microsoft.com/kb/2019698

Schedule a Task
http://technet.microsoft.com/en-us/library/cc748993.aspx

How To Upload and Download Files Using S3?

What is AWS S3?
Amazon S3: Simple Storage Service. Notice the 3Ses. It is an online file storage web service offered by Amazon Web Services (AWS).

Uploading and downloading files using S3 is remarkably easy. In this blog, we will explore how to upload a database backup file and then download it.

Uploading a file:
1. Logon to your AWS console and then click on S3:
S3

2. Create a new bucket to storage the file(s) to be uploaded:
Create Bucket

3. Name the bucket. No underscores”_”. It has a number of limitations. The best part of it is that it will tell you instantly if you violated the naming standards and unless you correct them, you will not be able to go to the next step.
Name the Bucket

4. If the bucket creation is successful, you will see the bucket in the “All Buckets” list:
Bucket Created

5. To upload a file to the designated bucket, click on the bucket name then click on the “Action” drop-down button, and then click on the “Upload” option:
Upload

6. Click on the “Add files” button:
Add Files

7. Browse to the file you wish to upload and then select it to open:
Select File to Upload

8. Click on the “Start Upload” button to upload:
Start Upload
9. You will see the file upload transfer in progress:
Upload in Progress

10. Once the upload is completed successfully, you will see the green check mark next to the “Done” button:
Upload Done

Downloading a file:
1. Browse to the corresponding bucket and select the file you wish to download:
Download

2. Click on the “Download” button on the following prompt:
Download Link

3. Download will start to download the file select to your specified location:
Download in Progress

Taking and Restoring a Snapshot for Migrations in the same AWS Account

You have created a Development SQL Server RDS instance and migrated all the required databases, logins, and jobs from another SQL Server instance. The hard work and heavy lifting are now done. The Development Team and the QA Team have tested, verified, and approved to create the Test SQL Server RDS with the exact same specifications as the Development SQL Server RDS instance. They opened the ticket for you on a Friday afternoon at 3:00PM and wish to have the Test SQL Server RDS instance ready by Monday morning at 9:00AM. They have also carbon copied everyone under the sky that it is a high priority item. You need to catch a 7:45PM flight to head out to the Midwest for a gathering. You need to be in the airport by 6:00PM. What do you do? Stopped getting frustrated for a second and head down to the nearest Starbucks and get yourself a cup of Mocha Frappuccino with no cream.  Once you are back to your office, take a snapshot of the Development SQL Server RDS instance and then restore it to create the Test SQL Server RDS instance. Yes. It is really that simple with a few caveats.

Assumptions:

  1. The Development and Test SQL Server RDS instance are all located on the same AWS account.
  2. The Mocha Frappuccino with no cream has helped to calm you down and you are focused on creating the Test SQL Server RDS instance rather than sending out angry emails.

Implementation Steps:

  1. Logon to the AWS Console, click on RDS and then click on Instances.  Select the Development SQL Server RDS instance. Click on the “Instance Actions” drop-down arrow and then click on “Take DB Snapshot”:
  2. Taking Snapshot

  3. Name the snapshot accordingly. Eg.: DBInstanceName-SS-08242014.
  4. Back on the RDS Dashboard, you will see the snapshot in creating progress.
  5. Creating Snapshot

  6. Once the snapshot creation is completed, select the snapshot and then click on the “Restore Snapshot” button.  Note: The duration of the snapshot creation will have a dependency on the size of the databases in the instance.
  7. Restoring from a Snapshot

  8. On the “Restore DB Instance” page, make sure you name the Test SQL Server RDS instance accordingly by keying in the “DB Instance Identifier” in addition to make sure all the “Network & Security” settings are correct as per requirement. Click on the “Launch DB Instance” to create the Test SQL Server RDS instance by cloning the Development SQL Server RDS instance. Yes. Everything in the Development SQL Server RDS instance will be copied over to the Test SQL Server RDS instance.
  9. Name the DB Instance Identifier

  10. Once the Test SQL Server instance is provisioned, on the RDS Dashboard click on “Instances” and then copy the endpoint.
  11. Locating the Endpoint

  12. Open SQL Server Management Studio and paste the endpoint in the “Server name:” textbox and make sure to replace the “:” with “,” in front of the port number. Key in the master user and password you had created for the Development SQL Server instance.
  13. Connecting to the RDS instance

  14. Once connected, browse to the Security node, select the Master User and rename it accordingly as per your internal naming standards for the Test environment and change the password. Warning: Even after you had renamed the master user in the SQL Server RDS instance, if you review it on the RDS Dashboard  it will remain and show the master user the same prior being renamed in the instance level.  You will be logging on using the master user name you had renamed rather than the one showing on the RDS Dashboard for the Test SQL Server RDS instance.

SQL Server RDS Instance Data Migration Between Separate AWS Accounts

Pop Quiz Again Hot Shot! You work for or consult for a company that has multiple development teams. Each team reports to their corresponding department heads. The organizational hierarchy is almost as complicated and just as political as the US government. Multiple AWS accounts have been opened and being used by multiple teams. You have been assigned with the critical tasks to migrate data from one SQL Server RDS instance in the AWS account belonging to Billy’s team to another SQL Server RDS instance in the AWS account belonging to Bob’s team. You need to get the migration completed fast before someone sets up a political storm and send it your way!

Warning: Don’t seek advice from the self-proclaimed AWS or “cloud” experts! They may point you to the misconception that you can take snapshot from on AWS account and restore it to another. AWS is still a relatively new technology. Similar many others whom are exploring AWS, I belonging to the novice group when it comes to AWS or “cloud.” But, I can share with you that as of today (August 23, 2014) and as per AWS support, you can’t take a snapshot from one AWS account and restore it to another.  Again, without sounding like a broken record, this is time sensitive information – this is a fact as of August 23, 2014. Please share with me once you figured out an alternative or once the feature for snapshot migration between different AWS accounts are available.

Assumptions:

  1. The SQL Server RDS instance belonging to Billy’s team (source) and the SQL Server RDS instance belonging to Bob’s team (destination) are located in separate AWS accounts.
  2. You are using BCP for the data transfer as you do not have the luxury of using a third party tool for the data migration.
  3. In the following example steps, for simplicity’s sake, we will only explore how to migrate the logins, database, and data.

Steps to BCP data out of the SQL Server RDS Instance (source) belonging to Billy’s team:

  1. Create a dedicated folder for the migration purpose. Eg.: RDSMigration.
  2. Create the corresponding folders in the dedicated folder for migration, RDSMigration, for each of the databases to be migrated over to the destination instance.  They will store the scripts and files for migration for each of the databases.
  3. Logon to source SQL Server RDS instance.
  4. Script out the logins. Save it to the RDSMigration folder.  Name the script accordingly. Eg.: SourceLogins.sql.
  5. For each of the databases to be migrated over to the Production RDS instance, generate the scripts for database objects and save it to the corresponding database name folder you had created in step 2.
  6. Connect to the source SQL Server RDS instance, right-click the database > Tasks > Generate Scripts…:
    Generate Scripts

    The “Introduction” screen for “Generate and Publish Scripts” will greet you. Click the “Next” button:
    Generate scripts for database objects

    On the “Choose Objects” page, make sure the default option “Script entire database and all database objects” is selected and then click on the “Next” button to continue:
    Script entire database and all database objects

    On the “Set Scripting Options” page, specify the location to save the script:
    Specify the folder to save the script

    On the “Save or Publish Scripts” screen, make sure you see all green marks. Otherwise, you will need to review and resolve the errors and retry. Click on the “Finish” button to wrap things up as we are seeing all green here.
    Generate and Publish Scripts - Confirmation Dialog Box

  7. To BCP each of the user tables data out of the source SQL Server RDS instance database to be migrated over to the destination SQL Server RDS instance, run the following TSQL on the database to generate the BCP command to be executed on the command prompt:
  8. select 'bcp ' + db_name() +'.'+ SCHEMA_NAME(schema_id) +'.[' + name + '] out "C:\RDSMigration\DatabaseName\' + name + '.dat" -E -n -S Billy.c2q1zlj4vmlz.us-east-1.rds.amazonaws.com,1433 -Umasteruser -PPassword -b 10000'
    from sys.tables where type = 'U'
    order by name

    Replace the output folder name, RDS instance name, master user, and password with the correct values.

  9. Copy the output generated on step 6 and paste into a text file. Make sure the SELECT clauses with all the required columns are in one line without wrapping to the next line. Else, error will be thrown when executed in the command prompt.
  10. Open the command prompt and execute the BCP commands from step 7. You will need to press enter for the last BCP command to execute it.
  11. Check the output folder to make sure the corresponding .dat files for each of the user tables are generated.

Steps to BCP data into Bob’s SQL Server RDS Instance database (destination):
1. Logon to the destination SQL Server RDS instance.

2. Run the login script, SourceLogins.sql, you had generated to create the logins on the destination SQL Server RDS instance.

3. Run the database and objects script you had generated from the Source SQL Server RDS instance to create the database and objects.

4. In SSMS, expand the database node, expand the database you had just created, expand the Tables nodes and make sure all the user tables exist as in the database in the source SQL Server RDS instance.

5. To BCP each of the user tables into the designation database, run the following TSQL on the database to generate the BCP command to be executed on the command prompt:

select 'bcp ' + db_name() +'.'+ SCHEMA_NAME(schema_id) +'.' +name + ' in "C:\RDSMigration\DatabaseName\' + name + '.dat" -E -n -S ProdInstance.c2q1zlj4vmlz.us-east-1.rds.amazonaws.com,1433 -Umasteruser -PPassword -b 10000' 
from sys.tables where type = 'U'
order by name

Replace the input folder name, RDS instance name, master user, and password with the correct values.

6. Copy the output generated on step 5 and paste into a text file. Make sure the SELECT clauses with all the required columns are in one line without wrapping to the next line. Else, error will be thrown when executed in the command prompt.

7. Open the command prompt and execute the BCP commands from step 6. You will need to press enter for the last BCP command to execute it.

8. Query the user tables to make sure the same data exist as in the source database. You can also write a script to do the row count and compare between them.

How to Create a New SQL Server RDS Instance?

I will take a stand here. Due to its limitations, as documented in here by AWS, SQL Server RDS is not my favorite. I have always advised whomever willing to listen to use AWS EC2 instances instead. What if there are good reasons due to budget or if the decision to use SQL Server RDS has been made for you by whomever cutting the checks? What do you do? Don’t sweat it. It’s not Rocket Science.

Pre-requisites and some caveats:

  1. Determine the edition that’s required. This is an easy one as opposed to the menus of the fancy restaurants. There are only two on the menu: SQL Server Standard Edition or SQL Server Enterprise Edition.   You will need to “bring-your-own-license”.
  2. Make a decision if the new SQL Server RDS instance will be in Production or Non-Production.
  3. Know the requirements of the new SQL Server RDS instance: CPU, RAM, and Storage Space.
  4. Determine the database instance identifier, master user, and the master user password. You can use the password generator to create a random password if you don’t feel like being creative at the moment: https://identitysafe.norton.com/password-generator.
  5. Know the inbound rules. You will need to configure it after the RDS instance is created. You will need to check with the application support and the network or system admin for the configuration of the inbound rules.
  6. Here’s a critical “gotcha”: If you are working on a migration of the development, test, staging, and production environments – don’t over prepare by creating all these instances ahead of time. The recommended approach is to create the development environment, migrate the data and all the associated objects over to the new RDS instance, once the development environment is validated and verified and in addition to all the stakeholders signed off, then take a snapshot of the development environment and then restore the snapshot to create a new RDS instance. Use this approach for all the environments after development with the development environment being the initial RDS instance.  You must remember and be aware that you cannot restore from a DB snapshot to an existing DB instance! A new DB instance is created when you restore from a snapshot.
  7. Here’s another critical “gotcha”: As per AWS support, you can’t copy a snapshot from one AWS account to another. This means that if you have an account for Production and another account for Development and you wish to create the Production RDS instance by taking a snapshot of the Development RDS instance, this can’t be done as of today (August 21, 2014). You must use BCP to BCP the data out of the Development instance and then BCP the data into the Production instance. If you have a third party tool on for the RDS instances data migration, use it instead of BCP. I will post a blog on how to use BCP in a later time.

Let’s get to it. Here’s how to create a new SQL Server RDS instance step by step:

  1. Logon to your AWS console, under the “Database” section, click on the “RDS” link:
  2. RDS

  3. You will be redirected to the “RDS Dashboard.” Click on the “Launch a DB Instance” button:
  4. Launch a DB Instance

  5. You will then be presented with the option to select the database engine. In this case, we are selecting “sqlserver-ee”:
  6. Selecting DB Engine

  7. On the next screen, you will need to make the decision on the purpose of the instance: Production or Non-Production? In this case, we are choosing the first option and then click on the “Next” button:
  8. Specifying DB Environment

  9. On the next step, you will need to specify the SQL Server RDS instance specifications and settings. You will need to select the License Model, DB Engine Version, DB Instance Class, Mutli-AZ Deployment, Storage, and if Provisioned IOPS is required. In addition, you will need to specify the DB Instance Identifier, Master Username, Master Password. Then, click the “Next” button to continue:
  10. Specifying DB Details

  11. On the next screen, you will see the instance is being created:
  12. Status of Creating

  13. After the instance is created. You will need to change the inbound rules. Click on the drop-down button next to the instance name and then select the “Modify” option. Click the “Security Group Name”:
  14. Click on the Security Groups to Modify the Inbound Rules

  15. On the “Security Group” section, click the “Inbound” tab and then click on the “Edit” button to add the required inbound rules. Note: you will need to check with your network admin on the required inbound rules.
  16. Configuring Inbound Rules

  17. Browse to the instance then copy the endpoint.
  18. Locating the Endpoint

  19. Test and verify your RDS instance by logging in. Open SQL Server Management Studio (SSMS), paste the endpoint and be sure to replace the “:” with a comma “,” before the port number. For authentication, select “SQL Server Authentication” and then key in the login name and password. Click the “Connect” button:
  20. Connecting to the RDS instance

  21. If you are able to see the RDS instance on “Object Explorer,” you are connected and you have successfully created your RDS instance:
  22. Connected

Road Trip: SQLSkills Immersion Event on Performance Tuning and Optimization – Part 1 @ Bellevue, WA

The following takes place between June 8th to June 14th 2014:

Journey to the West (June 8th 2014)

What a beautiful Sunday morning! It must have felt like 78 to 82 degrees Fahrenheit. I was excited about this road trip. I will be taking lessons on SQL Server performance tuning from Paul Randal and Kimberly Tripp, the top experts in the SQL Server industry. I am heading over to Bellevue, WA for the “SQLSkills Immersion Event on Performance Tuning and Optimization – Part 1.” I was about to be “Up in the Air” again. The voice of George Clooney giving Anna Kendrick advice on how to go through airport security was echoing on top of my head again as the car got closer to The LaGuardia (LGA) Airport. In case you are interested, you can search for the keyword “airport security” on this link http://www.imdb.com/title/tt1193138/quotes to see what Ryan Bingham said to Natalie Keener. Good advice. It is true. It takes me 2 minutes or less to go through the airport security.
When in Philly, enjoy a Philly Cheesesteak! It’s not as good as the Geno’s Steaks. But, it’s the airport. Let’s just comprise. Yes. Even though it’s less than 45 minutes away from the New York LaGuardia (LGA) Airport, the connecting flight is in Philadelphia. I could hear my European colleagues’ voices, “You Americans. Your banknotes are all the same sizes and you have a connecting flight in less than 95 miles!”

Sleepless in Seattle? (June 8th to June 14th 2014)

I haven’t watched the movie “Sleepless in Seattle” yet. But, if I knew Tom Hanks, I would have advised him to sleep in Bellevue, WA instead. It’s across the river from Seattle. It’s only 45 minutes away from Seattle downtown as per my experience by Sound Transit Bus Line 560 http://www.soundtransit.org/Schedules/ST-Express-Bus/560. I bet it will take much less time if you drove your own car. The most important thing is that he would have slept like a baby like I did!
It is called Subway in New York City; BART in San Francisco; Tube in London. In Seattle, they call it The Link. Here’s a photo:
The_Link

In New York, there’s the Empire State Building. In Chicago, the Sears Tower (now called the Willis Tower). In Toronto, the CN Tower. In Seattle, here’s the Space Needle:
Space_Needle

Do you fancy some fish? Here’s the world famous “The Pike Place Market”:
Farmers_Market

Believe the hype! (June 9th to 13th 2014)

Think you know SQL Server? Think again!
“Immersion Event on Performance Tuning and Optimization – Part 1” is the best professional training course that I have ever attended in the past decade based on 20+ courses evaluations. In the class, there were very knowledgeable, talented, and accomplished SQL Server DBAs from all corners of the world travelled to Bellevue, WA to take lessons on SQL Servers from the true masters of SQL Server: Paul Randal and Kimberly Tripp. In addition to the world-class SQL Server training for the week, it was great to network with these amazing and passionate SQL Server DBAs from around world.
During the week, Paul Randal and Kimberly Tripp explained database structures, data file internals and maintenance, transaction and locking, snapshot isolation, optimizing logging and recovery, index fragmentation, index internals and covering, statistics internals and updates, cardinality estimation issues, indexing strategies, and answered all questions that the attendees brought with them plus the questions that you can’t Google for possible answers. Paul Randal and Kimberly Tripp’s knowledge about SQL Server is exceptional and their clarity of explanations on SQL Server is bar none. They are simply amazing and the best in the industry.
Believe the hype! There are Senior SQL Server DBAs, Superstar SQL Server DBAs, then Real-Deal SQL Server DBAs, and then there are Paul Randal and Kimberly Tripp. They are in an elevated league of their own.

Microsoft Campus Building 35 (June 11th 2014)

Sure. I was tired after more than 8 hours of in-depth SQL Server lecture. But, it would be regrettable not to take a road trip to Redmond, WA to visit the Microsoft Campus. It would have been even more regrettable as SQL Server DBA and not visit Building 35, the building where the Microsoft SQL Server Development Team works.
Here are the photos:
To_Redmond

Shuttle_Connect

Microsoft_Campus

Microsoft_Campus

Building_35

Building_35_Front

Paul Randal’s Autograph (June 13th 2014)

Guess who’s the winner for a copy of the “Microsoft SQL Server 2012 Internals” book and asked Paul Randal to sign it? This is simply icing on the cake!
SQLSkills_IE1

Paul_Randal_Autograph

How to Display Line Numbers in the SQL Server Management Studio Query Window?

If it hasn’t already happened to you as a SQL Server DBA, you can bet on it that it will at some point in the near future. You are writing a script that has more than two pages, after you pat yourself on the back for a job well-done, you hit the execute button. To your horror, the script has an error. The error message references to a particular line number within your script. It is 3 am in the morning, you need to have this script ready and then head to bed for 3 hours of sleep before you have to get up to race to catch the railroad or the bus into the city. You are frustrated. You need to find the line number where the syntax error has occurred so that you can fix it or have a good starting point to dig a little deeper into what is the possible problem. Let’s “Keep Calm and Carry On.” SQL Server Management Studio (SSMS) has the option for you to enable the line numbers and here’s how it can be done:
1. Open SSMS. The on the menu bar, click on Tools > Options…
Tools and Options
2. On the “Options” dialog box, expand the “Text Editor” node then expand the “Transact-SQL” node. Click on “General” on the subtree and then on the “Display” section click on the “Line numbers” checkbox to enable or disable line numbering.
Line Number
3. Open a new query window and key in some T-SQL code. You will see the line number on the left hand side:
Query Window With Line Numbering

How to Change the Color in the SQL Server Management Studio Status Bar?

Have you ever executed a script that’s intended for testing purposes in the Production SQL Server environment rather than in the Development environment? If you have had this regrettable experience, don’t you wish SQL Server can give you color code warnings of the different types of environment? Well, actually, SQL Server does have this option and it is relatively easy to configure it. Similar to the traffic lights, you can configure red for Production, yellow for Staging, and green for Development. Just like traffic light, it can avert disasters or save you many hours to undo or attempt to undo the avoidable mistake.
Here’s how it is done:
1. Open SSMS.
2. On the “Connect to Server” dialog box, click the “Options >>” button.
Connect to Server
3. Click the “Connection Properties” tab, click on the “Select…” button to pick a color, and then click on the “Connect” button to connect to the designated SQL Server instance.
Connection Properties
Color Picker
4. Open a new query window. You will then see the color that you had picked as the background color on the status bar.
SSMS Red Status Bar

How to query and display the SQL Server port #?

Yes. You can query and display the SQL Server port # through TSQL. You can do so by querying the Windows Registry using the xp_regread stored proc. The following code is how it can be done:

DECLARE @tcp_port nvarchar(5)
EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @tcp_port OUTPUT

SELECT SERVERPROPERTY('SERVERNAME') AS [Instance Name]
,  @tcp_port AS [SQL Server Port #]

If you wish to send yourself an html report or you will be adding this section to your SQL Server Health Check Report, you can use the following code:

-- Query and display and SQL Server port #
 /*NOTE: Replace the recipients email addresses in line #17 */

set nocount on
-- declare the required variables 
DECLARE @Recipients VARCHAR(123) -- recipients for this alert
, @instancename varchar(55)
, @IsDBMailEnabled BIT
, @MailProfile VARCHAR(123)
, @MailSubject VARCHAR(123)
, @Subject VARCHAR(123)
, @TableHTML VARCHAR(MAX)

select @instancename = CONVERT(varchar, SERVERPROPERTY('SERVERNAME'));


SET @Recipients = 'email@company.com' -- specify your recipients here, separate with ;

DECLARE @tcp_port nvarchar(5)
EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @tcp_port OUTPUT

-- check if database mail is enabled
SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'Database Mail XPs'

IF @IsDBMailEnabled = 0
RAISERROR ('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

ELSE
BEGIN
	SELECT @TableHTML = 
	'<html>
	<div style="font-family:Verdana; font-size:14pt;"><div style="font-weight:bold; width:auto; float:left;">'+@instancename+'</div><div style=" width:auto; float:left; padding-left:5px; "> SQL Server Port #:</div><br></div></div><br>
	<table style="border: 1px solid #000000; border-collapse:collapse; table-layout:fixed; font-family:Arial; font-size:11px;" cellpadding=0 cellspacing=0 border=0>' +
	'<tr style="border: 1px solid #000000; font-family:Verdana; font-size:12px; background-color:#3B8D24; color:white; height:30px;">
		<th style="border: 1px solid #000000;" align=center>Instance Name</th>
		<th style="border: 1px solid #000000;" align=center>Port #</th>
	</tr>'
		
	SELECT @TableHTML = @TableHTML +
		'<tr>' +
		'<td style="border: 1px solid #000000;">' + @instancename + '</td>'+
		'<td style="border: 1px solid #000000;">' + @tcp_port  + '</td></tr>'

	SELECT @TableHTML = @TableHTML +'</table></html>'
			
	-- Send email notification
	SELECT @MailSubject = @instancename + ' SQL Server Port #'
		
	-- Find the database mail profile name
	create table #dbmail_profile
	(
	profileid int null,
	profilename varchar(125) null,
	accountid int null,
	accountname varchar(125) null,
	sequencenumber int null
	)
	insert #dbmail_profile
	EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

	select @MailProfile = profilename
	from #dbmail_profile
	where sequencenumber = 1

	drop table #dbmail_profile

	EXEC msdb.dbo.sp_send_dbmail
		@profile_name = @MailProfile,
		@recipients = @Recipients,
		@subject = @MailSubject,
		@body_format = 'HTML',
		@body = @TableHTML;
END
set nocount off	

This is an example of what it looks like:
SQL Server Port #

How to query and display the SQL Server Error Log location?

Even if you had installed the SQL Server instances yourself, you may not be able to have all the SQL Server Error Log locations for all the SQL Server instances on top of your head. In case of emergency, you need to review the SQL Server Error Logs for troubleshooting purposes, how can you find out the location of the error log file instantly?

You can use the built-in SERVERPROPERTY() function to query the location of the SQL Server Error Log file. The following query will get the job done. Yes. Just one line. That’s all it takes:

SELECT SERVERPROPERTY('ErrorLogFileName') AS [SQL Server Error Log Location]

If you wish to send yourself an html report or adding this informational section to your SQL Server Health Check report, the following is the code:

-- Display the SQL Server Error Log Location
 /*NOTE: Replace the recipients email addresses in line #17 */

set nocount on

-- declare the required variables 
DECLARE @Recipients VARCHAR(123) -- recipients for this alert
, @instancename varchar(55)
, @IsDBMailEnabled BIT
, @MailProfile VARCHAR(123)
, @MailSubject VARCHAR(123)
, @Subject VARCHAR(123)
, @TableHTML VARCHAR(MAX)
select @instancename = CONVERT(varchar, SERVERPROPERTY('SERVERNAME'));


SET @Recipients = 'email@company.com' -- specify your recipients here, separate with ;

-- check if database mail is enabled
SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'Database Mail XPs'

IF @IsDBMailEnabled = 0
	RAISERROR ('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

ELSE
	BEGIN
		SELECT @TableHTML = 
		'<html>
		<div style="font-family:Verdana; font-size:14pt;"><div style="font-weight:bold; width:auto; float:left;">'+@instancename+'</div><div style=" width:auto; float:left; padding-left:5px; "> SQL Server Error Log Location:</div><br></div></div><br>
		<table style="border: 1px solid #000000; border-collapse:collapse; table-layout:fixed; font-family:Arial; font-size:11px;" cellpadding=0 cellspacing=0 border=0>' +
		'<tr style="border: 1px solid #000000; font-family:Verdana; font-size:12px; background-color:#3B8D24; color:white; height:30px;">
			<th style="border: 1px solid #000000;" align=center>SQL Server Error Log Location</th>
		</tr>'
		
		SELECT @TableHTML = @TableHTML +
			'<tr>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(varchar(max), SERVERPROPERTY('ErrorLogFileName')) + '</td></tr>'
		SELECT @TableHTML = @TableHTML +'</table></html>'
			
		-- Send email notification
		SELECT @MailSubject = CONVERT(VARCHAR(50),@instancename) + ' SQL Server Error Log Location'
		
		-- Find the database mail profile name
		create table #dbmail_profile
		(
		profileid int null,
		profilename varchar(125) null,
		accountid int null,
		accountname varchar(125) null,
		sequencenumber int null
		)
		insert #dbmail_profile
		EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

		select @MailProfile = profilename
		from #dbmail_profile
		where sequencenumber = 1

		drop table #dbmail_profile

		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @MailProfile,
			@recipients = @Recipients,
			@subject = @MailSubject,
			@body_format = 'HTML',
			@body = @TableHTML;
	END

set nocount off	

This is an example of what it looks like:
SQL Server Error Log Location