What to do when you are genuinely bored and you need to copy MS SQL logins, between instances with dbatools.

Hi all, this is what happened today at work to me, an Accidental SQL DB Administrator, amongst other “virtues”.

The Reason?

Support team requested a new instance of SQL server 2017. That’s it!!! I will not add manually every user existed in the previously installed SQL instance to the new, period.

It’s not the process that is boring… I’m bored doing menial jobs. So, what should I do…?

Well thank God and Microsoft PowerShell exists, now how can I use this to complete the job at hand.

Google you say, right… so found this blog https://blog.netnerds.net/2016/06/its-2016-why-is-sp_help_revlogin-a-thing/.

This a well written post by Chrissy LeMaire arguing the use of the (let’s face it ) sp_help_revlogin to PowerShell. By the way sp_help_revlogin enables SQL Admins to copy users between SQL instances and/or servers, not always in straight forward manner.

So you might wonder why don’t you use sp_help_revlogin  told you I’m bored and a like PowerShell a lot.

First things first install DBatools :

1. using PowerShell with Install-Module dbatools
2. using Chocolatey with Choco install dbatools
3. clone the project from Github

There 400 commands in dbatools and you can regain sysadmin access, take back-ups effortlessly, keep track of your databases, export your users in list etc.

Well long story sort, I launched a PowerShell window and enter the following command:

Copy-dbaLogin -Source SQL1 -destination SQL1\Instance1

The above command copies all logins from default instance to another. You could also copy from instance to instance it works!!!!!

Active Directory Forced Replication

Hi all, this is what happened today at work, to me, an Accidental Active Directory Administrator, amongst other “virtues”.

The Reason?

A developer enters the room … silence… as if God Almighty came down to earth.

He was furious ,someone cannot say who, went over and beyond his duties and added him to Active Directory guests group. So every time he would log off the user profile would be deleted.

No biggie you say … well you know he is a developer … unaware of any-other drive than C:\ and any-other than c:\users\Dev_GOD_Almighty\Desktop\Very_Big_Project.exe.

With that being said he was removed from guests group and since we keep a plethora of Active Directory servers we needed to force this changed to every directory server. Someone cannot say who, suggested the following :

1. Open Sites and services
2. Expand Sites > Default-First-Site-Name
3. Locate directory server you altered and expand
4. Click on NTDS Settings
5. Right click on the entries you see on the right hand side and select “replicate now”

I was bored to do all this clicking, honest to Dev God Almighty, so i push my self to remember the CLI command to force replication.

Luckily i did, the command is repadmin and this is what i did:

1. Opened cmd from a client
2. Wrote down repadmin /replicate Dest_SRV Source_SRV DC=Paradise,Dc=Lost /force

If you get Sync from Source_SRV to Dest_SRV completed successfully you are done, replication is forced.

Ten minutes later i received a call by the Dev, thanking me for my prompted response to his problem … Now I’m in God’s almighty favor!!!!

Lessons Learned

The repadmin command comes with a variety of switches that will help you do your job in a quick and efficient way:

• /showrepl command helps you understand the replication topology and replication failures
• /replicate triggers the immediate replication of the specified directory
  partition to the destination domain controller from the source DC
•  /replsummary  summarizes quickly and concisely the replication state and relative health of a forest
•  /syncall synchronizes a specified domain controller with all replication
  partners.

P.S. I still cannot say who moved him to guest group….

 

 

SQL Suspect DB

Hi all, this is what happened today at work to me, an Accidental SQL DB Administrator, amongst other “virtues”.

Well now let me describe the situation. Early in the morning we received a call from a customer in distress.

The Reason?

An SQL db was tagged as Suspect…

The client claimed that it happened out of the blue. Well you have to read the fine print every time a client gives you an “accurate” description of the problem…

After several minutes we’ve learned that there was a power failure in the data-center, UPS and electric generator did not performed (I assumed that there is no UPS or a generator).

As soon as the SQL server was up, their main WMS db as tagged Suspect by the SQL.  There are several reasons for a DB to be tagged as Suspect, some of them follow:

• Database Server wasn’t properly shutdown.
• Database or log files are corrupted.
• Database or log files are missing.
• Database or log files are “locked” due to backup process.
• Device that stores Database or log files is not available.
• Insufficient disk storage and /or memory.

Well a while ago I stumbled upon this article about suspect dbs. I remembered this statement:

Never detach a suspect database

So I advised the client to follow the following (based on the previous article):

1. ALTER DATABASE [db name] SET EMERGENCY;

Setting Emergency mode allow you to access the suspect db

2. ALTER DATABASE [db name] SET SINGLE_USER;

Setting db in Single User mode is prerequisite for repair process.

3. DBCC CHECKDB (N’ db name ‘, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

Process log for recovery of as much data as it can be recovered then forces damaged log to rebuild. Finally it performs a full repair of the db.

4. SELECT DATABASEPROPERTYEX (N’DemoSuspect’, N’STATUS’) AS N’Status’;

Use this to check db state.

After all these db finally became Online.

Lessons Learned

If it happens and you (or you don’t want to) find yourself in a similar position as our client was, invest in a UPS “big” enough to cater your needs (preferably an online), invest in a power generator and last but not least ALWAYS HAVE A BACKUP and that is another story…