Windows PowerShell (파워셸)에서 SQL 서버 데이터베이스에 연결하는 방법

파워셸 (PowerShell)은 데이터 분석이나 오케스트레이션에 사용되는 용이한 툴입니다.  그러나 데이터는 다른 곳에서 와야 하죠.  파워셸은 아주 많은 곳에 사용될 수 있습니다.  예를 들면 새 직원 온보딩 (onboarding) 프로세스의 자동화이죠.  그리고 리포팅 용에도 사용이 됩니다.

 

이 포스팅은 다음의 세가지 포스팅과 연결되어 있습니다.

 

1. 간단한 SQL 서버 데이터베이스 구축하기 (https://ranku.tistory.com/487)
2. Windows PowerShell (파워셸)에서 SQL 서버 데이터베이스에 연결하는 방법 (https://ranku.tistory.com/486)
3. Windows PowerShell (파워셸)에서 SQL 서버 데이터를 필터링하는 방법 (https://ranku.tistory.com/485)

 

 

이 포스팅에서는 두 번째 포스팅으로 파워셸을 이용하여 구축한 데이터베이스에 연결하기를 알아보겠습니다

 

파워셸에서 SQL 서버에 연결하는 방법은 어려웠던 방법이었습니다.  .NET의 변화로 아주 많은 성장을 거쳤었고 마이크로소프트사에서 파워셸용으로 Invoke-SQLCmd라는 cmdlet을 선보인 후로 아주 많이 간단해졌습니다.

 

728x90

---

1: 원격 커넥션 허용하기

 

파워셸로 데이터베이스에 연결하기 전에, 다음의 방법으로 SQL 서버에서 원격 커넥션을 허용하여야 합니다.  

 

1. SQL Server Management Studio (SSMS)를 오픈
2. Object Explorer에 보이는 서버 이름에 오른쪽 마우스 버튼을 클릭
3. 메뉴에서 Properties (속성)을 클릭.  속성 스크린이 오픈됨.
4. Connections 탭
5. “Allow Remote Connections to this Server” 체크박스를 체크함
6. OK 버튼 클릭

---

2: 파워셸 모듈을 설치하기

 

첫 포스팅에서 구축된 데이터베이스에 파워셸에서 연결을 하려면 SQL Server 모듈을 설치하여야 합니다.  만약에 SQL 서버가 파워셸을 사용하는 컴퓨터에 설치되어 있으면 이 모듈은 자동으로 설치되어 있습니다.

 

모듈을 설치하는 방법:

1. 파워셸을 관리자모드로 오픈
2. Install-Module SQLServer 실행

 

 

물어보는 프롬프트에 y로 답을 하면 설치가 됩니다.  위의 이미지의 오류 메시지는 모듈이 설치가 되어 있었기 때문입니다.

 

그리고 OS와 SQL Server가 어디에서 구동을 하는지에 따라서 SQLPS라는 모듈의 설치가 필요할 수도 있습니다.  그럴 경우에는 다음의 커맨드를 입력하면 됩니다.

 

Import-Module SQLPS

 

오류 메시지가 만약에 뜬다면 아래의 이미지와 같이 시스템의 execution policy의 이유일지도 모릅니다.

 

 

그럴 시에는 위의 이미지와 같이 아래의 커맨드를 입력하고 다시 설치를 시도합니다:

 

Set-ExecutionPolicy Unrestricted

---

3: SQL 쿼리 실행하기

 

파워셸에 SQL Server 모듈이 설치가 되었으므로 SQL 서버에 연결을 할 수 있습니다.  이전에는 connection string을 셋업하고 사용하여 SQL 서버에 연결을 하였지만 이제는 Invoke-SQLCmd의 커맨드에 전부 넣어 사용할 수 있습니다.

 

다음의 커맨드 수식을 사용하겠습니다

 

$Data = Invoke-SQLCmd -ServerInstance [서버인스턴스 이름] -Database [데이터베이스이름] -Query “[쿼리]”

 

$Data는 파워셸의 변수이고 Invoke-SQLCmd에서 반환되는 데이터가 저장이 됩니다.

 

이 커맨드를 이용하는 데에는 아래의 주의점을 인지하고 있어야 합니다:

 

1. 원격 인스턴스 계정.  만약에 SQL 서버 인스턴스가 같은 컴퓨터가 아니고 다른 장소이면 그 인스턴스의 로그인 계정이 필요합니다.  Invoke-SQLCmd는 Username과 Password가 포함된 credentials (계정정보)를 파라미터 형식으로 보낼 수 있습니다.  더 자세한 정보는 Get-Help Invoke-SQLCmd 커맨드를 이용하면 됩니다.
2. 인스턴스 이름 포맷.  인스턴스의 이름이 다를 수 있습니다.  첫 포스팅 (SQL 서버 구축)을 보면 마지막 Summary 스크린에 SQLEXPRESS라는 인스턴스 이름이 있죠.  하지만 파워셸에서 이 이름을 사용하면 오류가 생깁니다.  인스턴스 이름의 포맷은 컴퓨터 이름\SQL 서버 인스턴스 이름입니다.  이 예제에 사용되는 인스턴스이름은 아래의 이미지와 같이 Win11 SQL\SQLEXPRESS입니다.

 

 

그러면 아래와 같은 예제 커맨드를 입력하게 되는 것이죠:

 

$Data = Invoke-SQLCmd -ServerInstance Win11SQL\SQLEXPRESS -Database MyAppDB -Query “Select Month, ItemsSold From Table1”

 

반응형

---

4: SQL 서버에 데이터 저장하기

 

파워셸을 이용하여 데이터를 저장해 보겠습니다.  다른 점은 Select라는 SQL 쿼리 대신에 Insert Into라는 쿼리를 사용하게 되죠.  이 예제에는 July라는 Month에 ItensSold라는 칼럼에 35라는 수를 넣어 보겠습니다.  

 

다음의 커맨드가 사용이 됩니다:

 

$Data=Invoke-SQLCmd -ServerInstance Win11SQL\SQLEXPRESS -Database MyAppDB -Query “Insert Into Table1 (Month, ItemsSold) Values (‘July’,35)”

 

쿼리문은 Insert Into Table1 (Month, ItemsSold) Values (‘July’,35) 이므로 Table1이라는 테이블에  Month가 July이고 ItemsSold가 35인 새로운 행을 넣게 되는 것이죠.

 

 

위의 이미지와 같이 Insert Into를 이용한 커맨드를 입력하고 Select 커맨드를 입력하여 $Data 변수에 저장된 그 결과 값을 불러보면 35가 저장된 July라는 행이 생성이 되어 있는 것을 볼 수 있습니다.

 

파워셸 커맨드의 쿼리문에는 변수에 값을 지정하여 쿼리에 넣는 방법도 사용할 수 있습니다.

 

다음의 포스팅에는 데이터를 불러와서 필터링을 하는 방법을 알아보겠습니다.

 

---

유익하거나 즐거우셨다면 아래의 ❤️공감 버튼이나 구독 (SUBSCRIBE) 버튼을 눌러 주세요~ 

감사합니다

 

 

참고:

itprotoday.com