Disclaimer

I am writing this blog  and others to explain how things work and some ways deployment and operational tasks can be handled. In other words, these postings are for demonstration purposes only. Since I am not familiar with your organization or environment I do not know if these steps are applicable to your environment or are even safe to perform in your environment. It is recommended that you contact Microsoft Support prior to making changes in your environment to ensure that these steps are applicable to your environment, and are safe to perform in your environment. By writing this blog I am in no way recommending that you perform these steps in your own environment. If you choose to follow the steps outlined in this or other blog postings on this site, you are assuming the risk for your actions.

1              Troubleshooting Active Directory Replication

1.1                 Repadmin.exe

Repadmin is a tool for checking replication status and troubleshooting replication issue.  Below is a table highlighting commonly used syntax of the repadmin tool.

Additional information on Repadmin.exe is available here: https://technet.microsoft.com/en-us/library/cc736571(v=ws.10).aspx

1.2                 Repadmin /replsummary

As seen in the screenshot below repadmin /replsummary will give statistics for replication with replication partners.  The output also lists any errors that were encountered with replication. This is useful for getting an overview of any replication issues the DC is having.

You can also sort the output.  In the example below, the output is sorted by the largest delta since last replication.

1.3                 Repadmin /showrepl

As seen below repadmin /showrepl shows the replication status with all of the DCs replication partners and is sorted by the Naming Context that is being replicated.

One trick that can be used to get a more manageable output is to use repadmin to send its output to a CSV and the use PowerShell to convert the CSV to a GridView.  The command to do this is repadmin /showrepl * /csv | ConvertFrom-CSV | Out-GridView

The resulting output is in a manageable GUI.

In GridView you can sort and filter.  Below is an example of filtering on Number of Failures, so that I can easily see what failed.

1.4                 Repadmin /showutdvec

Replications changes are tracked through incrementing numbers called USNs.  There are times where you will want to know what knowledge each DC has about other DCs current state.  The up-to-dateness vector is the knowledge that a DC as about the current state of other DCs.  This information can be useful when trying to troubleshoot replication issues such as USN Rollback.  USN Rollback is when a DC is restored from an unsupported method such as a snapshot.  In that case the up-to-dateness vector would be much larger than the actual USN of the DC. Since, there is going to be some delay in replication you will notice some differences but the numbers should be relatively close.  For example, if you compare the up-to-dateness vector for DC01 across DCs you will notice the following: for itself DC01 has USN of 17347, DC02 has a USN of 17346 for DC01, and DC03 has a USN of 17346 for DC01.  So, we can see the numbers are relatively close and that DC01 potentially has one change that it needs to replicate to DC02 and DC03.

1.5                 Repadmin /showobjmeta

The /showobjmeta switch shows detailed information for attributes of an object.  It is most commonly used when comparing the output of the command from 2 DCs to see if they are in sync and the current status of the attributes.  Differences can be used to identify replication problems.

1.6                 Repadmin /syncall

Repadmin /syncall is used to force replication between domain controllers.  You can easily view options for the /syncall switch with the following command: repadmin /syncall /?

A normal use of repadmin /syncall is with the /AeP switch

1.7                 Repadmin /showmsg

The /showmsg switch is used to convert an error message you may receive as the result of a repadmin command and converts it to human readable text.

1.8                 Repadmin /viewlist

Repadmin /viewlist is used to get a list of domain controllers.

1.9                 PowerShell

PowerShell is an object oriented scripting language that allows enterprises to automate IT tasks.

Below is a conversion table that shows the PowerShell command that can be used in place of the Repadmin command.  So, why would you choose to use PowerShell?  The output of PowerShell commands are objects those objects can be filtered with properties, piped through other PowerShell commands and manipulated to many useful things including great control in how the data is presented to the user.

Get-ADReplicationParnerMetadata is very similar to running repadmin /showrepl.  Without passing the output through another cmdlet the formatting is a bit different then to what you get with repadmin.

However, the advantage is that the output of the command are objects.  You can constrain your views to certain properties.

The other advantage is that you can pass objects through other cmdlets.  As seen here I am passing the output of Get-ADReplicationPartnerMetadata through Output-GridView.

Once in GridView you have the ability to sort and filter the data.

Here is another example of the usefulness of using PowerShell over repadmin.  In this example I take the output of Get-ADReplicationPartnerMetadata then passing it through Select-Object so that we can then limit what objects are presented in GridView.

Here we see the output of that command.

1.10             Replication Errors

Here is a list of replication errors you may come across in either the Directory Services event log or while running repadmin.

2              Troubleshooting Steps for Common Replication Issues

2.1                 Troubleshooting -21468930222  (The target principal name is incorrect)

On the DC that is the cause of the error, perform the following steps:

Step 1: Open Services.msc

Step 2: Configure KDC Service for Manual

Step 3: Stop the Service

Step 4: Restart the Domain Controller

Step 5: Open PowerShell as an Administrator

Step 6: Run: $cred = Get-Credential

Step 7: Enter Credentials and click OK

Step 8: Run, Reset-ComputerMachinePassword –Server <ServerName> -Credential $cred

Step 9: Restart the server

Step 10: Set the KDC service to Automatic, Start the service and click OK.

2.2                 Troubleshoot Replication Error 8606, Event ID 1388, and Event ID 1988

These issues are caused by lingering objects.  Lingering objects can be caused when a domain controller is taken offline for an extended period of time, does not replicate for longer than the tombstone lifetime, or is restored from a backup that is older than the tombstone lifetime. 

When an object is deleted it is put in a tombstone state.  After the tombstone lifetime passes (typically 180 days), DC run garbage collection and those tombstone objects are deleted.  If a DC was offline for the entire TSL and then were brought back online they may have objects that have since been deleted, tombstoned, and garbage collected.  Any objects that were deleted will still exist on that DC.  These objects go unnoticed until a change is made to that object then the DC attempts to replicate that object, and at that point that is where it is either re-introduced into the environment or if strict replication consistency is enabled, blocked. 

2.2.1   How to Determine TSL

Run the following command: dsquery * “cn=directory service,cn=windows nt,cn=services,cn=configuration,<Forest DN>” –scope base –attr tombstonelifetime

2.2.2   How to Remove Lingering Objects

2.2.2.1          Repadmin /removelingeringobjects

One way to remove lingering objects is to user repadmin with the /removelingeringobjects switch.  First you must identify a clean source of the partition.  The syntax of the command is repadmin /removelingeringobjects <Dest DC Name> <Source DC Guid> <Naming Context>.  So, in other words you need to identify the source DCs guid and the Naming Context you want to clean.  The naming context will be available in the Event 1388 or 1988 you receive in the event long.  Once you find a clean source you can obtain the guid by opening DNS Manager and opening up the _msdcs Zone and obtaining the CName record for the DC in question.

Below is an example of running the repadmin /removelingeringobjects command

You will receive an Event 1937 when the removal of lingering objects begins.

You will then receive an Event 1939 when removal completes.

2.2.2.2          Repadmin /rehost

An alternative to using repadmin /removelingeringobjects command is to unhost the partition so that the domain controller no longer has that partition and then rehosting the entire partition with a good source.

The repadmin syntax for unhosting the partition is repadmin /unhost <DC Name> <Partition Name>

You will receive an event an event 1658 when the removal begins.

You will receive an event 1660 when the removal completes

The syntax for rehosting the partition is: repadmin /rehost <Dest DC Name> <Partition> <Source DC Name>

2.3                 Troubleshooting Event ID 2042

Review event log for any 1988 or 1388 errors.  If found use the previous section to remove the lingering objects from the domain controller. 

Option 1: Re-hosting the partition that has not replicated

If the partition is a GC partition consider unhosting and rehosting the partition. Instructions for unhosting and rehosting are in the previous section called Repadmin /rehost

Option 2: Removing and then re-adding the domain controller to Active Directory

Another option is removing the DC from Active Directory and Re-promoting the Domain Controller

Step 1: Run Import-Module ADDSDeployment

Step 2: Run: Uninstall-ADDSDomainController –DemoteOperationMasterRole:$true –Force:$true

Step 3: Enter and confirm the new local password

Step 4:  Next you will need to run the Install –ADDSDomainController cmdlet.  Below is a sample that you can use.  You will need to modify the template to meet the requirements of your environment. 

Install-ADDSDomainController –NoGlobalCatalog:$false –CreateDnsDelegation:$false –CriticalReplicationOnly:$false –DatabasePath “C:\Windows\NTDS” –DomianName “fabrikam.com” –InstallDNS:$true –LogPath “C:\Windows\NTDS” –ReBootOnCompletion:$false –ReplicationSourceDC “DC01.fabrikam.com” –SiteName “Default-First-Site-Name” –SysvolPath “C:\Windows\SYSVOL” –Force:$true

Option 3: Enabling Replication with Divergent and Corrupt Partner

Due to the risk of adding lingering objects to Active Directory the final consideration should be enabling the following setting: Allow Replication With Divergent and Corrupt Partner. 

Step 1: To enable this setting run the following command on the domain controller:

repadmin /regkey <hostname> +allowDivergent

Step 2: Let replication complete

Step 3: Disable the setting with the following command: repadmin /regkey <hostname> -allowDivergent

2.4                 Troubleshooting Event ID 1311

Event 1311 is caused when there is not complete connectivity between domain controllers. There are a number of reasons there may not be complete connectivity. 

2.4.1   ISTG

The Inter-Site Topology Generator (ITSG) is responsible for building the replication topology.  So to determine what the scope of the connectivity issues it is important to identify the ISTGs that are logging 1311. 

To find the ISTGs in your environment you need to use ldp.exe

Below are the steps for locating the ISTGs:

Step 1: Launch ldp.exe

Step 2: When LDP opens, select Connection and then Connect…

Step 3: In the Connect dialog box, enter the name of a Domain Controller for the Server you want to connect to and then click OK

Step 4: Click on Connection and then click Bind…

Step 5: In the Bind dialog box, click OK

Step 6: Select the Browse menu and then select Search

Step 7: In the search enter the following:

Base DN: CN=Sites,CN=Configuration,<DN of Forest Root> (example: CN=Sites,CN=Configuration,DC=fabrikam,DC=com)

Filter: (CN=NTDS Site Settings)

Scope: Subtree

Attributes: Append the following to the attributes that are already listed: ;interSiteTopologyGenerator

Step 8: Click Run

Step 9: For each site you will then need to look for interSiteTopologyGenerator to determine the ITSG for each site.

2.4.2   BASL

By default, Bridge All Site Links (BASL) is enabled in Active Directory.  If your environment is not fully routed, then you will want to disable BASL.  By fully routed we mean each site can contact every other site.  If BASL is configured on a network which is not fully routed, the KCC will generate site bridges that cannot actually be reached. To determine if BASL is enabled launch Active Directory Sites and Services (dssite.msc). 

Expand Sites, then Inter-site Transports.

Right-click on IP and select Properties from the context menu

If Bridge all site links is enabled, there will be a check box next to it.  To disable BASL, uncheck the checkbox and click OK.

2.4.3   Site Link Bridges

If you disable BASL you can still bridge site links.  You would do that if you wanted two spoke sites to communicate directly if they could not communicate with the hub site.  In a hub and spoke configuration the cost of crossing to site links (bridging a site link) will typically be a higher then just connecting directly to the hub site.  So, ordinarily you would not have to worry about the Site Link Bridge being used instead of a direct site link.  That being said, there are not a whole lot of scenarios where you would need to create Site Link bridges.

The following steps will allow you to bridge two site links.

Step 1: Open the Active Directory Sites and Services MMC.

Step 2: Expand Sites and then expand Inter-site Transports

Step 3: Select New Site Link Bridge… from the context menu

Add at least two sites to the Site Link Bridge, give it a Name, and click OK

And the Site Link Bridge has been completed.

2.4.4   Verify that all Sites are in a Site Link

Step 1: Run the following command in a PowerShell Console: Get-ADObject –LDAPFilter ‘(objectClass=site)’ –SearchBase (Get-ADRootDSE).ConfigurationNamingContext –Property Name | Format-Table Name

Step 2: In another PowerShell Console run: Get-ADObject –LDAPFilter ‘(objectClass=sitelink)’ –SearchBase (Get-ADRootDSE).ConfigurationNamingContext –Property Name, Cost, Description, Sitelist | Format-List Name, Sitelist

Step 3: Verify that each site that was listed in Step 1 exists in one of the site lists returned in Step 2

If not all sites are contained in a site link that you need to determine what site link that site needs to be added to or if a new site link needs to be created.

And that is all I have for replication troubleshooting for today.

출처 : Troubleshooting Active Directory Replication – xdot509.blog

Windows Server 평가판 다운로드 Url

https://www.microsoft.com/ko-kr/evalcenter/evaluate-windows-server-2019?filetype=ISO

Function Copy-WithProgress
{
    [CmdletBinding()]
    Param
    (
        [Parameter(Mandatory=$true,
            ValueFromPipelineByPropertyName=$true,
            Position=0)]
        $Source,
        [Parameter(Mandatory=$true,
            ValueFromPipelineByPropertyName=$true,
            Position=0)]
        $Destination
    )

    $Source=$Source.tolower()
#    $Filelist=Get-Childitem "$Source" –Recurse   -- 대상 폴더안의 파일 및 폴더 전체를 리스트함.
    $Filelist=Get-Childitem 파일명
    $Total=$Filelist.count
    $Position=0


    foreach ($File in $Filelist)
    {
        $Filename=$File.Fullname.tolower().replace($Source,'')
        #$Filename= 파일명
        $DestinationFile=($Destination+$Filename)
        Write-Progress -Activity "Copying data from '$source' to '$Destination'" -Status "Copying File $Filename" -PercentComplete (($Position/$total)*100)
        Copy-Item $File.FullName -Destination $DestinationFile
        $Position++
    }
}

$src="c:\temp"
$dest="C:\Temp\download"

Copy-WithProgress -Source $src -Destination $dest

#복사 완료 후 제거 
Remove-Item c:\temp\sw_dvd5_office_professional_plus_2016_64bit_korean_mlf.iso -Force  

MSSQL에서는 날짜를 여러가지형태로 가공할 수 있는 함수들을 많이 제공하고 있습니다.

이번 포스팅에서는 대표적인 날짜함수(GETDATE,DATEADD,DATEPART,DATEDIFF)의 사용법에 대해서 알아보도록 하겠습니다.

GETDATE 

GETDATE함수는 현재 컴퓨터에 설정되어있는 시스템 시간을 불러와주는 함수입니다.

2018-03-31 오전 01:40:30 

위와같이 GETDATE()함수는 년월일은 물론이거니와 분,초 까지도 구해줍니다.

하지만 모든 사용자들이 위와같은 형식으로 사용하지는 않겠죠.

여기서 우리는 우리가 표시할 시간이나 날짜만 적절히 뽑아 올 수도 있고

날짜 형태를 바꿔서 출력할수도 있습니다.

사용법

--현재 날짜 출력--

SELECT GETDATE() AS 시스템일자

--현재 날짜의 연,월,일 출력--

SELECT YEAR(GETDATE()) AS 년,

MONTH(GETDATE()) AS 월,

DAY(GETDATE()) AS 일

출력 포맷 변경

--YYYY/MM/DD--

Select Convert(varchar(10),Getdate(),111)

--YYYYMMDD--

Select Convert(varchar(10),Getdate(),112)

--HH:MM:SS--

Select Convert(varchar(8),Getdate(),108)

--HH:MM:SS:mmm--

Select Convert(varchar(12),Getdate(),114)

--HHMMSS--

Select Replace(Convert(varchar(8),Getdate(),108),':','')

--HHMMSSmmm--

Select Replace(Convert(varchar(12),Getdate(),114),':','')

--YYYY/MM/DD HH:MM:SS--

Select Replace(Convert(varchar(30),Getdate(),120),'-','/')

--YYYY/MM/DD HH:MM:SS--

Select Replace(Convert(varchar(30),Getdate(),121),'-','/')

--YYYY/MM/DD HH:MM:SS--

Select Convert(varchar(10),Getdate(),111) + Space(1) + Convert(varchar(8),Getdate(),108)

--YYYYMMDDHHMMSS--

Select Convert(varchar(10),Getdate(),112) + Replace(Convert(varchar(8),Getdate(),108),':','')

DATEADD 

DATEADD함수는 날짜에 원하는 일수를 더해서 출력해주는 함수입니다.

100일뒤에는 몇일이다 이런것은 쉽게 계산하기 힘든데

DATEADD함수를 쓰면 이런것들을 편리하게 계산할 수 있습니다.

이 함수를 좀 더 응용하면 100일 뒤에는 무슨 요일이다 이렇게도 응용가능합니다.

사용법

--2개월 후 출력--

SELECT DATEADD(MM,2,GETDATE()) AS '2개월후'

--30일전 출력--

SELECT CONVERT(NVARCHAR(8),DATEADD(DAY,-30,'20180124'),112)AS '30일전'

--1달 뒤 요일계산

SELECT DATENAME(WEEKDAY,DATEADD(MM,1,GETDATE())) AS'요일계산'

DATEPART 

DATEPART함수는 날짜에서 지정한 날짜형식의 부분만 출력해주는 함수입니다.

아주 다양하게 활용이 가능한 함수입니다.

사용법

--현재 년도 구하기

SELECT DATEPART(YEAR,GETDATE());

--현재 월 구하기--

SELECT DATEPART(MONTH,GETDATE())

--현재 일 구하기--

SELECT DATEPART(DAY,GETDATE())

--현재 분기 구하기--

SELECT DATEPART(QQ,GETDATE())

--올해의 몇번째 날인지 구하기--

SELECT DATEPART(DAYOFYEAR,GETDATE())

--올해의 몇째 주인지 구하기--

SELECT DATEPART(WEEK,GETDATE())

--이번주의 몇번째 날인지 구하기--

SELECT DATEPART(WEEKDAY,GETDATE())

--오늘이 무슨요일인지 구하기--

SELECT

CASE DATEPART(WEEKDAY, GETDATE())

WHEN '1' THEN '일요일'

WHEN '2' THEN '월요일'

WHEN '3' THEN '화요일'

WHEN '4' THEN '수요일'

WHEN '5' THEN '목요일'

WHEN '6' THEN '금요일'

ELSE '토요일'

END AS '요일'

DATEDIFF 

DATEDIFF함수는 지정한 두 날자간의 간격을 계산해주는 함수입니다.

정해준 날짜형식에 맞춰 리턴값이 다르므로 적절하게 잘 설정해주셔야 합니다.

사용법

--지정일과의 현재와의 년도차이 계산--

SELECT DATEDIFF(YY,'2000-01-01',GETDATE())

--지정일과의 현재와의 월차이 계산--

SELECT DATEDIFF(MM,'2000-01-01',GETDATE())

--지정일과 현재와의 일차이 계산--

SELECT DATEDIFF(DD,'2000-01-01',GETDATE())

데이터베이스를 사용하다보면 테이블안에있는 데이터의 특정 문자들만 치환 해줘야 하는 일이 생깁니다. 이번 포스팅에서는 특정 문자열을 바꿔주는 REPLACE함수와 STUFF함수에 대해서 알아보도록 하겠습니다.

REPLACE

지정된 문자열 값을 특정 문자열로 바꿔주는 함수입니다.

사용법

--문법--

REPLACE('문자열','치환예정문자','치환할문자')

--예시--

REPLACE('ABCDEFG','DEF','XXX')

예제

--MY_TABLE에서 이름(NM_KOR)을 이씨를 김씨으로 바꿔서 출력--

SELECT REPLACE(NM_KOR,'이','김')AS 사원명 FROM MY_TABLE

STUFF

지정된 문자열의 시작위치와 크기를 지정하여 원하는 문자로 치환하는 함수입니다.

사용법

--문법--

STUFF('문자열','시작위치','크기','치환할문자')

--예시--

STUFF('ABCDEFG',2,3,'XXX')

예제

--MY_TABLE에서 이름(NM_KOR)칼럼의 이순신을 이성계로 바꿔서 출력--

SELECT STUFF(NM_KOR,2,2,'성계')AS 사원명 FROM MY_TABLE

데이터베이스를 사용하다보면 테이블안에있는 데이터의 특정 문자들만 치환 해줘야 하는 일이 생깁니다. 이번 포스팅에서는 특정 문자열을 바꿔주는 REPLACE함수와 STUFF함수에 대해서 알아보도록 하겠습니다.

REPLACE

지정된 문자열 값을 특정 문자열로 바꿔주는 함수입니다.

사용법

--문법--

REPLACE('문자열','치환예정문자','치환할문자')

--예시--

REPLACE('ABCDEFG','DEF','XXX')

예제

--MY_TABLE에서 이름(NM_KOR)을 이씨를 김씨으로 바꿔서 출력--

SELECT REPLACE(NM_KOR,'이','김')AS 사원명 FROM MY_TABLE

STUFF

지정된 문자열의 시작위치와 크기를 지정하여 원하는 문자로 치환하는 함수입니다.

사용법

--문법--

STUFF('문자열','시작위치','크기','치환할문자')

--예시--

STUFF('ABCDEFG',2,3,'XXX')

예제

--MY_TABLE에서 이름(NM_KOR)칼럼의 이순신을 이성계로 바꿔서 출력--

SELECT STUFF(NM_KOR,2,2,'성계')AS 사원명 FROM MY_TABLE

LEFT 

Left함수는 문자열을 받아서 왼쪽부터 원하는 길이만큼 자르는 함수이며

주민등록번호만으로도 생년월일을 구하거나 이름을 잘라서 성만 출력하는 등

사용법

--문법--

LEFT(문자열,길이)

--예시--

LEFT(NAME,2)

예제

--테이블(MY_TABLE)에서 이름(Name)을 잘라 성만 출력--

SELECT LEFT(Name,1) AS 이름 FROM MY_TABLE

RIGHT

RIGHT함수는 LEFT함수와 기능은 같지만 방향만 다른 함수입니다.

RIGHT함수는 문자열을 받아서 오른쪽부터 원하는 길이만큼 자르는 함수이며

LEFT함수와 마찬가지로 다양하게 사용이 가능합니다.

사용법

--문법--

RIGHT(문자열,길이)

--예시--

RIGHT(NAME,3)

예제

--테이블(MY_TABLE)에서 이름(NM_KOR)을 잘라 이름만 출력--

SELECT RIGHT(Name,2) AS 이름 FROM MY_TABLE

SUBSTRING

SubString함수의 기능은 문자열을 받아서 일정한 영역만큼 잘라낸 후 리턴하는 기능을 가지고 있습니다.

주민등록번호만으로도 성별을 잘라서 활용하거나 날짜를 잘라서 월별로 그룹을 만드는등

다양한 방법으로 활용 가능합니다.

정말 많이쓰이는 문자열함수 중 하나입니다.

사용법

--문법--

SUBSTRING(문자열,시작자리번호,자를문자수)

--예시--

SUBSTRING(resident_number,0,6)

예제

--테이블(MY_TABLE)에서 이름 2번째자리에서 2개만 잘라서 출력--

SELECT SUBSTRING(Name,2,2) AS 이름 FROM MY_TABLE

--테이블(MY_TABLE)에서 날짜(DT)를 잘라 0000년00월00일 형식으로 만들기--

SELECT SUBSTRING(DT,1,4)+'년'+SUBSTRING(DT,5,2)+'월'+SUBSTRING(DT,7,2)+'일' AS일자 FROM MY_TABLE

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p
inner join sysdatabases d on p.dbid = d.dbid
where d.name = 'DB_Name'

or

exec sp_who2

kill spid

alter database DB_Name set multi_user;

alter database DB_Name set sigle_user;

$user = Import-csv C:\temp\sid.csv

$user | foreach{Get-ADUser -Identity $_.samaccountname -Properties * | Select-Object samaccountname, SID} | Export-Csv C:\temp\s_sid.csv -Delimiter "|" -Encoding Unicode -NoTypeInformation

or 

dsquery * -filter “&(objectcategory=user)(samaccountname=계정)”-attr objectsid sIDHistory