Migrating databases between SQL Server instances

If you ever have a need to migrate SQL Server databases between servers; particularly a backup/restore scenario, you need to be aware that the database security login ids are not automatically migrated. Here are some notes on this task.

Once databases are backed up from one instance and restored to another instance, database security needs to be transferred as well.

This task can be a challenge, fortunately Microsoft has created a SQL script that can be used for this purpose. This can be found here:

How to transfer logins and passwords between instances of SQL Server

The process is well documented. The steps are as follows:

1) From the transfer logins and passwords web page, copy/paste the SQL script to SQL Server Management Studio (SSMS) running on the SOURCE server.

2) Execute the script – it will create a new stored procedures on the Master database.

3) From SSMS, run the following query:

EXEC sp_help_revlogin

The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

4) On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.

Important Before you go to step 5, review the information in the "Remarks" section. (Note: see web page mentioned earlier)

5) Open a new Query Editor window on TARGET server, and then run the output script that is generated in step 3.

Posted in ConfigMgr, SQL, SQL Server, SQL Server 2012, SQL Server 2014 | Leave a comment

ConfigMgr – Why you need to implement DNS Scavenging

Interestingly, the topic of DNS scavenging came up on the MVP alias. Since it appears many client environments still have not yet enabled DNS scavenging, this article is dedicated to why you NEED to enable DNS scavenging. Before it is too late. (dramatic enough? ;)

First, a short story. While employed at a large financial services company as one of their Windows Engineers and lead SCCM architect, we started encountering some strange issues with SCCM remote control. Attempt to remote control a Windows client by NETBIOS name and *another* Windows client would be brought up in the remote control session.

After troubleshooting this issue, we came to the conclusion that there were multiple DNS records for the same NETBIOS name, each with a different IP address! So, it was rather random whether you’d actually remote control to the intended client. Turns out, in 5 years or so that Active Directory was in use, DNS scavenging had NOT been enabled, leading to 1,000’s of invalid, old entries. Implementing DNS Scavenging solved this issue.

What are some of the side effects of DNS with no scavenging? Here are just a few of the possible side effects:

- Remote control fails to reach the intended client

– Client Installation Push method fails to connect to the correct client

– OSD installations – software installations fail

– Client Inventory reporting

– Active Directory discovery issues

How to properly implement? There are a few things you need to know about DNS Scavenging; the first is implementing DNS Scavenging within DNS, then enabling at each DNS zone and the impact of DHCP lease duration.

Some general advice; I’ll usually enable and leave DNS scavenging set to the default 7 days per DNS zone. Since DHCP lease renewals happen approximately half way through the lease period, even a 7 day DHCP lease will typically be OK.

Scavenging should only remove those records that were dynamically registered, not the manually entered A records.

The recommendation would be to take a backup of your DNS environment prior to implementing. And, for the first implementation, starting on an evening or weekend is probably a good idea.

For those clients that get removed from DNS because they are on vacation for 2 weeks, they re-register when they re-connect, and will likely send a new ConfigMgr hw delta at the same time.





Script to locate duplicate DNS entries (thanks Russ):


Posted in ConfigMgr | 8 Comments

ConfigMgr 2012 R2 support for SQL Server 2014

ConfigMgr 2012 R2 does support SQL Server 2014, but only if it had been installed with SQL Server 2012, then upgraded to SQL Server 2014.

The interesting part arises, what if you have to perform a restore of ConfigMgr 2012 R2 on another server? If you install ConfigMgr 2012 R2, the recovery wizard will not work with SQL 2014. Then what?

Microsoft has addressed this issue with the following SQL 2014 restore hotfix (KB 3020755). Turns, out this hotfix is not CU dependent, and can be used to install ConfigMgr 2012 R2 natively with SQL Server 2014.

Posted in ConfigMgr, SQL Server 2014 | Leave a comment

SCU 2015 – Top 10 ConfigMgr 2012 Issues – SQL Content Links

Kent Agerlund and Johan Arwidmark; aka Robin and Batman (below) presented a great list of top 10 ConfigMgr 2012 Issues at the System Center Universe 2015 event in Irving, TX.


Appreciated the shout out to my blog for a few of the 10 ten issues; here are the direct links to save a little searching.

Top Issues

Slow or sluggish console performance… most likely culprit; out of date indexes and statistics

SQL Backups – take advantage of database compression

SQL PowerShell script – Kent touched on this one… how do you know if your database is fragmented, and a lot more.

Great event, thank you to the Catapult team and sponsors for making this event happen.

Posted in ConfigMgr, PowerShell | Leave a comment

Softchoice Openings in Dallas TX


Softchoice has two great openings in the Dallas, TX area.
Microsoft Solutions Architect (pre-sales)


Microsoft Senior Systems Engineer (Solution Delivery)


Please contact me for further details/referral!

Posted in Softchoice | Leave a comment

ConfigMgr 2012 R2 CU4 released


Hot off the MS grill:

3026739 – Description of Cumulative Update 4 for System Center 2012 R2 Configuration Manager

3031717 – Description of Windows PowerShell changes in Cumulative Update 4 for System Center 2012 R2 Configuration Manager

Posted in ConfigMgr | Leave a comment

T-SQL Tuesday #62 – Healthy SQL

 SQL Health Checks


Healthy SQL is an important topic… I’ve gotten involved in a number of SQL health checks and would like to share some of the key methodology, metrics investigated and tools/utilities used.

There are at least two main components to this topic:

- Review of existing SQL Server implementations

- Developing on-going monitoring to detect small issues before they become large ones (think alerting)

This article will focus on the first component; a review of a SQL Server instance or instances that may have not been touched since the initial install. It happens with some of the smaller clients that do not have a local DBA. Or, these SQL instances have been modified, post installation, by a party or parties that are now, no longer there!

What follows are the main categories and items checked, with a few links to relevant content. My personal preference for these SQL health check reviews; is to start at the 10,000 foot view, then work my way down to the items or items that deserve closer scrutiny. For example, some reviews I’ve seen ignore the server platform… if there are performance issues at that level, this can adversely affect SQL Server performance!

Following this checklist, there are some of the tools/utilities that I’ve found useful or have had to create to assist in the documentation effort.

Note: By no means is this meant to be inclusive list, primarily the important, high-level topics that should checked.

Windows Server Platform

- Operating System Version / Service Pack: running on latest, supported OS and SP for that platform?

- Physical / Virtual: investigate platform for optimal settings

- Server security: is the server physically secure, and are the accounts with local administrative rights appropriate?

- Security Updates: is the Windows Server up-to-date with the latest security updates?

- Logical disk configuration: drive space; total and available

- NFTS block sizes: drives dedicated for SQL Server set to 64KB?

- Performance: set up a performance (Perfmon) trace logging to a file for at least a 24 hour period, sampling every 5 minutes. Use appropriate performance counters for both Windows Server and SQL Server. Then review. Look for potential bottlenecks, and address. Save the performance log files; they become a good comparison point or baseline. If available, SolarWinds can be an excellent asset for examination of server and SQL performance.

- Page file: located on a drive other than logical C: ? Is the page file in use?

SQL Server

- SQL Server install location: installed on a separate drive, not on logical C: ?

- SQL Server install date

- Current configuration valuessp_configure – consider enabling advanced options here to view everything

- SQL Server memory allocation: max memory value set? (link for use of SQL server with ConfigMgr 2012)

- Database Names, paths, file size and location

- Database properties: owner, recovery model, log size and log percent in use

- I/O Rank: list database rank by IO (MB) – may help prioritize database investigation

- TempDB: sized appropriately, located on a dedicated drive?

- SQL Security: Mixed vs. Windows security mode, review sysadmin list for appropriate membership

- Monitoring: System Center Operations Manager 2012 or something else?


- Database / transaction log files: Auto-growth set appropriately?

- Statistics: last updated date fairly recent?

- Index fragmentation: Examine Average fragmentation, page counts, etc.

- Virtual Log Files (VLF) counts: while written with a bias toward ConfigMgr 2012, the concept applies to any database

- Last backup date: backups properly scheduled and restores tested?

Tools / Utilities used

Custom PowerShell SQL Audit script – Many, if not all of the above metrics can be captured by PowerShell and saved as an XLS workbook. This helps provide a baseline, the bonus is available documentation that can be shared and provides a guideline for any future changes. And, the PS script can be extended as needed.

Using Ola Hallengren’s Maintenance scripts – optimizing ConfigMgr 2012 databases applies to any database that needs to have the indexes and statistics kept up-to-date. There is a link from that blog to Ola’s open source scripts.

Diagnostic Queries – some great queries that I recommend, and have modified on occasion for specific purposes. A few of these have found their way into the Custom PowerShell script ;) (Thanks Glenn)

Windows Performance Monitor – I don’t need to explain this one, do I? ;)

Microsoft Assessment and Planning Toolkit – I’ve found this solution accelerator to be very useful for mapping SQL Server installations in new environments. Especially, where the client is not always sure what is installed.

SQLIO Disk Subsystem Benchmark Tool – On occasion, where SQL appears to have difficulties with the disk subsystem. SQLIO can help determine I/O capacity of a given system.


The above guidelines should provide a good start to determining SQL Health. As mentioned, these guidelines are in no means meant to be inclusive. Many times, this preliminary information will lead to deeper investigation. Additional work may be indicated (as needed) to investigate specific application interaction with SQL Server.

The challenge – for full details: http://www.sqlservercentral.com/blogs/pearlknows/2015/01/06/t-sql-tuesday-62-invitation-to-healthysql/

#HealthySQL #tsql2sday

Posted in SQL, SQL Server, SQL Server 2012, SQLTuesday | 2 Comments