With this short article, I will tell you about How to Fix Distribution setup SQL Server Agent error: "RegCreateKeyEx() returned error 5, 'Access is denied.'" on my SQL Server 2012 Hosting.

In the Configure Distribution Wizard, the step "Configuring SQL Server Agent to start automatically" errors with the following text:

TITLE: Configure Distribution Wizard
------------------------------
An error occurred configuring SQL Server Agent.
------------------------------
ADDITIONAL INFORMATION:
RegCreateKeyEx() returned error 5, 'Access is denied.' (Microsoft SQL Server, Error: 22002)

This is a very minor error, and not difficult to work around at all. The wizard is making an attempt to alter the SQL Server Agent service "Start Mode" to Automatic. you'll be able to try this via the SQL Server Configuration Manager instead.

In the Sysinternals method Monitor, you'll see: Operation: RegCreateKey Result: ACCESS DENIED Path: "HKLM\System\CurrentControlSet\Services\SQLAgent$SQL2012"

If you encounter this error, choose "No" in the "SQL Server Agent Start" page in the configure Distribution Wizard (as shown below), so set your agent service to Automatic start Mode via the SQL Server Configuration Manager.

The third step of the wizard that failed before won't happen. Why the failure truly happens I didn't figure this out, and i am open to feedback, however this seems like a vestigial step to a wizard that otherwise has no negative impact. Running SSMS with "run as Administrator" doesn't seem to fix this error either. i would like to recognize why this error happens within the 1st place.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

Instead of triggers are used to skip DML commands. They fire when you try to execute insert, update or delete statement but instead of executing these commands trigger actually works and trigger functionality executes.
 

Example
    create table approved_emp ( eid int identity(1,1), ename varchar(30)) 
    create table emp ( id int identity(1,1) , ename varchar(30), AddedBy varchar(30)) 
      
    Create trigger instead_of on approved_emp 
    instead of insert 
    as 
    begin 
    declare @name varchar(30) 
    select @name=ename from inserted 
    insert into temp_audit values(@name, USER ) 
    end 

So, basically, trigger will work as, when we will try to add new record in approved_emp table, instead of inserting new records it will add ename into emp table. No data will reflect in approved_emp table as trigger is fired on the table every time while adding data into that table.
 
You can also create instead of triggers for update and delete as well.
 

Database Administrators usually use the sp_renamedb system stored procedure to quickly rename a SQL Server Database. However, the drawback of using sp_renamedb is that it doesn't rename the Logical and Physical names of the underlying database files. It's a best practice to make sure the Logical Name and Physical File Name of the database is also renamed to reflect the actual name of the database to avoid any confusion with backup, restore or detach/attach operations.

Let's first create a new database named CoreDB using the T-SQL below:

USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CoreDB')
DROP DATABASE CoreDB
GO
USE master
GO
CREATE DATABASE [CoreDB]
ON PRIMARY
(
NAME = N'CoreDB',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CoreDB.mdf' ,
SIZE = 2048KB ,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'CoreDB_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CoreDB_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 10%
)
GO
Rename CoreDB Database Using sp_renamedb System Stored Procedure

Now let's rename the CoreDB database to ProductsDB by executing the below T-SQL code.

USE master
GO
ALTER DATABASE CoreDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
EXEC master..sp_renamedb 'CoreDB','ProductsDB'
GO
ALTER DATABASE ProductsDB
SET MULTI_USER
GO
Once the above T-SQL has executed successfully the database name will change however the Logical Name and File Name will not change. You can verify this by executing the T-SQL below:

USE master
GO
/* Identify Database File Names */
SELECT
name AS [Logical Name],
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'ProductsDB')
GO
Your output should look something like this from the above query.

You can see in the above snippet that the Logical Name and File Name in the DB File Path column for ProductsDB are still reflecting the old name of CoreDB. This is not a good practice to follow in a Production Environment. Below you will see the steps which a DBA can follow to rename the database and its respective files.

Steps to Rename a SQL Server Database

DBAs should follow the below steps which will not only rename the database, but at the same time will also rename the Logical Name and File Name of the database.

This first set of commands put the database in single user mode and also modifies the logical names.


/* Set Database as a Single User */
ALTER DATABASE CoreDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/* Change Logical File Name */
ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB', NEWNAME=N'ProductsDB')
GO
ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB_log', NEWNAME=N'ProductsDB_log')
GO
This is the output from the above code.

Now we need to detach the database, so we can rename the physical files.  If the database files are open you will not be able to rename the files.

/* Detach Current Database */
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'CoreDB'
GO
Once the CoreDB database is detached successfully then the next step will be to rename the Physical Files. This can be done either manually or by using the xp_cmdshell system stored procedure. You can enable xp_cmdshell feature using the sp_configure system stored procedure.

USE master
GO
sp_configure 'show advanced options'
GO
/* 0 = Disabled , 1 = Enabled */
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Once xp_cmdshell is enabled you can use the below script to rename the physical files of the database.

/* Rename Physical Files */
USE [master]
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\
MSSQL\DATA\CoreDB.mdf", "ProductsDB.mdf"'
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\
MSSQL\DATA\CoreDB_log.ldf", "ProductsDB_log.ldf"'
GO
Once the above step has successfully executed then the next step will be to attach the database, this can be done by executing the T-SQL below:

/* Attach Renamed ProductsDB Database Online */
USE [master]
GO
CREATE DATABASE ProductsDB ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.ldf' )
FOR ATTACH
GO
Once the above step has successfully executed then the final step will be to allow multi user access for the user database by executing the below T-SQL:

/* Set Database to Multi User*/
ALTER DATABASE ProductsDB SET MULTI_USER
GO
You can verify the Logical and File Names for the ProductsDB database by executing the T-SQL below:

USE master
GO
/* Identify Database File Names */
SELECT
name AS [Logical Name],
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'ProductsDB')

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

In this article, we will see about fixed database roles in SQL Server. As the name suggests, fixed database roles cannot be removed or modified and performs specific administrative tasks as it has pre-defined set of permissions. You need to be very careful while assigning these roles and assign these roles only when there is a serious requirements. You can execute the sp_helpdbfixedrole system procedure to get the list of fixed database roles.

EXEC sp_helpdbfixedrole
GO

• db_owner
• db_accessadmin
• db_securityadmin
• db_ddladmin
• db_backupoperator
• db_datareader
• db_datawriter
• db_denydatareader
• db_denydatawriter

You can navigate to Database Roles from Object Explorer –> Expand Databases Node –> Select Database –> Navigate to Security –> Roles –> Database Roles

db_owner

All the members of db_owner fixed database roles can perform all the maintenance activities and setting configurations. This role should not be given to regular users and should be assigned very carefully as it can perform almost all the operations in a database.

db_accessadmin

As the name suggests, all the members of db_accessadmin can handle access related issues. This role controls security, grants access, revokes access for logins to enter database. This role is rarely used as these operations are performed by DBAs as he has relevant fixed server role.

db_securityadmin

Members of db_securityadmin role manages all the permissions and security related activities. As database administrators usually manages security, permissions, role membership etc. so this role is hardly assigned and used. You should not assign this role to regular users.

db_ddladmin

If any member is assigned with db_ddladmin role then he can perform all DDL operations and can execute, create, drop and alter any objects. Normally this role is assigned to developers to perform the related operations in application. This role is usually not assigned to regular users as he can misuse the DDL operations.

db_backupoperator

db_backupoperator role can perform the database backup operations. This role is rarely used as backup activity is a role of database administrator and he has much higher permissions rather than using this specific fixed database role.

db_datareader

If any user is requesting for SELECT permission on database tables then you can tag him with db_datareader fixed database role as this role allows a member to perform SELECT operations on database tables and views and tagged member will not be able to modify any object. This role is mostly assigned to developers and regular users who need table access on production database.

db_datawriter

All the members of db_datawriter fixed database role can perform INSERT, UPDATE, DELETE operations on all tables and views in respective database. This role is basically assigned to developers to perform operations on QA servers. Testing applications sometimes require this role.

db_denydatareader

As the name suggests this role doesn’t allow to read data from tables in a database. So user will not be able to perform SELECT operations on a table or views. I haven’t used this role any time and also never seen members assigned with this role.

db_denydatawriter

This role is basically opposing db_datawriter role which means members with this role will not be able to perform INSERT, UPDATE or DELETE operations on tables and views. Again as a DBA I have not used this role yet and never seen anyone using this role.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

Today I had a problem with a table that has around 5 million records on my SQL Server 2012. The table has as primary key “Header No.”,”Transaction No.”. In the table is a field “Order No.” that at the moment is blank for all records. I also had an index on that field, because I will need to search for it. I think that in the future only 1 in several 1000 records will have an order no. So the index will be highly selective because I will have only 1 record per order no.

The code is like this:
Table.RESET;
Table.SETCURRENTKEY(“Order No.”);
Table.SETRANGE(“Order No.”,TheOrderNo);
IF Table.FINDFIRST THEN BEGIN

It should go lightening fast but it takes almost 1 second for each FINDFIRST.

Why?
The SQL plan gave me this:

Top[2,1];Nested Loops[3,2];Index Seek($2)[4,3];Clustered Index Seek(Company Name$Table$0)[6,3]
WHAT? A CLUSTERED index Seek?????
it should have been an index seek.
The select was what I expected:
SELECT TOP 1 * FROM “Company Name$Table” WITH (READUNCOMMITTED)  
HERE ((”Order No_”=?)) ORDER BY “Order No_”,”Header No_”,”Transaction No_” OPTION (OPTIMIZE FOR UNKNOWN)

But because SQL knows that the Field “Order No.” contained only blanks, it decided to do a clustered index seek because of the ORDER BY (at least this is what I think SQL decided). So how to fix it NOW (I don’t want to wait that it goes into production and it takes days or months before the problem fixes itself.

What is the code for?
The FINDFIRST serves because I want the Invoice No. of that record and it has to be a non-blank value.
So, I changed the index from “Order No.” to “Order No.”,”Invoice No.”. It makes the index a little bigger, but it shouldn’t be a big problem (or at least I hope so).
The C/AL code I changed like this:

Table.RESET;
Table.SETCURRENTKEY(“Order No.”,”Invoice No.”);”
Table.SETRANGE(“Order No.”,TheOrderNo);
Table.SETFILTER(“Invoice No.”,’<>%1’,’’);
IF Table.FINDFIRST THEN BEGIN

Even with all blank values in “Order No.”,”Invoice No.” at the moment, SQL has changed its plan to an index seek and the code has become very fast.

Recently one of my application website went down. I checked all the basic connectivity troubleshooting on my SQL Server 2012 Hosting and seem everything was looking and working fine. Finally, I found the problem with the browser service but that’s also in running state.

Error from the event viewer:

The quick solution is rebooting the SQL browser (Start –>All programs–>Microsoft SQL server 200X–>Configuration tools –>SQL server configuration Manager) without rebooting SQL service. I searched and found a couple of MS links (KB-2526552 And SQLBrowser Unable to start) but, I did not apply it. I used another way that is also a permanent fix.

Troubleshooting ways and a permanent fix:

For me it’s a named instance and listening a dynamic port and DBAs knows the browser service is mainly for named instance. From the local machine we can connect the server through SSMS by using server name and server name + port number. But, other than local machine you cannot connect the server by using server name. (You can test that by connecting some other server or better install only SSMS on the application server and try to connect it) so I went to the application server and opened a connection string as expected the data source only has the server name. So We changed it from Datasource “from Data Source=Servername\Instance to Data Source= Servername\Instance,port” Ex: Muthu1\SQL1,5432.

Application team made a standard to always include a port number in the connection string block i.e. FQDN. 

A Basic SQL Connectivity checks:

- Check SQL service is running or not and try to connect through SSMS from local and remote

- Check TCP/IP protocol enabled on SQL server configuration manager and find the port number

- Connect using a server+port number from SSMS local and remote

- For firewall block/port not opened you can check through command prompt TELNET server port ex: TELNET server 1433

- Check remote connections are enabled & SQL Browser service is running (For a named instance which is not using FQDN)

- Check you have any alias/DNS name.

We have been using plus sign (+) operator for concatenating string values for years in SQL Server with its limitations (or more precisely, its standard behaviors). The biggest disadvantage with this operator is, resulting NULL when concatenating with NULLs. This can be overcome by different techniques but it needs to be handled. Have a look on below code;

 -- FullName will be NULL for 
 -- all records that have NULL 
 -- for MiddleName 
 SELECT  
   BusinessEntityID 
   , FirstName + ' ' + MiddleName + ' ' + LastName AS FullName 
 FROM Person.Person 
 -- One way of handling it 
 SELECT  
   BusinessEntityID 
   , FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName AS FullName 
 FROM Person.Person 
 -- Another way of handling it 
 SELECT  
   BusinessEntityID 
   , FirstName + ' ' + COALESCE(MiddleName, '') + ' ' + LastName AS FullName 
 FROM Person.Person 

SQL Server 2012 introduced a new function called CONCAT that accepts multiple string values including NULLs. The difference between CONCAT and (+) is, CONCAT substitutes NULLs with empty string, eliminating the need of additional task for handling NULLs. Here is the code.

 SELECT  
   BusinessEntityID 
   , CONCAT(FirstName, ' ', MiddleName, ' ', LastName) AS FullName 
 FROM Person.Person 

If you were unaware, make sure you use CONCAT with next string concatenation for better result. However, remember that CONCAT substitutes NULLs with empty string which is varchar(1), not as varchar(0).

There are plenty of tutorials out there that explain how to configure an MS SQL Server alias. However, since none of them worked for me, I wrote this post so I'll be able to look it up in the future. Here's what finally got it working for me.

My Use Case

In my development team at work, some of our local database instances have different names. Manually adapting the connection string to my current local development machine every single time is not an option for me because it's error-prone (changes might get checked into version control) and outright annoying.

The connection string we're using is defined in our Web.config like this:

<add name="SqlServer" connectionString="server=(local)\FooBarSqlServer;…"   

providerName="System.Data.SqlClient" />

This is the perfect use case for an alias. Basically, an alias maps an arbitrary database name to an actual database server. So I created an alias for FooBarSqlServer, which allows me to use the above (unchanged) connection string to connect to my local (differently named) SQL Server instance. That was when I ran into the trouble motivating me to write this post. The alias simply didn't work: I couldn't use it to connect to the database, neither in our application nor using SQL Server Management Studio.

The Working Solution

I googled around quite a bit and finally found the solution in Microsoft's How to connect to SQL Server by using an earlier version of SQL Server: The section Configure a server alias to use TCP/IP sockets pointed out that I had to look up the specific port number used by the TCP/IP protocol:

Here's how you find the port number that's being used by TCP/IP on your machine:

1) Open the SQL Server Configuration Manager.

2) Expand SQL Server Network Configuration and select Protocols for <INSTANCE_NAME>.

3) Double-click on TCP/IP and make sure Enabled is set to Yes.

4) Remember whether Listen All is set to Yes or No and switch to the IP Addresses tab.

- Now, if Listen All was set to Yes (which it was for me), scroll down to the IPAll section at the very bottom of the window and find the value that's displayed for TCP Dynamic Ports.

- If Listen All was set to No, locate the value of TCP Dynamic Ports for the specific IP address you're looking for.

You'll have to copy this port number into the Port No field when you're configuring your alias. Note: that you'll have to set the Alias Name to the exact value used in your connection string. Also, if you're not using the default SQL Server instance on your development machine (which I am), you'll need to specify its name in the Server field in addition to the server name. In my case, that would be something like YourDirectory\NAMED_SQL_INSTANCE. Remember to also define the alias for 32-bit clients when your database has both 64-bit and 32-bit clients.