2018年10月21日 星期日

SQL Server 管理員專用連線(DAC)

SQL Server 管理員專用連線(DAC)

env: all SQL Server

Dedicated Administrator Connection (DAC) : 管理員專用連線
 1.預設只偵聽127.0.0.1上的1434
 2.sqlcmd連接
 command:請自行替換"ServerName"
 SQLCMD -E -S ServerName -A

 3.使用SSMS
 在 ServerName輸入"admin:ServerName\InstanceName"

 DAC連線錯誤:
 A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

原因: 因為DAC允許一個連線,當下可能已經有其他連線。此錯誤也可以從SQL Server Errorlog看到。

開啟遠端DAC:
sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO




2018年10月14日 星期日

SQL Server connect to oracle 11g via linkedserver

SQL Server connect to oracle 11g via linkedserver

env: Windows Server 2012
        SQL Server 2012
        Oracle Enterprise Linux 6
        Oracle 11g


1.下載odac(xcopy edition)到SQL Server主機

2.建立以下路徑
1).c:\oracle\odac64
2).c:\oracle\odac64\bin

3.設定tnsnames.ora
1).建立以下路徑,C:\oracle\odac64\network\admin
2).建立檔案tnsnames.ora
3).在檔案tnsnames.ora輸入以下內容

服務名稱可以自訂,ip請以自己的Oracle主機為主。
服務名稱一:fa0test ip:100.168.114.120
服務名稱二:fa0prd01 ip:192.168.125.214

範本:
# tnsnames.ora Network Configuration File: /oracle/product/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
fa0test =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 100.168.114.120 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = fa0test)
    )
  )
fa0prd01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.214 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = fa0prd01)
    )
  )

 4.設定Windows環境變數
 請以自己的環境內容為主,並做調整

調整前環境變數內容:
%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\IBM\Client Access\Emulator;C:\Program Files (x86)\IBM\Client Access\Shared;C:\Program Files (x86)\IBM\Client Access\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\;C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\

調整後環境變數內容:
增加此變數"c:\oracle\odac64;c:\oracle\odac64\bin;"



c:\oracle\odac64;c:\oracle\odac64\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\IBM\Client Access\Emulator;C:\Program Files (x86)\IBM\Client Access\Shared;C:\Program Files (x86)\IBM\Client Access\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\;C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\

5.Reboot Server

6.確認設定是否成功
在llinkedserver要有OraOLEDB.Oracle





















7.設定OraOLEDB.Oracle
保留"允許 Inprocess"

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO













8.設定Linkedserver
輸入linkedserver name
選擇"Oracle Provider for OLE DB"
並輸入TNSNAMES裡面設定的服務名稱











9.使用OPENQUERY對Oracle的表進行查詢。

SELECT * FROM OPENQUERY(FA0TEST,'SELECT * FROM TFP1U1.TB_M1_COUNT')


Install Docker in CentOS7

Install Docker in CentOS7

env: CentOS7

參考來源:
https://docs.docker.com/install/linux/docker-ce/centos/#install-using-the-repository

1.準備CentOS7,並且與外部網路相通

2.使用yum安裝所需要的packages
因為package再安裝CentOS7時已經安裝,所以最後出現Nothing to do
[root@localhost cdrom]# sudo yum install -y yum-utils \
>   device-mapper-persistent-data \
>   lvm2
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: linux.cs.nctu.edu.tw
 * extras: linux.cs.nctu.edu.tw
 * updates: linux.cs.nctu.edu.tw
Package yum-utils-1.1.31-45.el7.noarch already installed and latest version
Package device-mapper-persistent-data-0.7.3-3.el7.x86_64 already installed and latest version
Package 7:lvm2-2.02.177-4.el7.x86_64 already installed and latest version
Nothing to do

3.設定repo, 之後要透過此設定來下載docker與安裝docker
[root@localhost cdrom]# sudo yum-config-manager \
>     --add-repo \
>     https://download.docker.com/linux/centos/docker-ce.repo
Loaded plugins: fastestmirror, langpacks
adding repo from: https://download.docker.com/linux/centos/docker-ce.repo
grabbing file https://download.docker.com/linux/centos/docker-ce.repo to /etc/yum.repos.d/docker-ce.repo
repo saved to /etc/yum.repos.d/docker-ce.repo

4.啟用名稱為docker-ce-test repositories
[root@localhost cdrom]# sudo yum-config-manager --enable docker-ce-test
Loaded plugins: fastestmirror, langpacks
============================= repo: docker-ce-test =============================
[docker-ce-test]
async = True
bandwidth = 0
base_persistdir = /var/lib/yum/repos/x86_64/7
baseurl = https://download.docker.com/linux/centos/7/x86_64/test
cache = 0
cachedir = /var/cache/yum/x86_64/7/docker-ce-test
check_config_file_age = True
compare_providers_priority = 80
cost = 1000
deltarpm_metadata_percentage = 100
deltarpm_percentage =
enabled = 1
enablegroups = True
exclude =
failovermethod = priority
ftp_disable_epsv = False
gpgcadir = /var/lib/yum/repos/x86_64/7/docker-ce-test/gpgcadir
gpgcakey =
gpgcheck = True
gpgdir = /var/lib/yum/repos/x86_64/7/docker-ce-test/gpgdir
gpgkey = https://download.docker.com/linux/centos/gpg
hdrdir = /var/cache/yum/x86_64/7/docker-ce-test/headers
http_caching = all
includepkgs =
ip_resolve =
keepalive = True
keepcache = False
mddownloadpolicy = sqlite
mdpolicy = group:small
mediaid =
metadata_expire = 21600
metadata_expire_filter = read-only:present
metalink =
minrate = 0
mirrorlist =
mirrorlist_expire = 86400
name = Docker CE Test - x86_64
old_base_cache_dir =
password =
persistdir = /var/lib/yum/repos/x86_64/7/docker-ce-test
pkgdir = /var/cache/yum/x86_64/7/docker-ce-test/packages
proxy = False
proxy_dict =
proxy_password =
proxy_username =
repo_gpgcheck = False
retries = 10
skip_if_unavailable = False
ssl_check_cert_permissions = True
sslcacert =
sslclientcert =
sslclientkey =
sslverify = True
throttle = 0
timeout = 30.0
ui_id = docker-ce-test/x86_64
ui_repoid_vars = releasever,
   basearch
username =

5.透過yum下載docker並安裝docker。
這裡安裝的是社群板。
[root@localhost cdrom]# sudo yum install docker-ce
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: linux.cs.nctu.edu.tw
 * extras: linux.cs.nctu.edu.tw
 * updates: linux.cs.nctu.edu.tw
docker-ce-stable                                                             | 2.9 kB  00:00:00    
docker-ce-test                                                               | 2.9 kB  00:00:00    
(1/2): docker-ce-stable/x86_64/primary_db                                    |  13 kB  00:00:00    
(2/2): docker-ce-test/x86_64/primary_db                                      |  47 kB  00:00:00    
Resolving Dependencies
--> Running transaction check
---> Package docker-ce.x86_64 0:18.05.0.ce-3.el7.centos will be installed
--> Processing Dependency: container-selinux >= 2.9 for package: docker-ce-18.05.0.ce-3.el7.centos.x86_64
--> Processing Dependency: pigz for package: docker-ce-18.05.0.ce-3.el7.centos.x86_64
--> Running transaction check
---> Package container-selinux.noarch 2:2.55-1.el7 will be installed
---> Package pigz.x86_64 0:2.3.3-1.el7.centos will be installed
--> Finished Dependency Resolution
Dependencies Resolved
====================================================================================================
 Package                  Arch          Version                         Repository             Size
====================================================================================================
Installing:
 docker-ce                x86_64        18.05.0.ce-3.el7.centos         docker-ce-test         35 M
Installing for dependencies:
 container-selinux        noarch        2:2.55-1.el7                    extras                 34 k
 pigz                     x86_64        2.3.3-1.el7.centos              extras                 68 k
Transaction Summary
====================================================================================================
Install  1 Package (+2 Dependent packages)
Total download size: 35 M
Installed size: 151 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7/extras/packages/container-selinux-2.55-1.el7.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Public key for container-selinux-2.55-1.el7.noarch.rpm is not installed
(1/3): container-selinux-2.55-1.el7.noarch.rpm                               |  34 kB  00:00:00    
(2/3): pigz-2.3.3-1.el7.centos.x86_64.rpm                                    |  68 kB  00:00:00    
warning: /var/cache/yum/x86_64/7/docker-ce-test/packages/docker-ce-18.05.0.ce-3.el7.centos.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID 621e9f35: NOKEY
Public key for docker-ce-18.05.0.ce-3.el7.centos.x86_64.rpm is not installed
(3/3): docker-ce-18.05.0.ce-3.el7.centos.x86_64.rpm                          |  35 MB  00:00:05    
----------------------------------------------------------------------------------------------------
Total                                                               6.2 MB/s |  35 MB  00:00:05    
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Importing GPG key 0xF4A80EB5:
 Userid     : "CentOS-7 Key (CentOS 7 Official Signing Key) <security@centos.org>"
 Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5
 Package    : centos-release-7-5.1804.el7.centos.x86_64 (@anaconda)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Is this ok [y/N]: y
Retrieving key from https://download.docker.com/linux/centos/gpg
Importing GPG key 0x621E9F35:
 Userid     : "Docker Release (CE rpm) <docker@docker.com>"
 Fingerprint: 060a 61c5 1b55 8a7f 742b 77aa c52f eb6b 621e 9f35
 From       : https://download.docker.com/linux/centos/gpg
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pigz-2.3.3-1.el7.centos.x86_64                                                   1/3
  Installing : 2:container-selinux-2.55-1.el7.noarch                                            2/3
  Installing : docker-ce-18.05.0.ce-3.el7.centos.x86_64                                         3/3
  Verifying  : 2:container-selinux-2.55-1.el7.noarch                                            1/3
  Verifying  : docker-ce-18.05.0.ce-3.el7.centos.x86_64                                         2/3
  Verifying  : pigz-2.3.3-1.el7.centos.x86_64                                                   3/3
Installed:
  docker-ce.x86_64 0:18.05.0.ce-3.el7.centos                                                       
Dependency Installed:
  container-selinux.noarch 2:2.55-1.el7               pigz.x86_64 0:2.3.3-1.el7.centos             
Complete!

6.啟動docker
[root@localhost cdrom]# sudo systemctl start docker

7.測試驗證docker。建立一個名為hello-world的container
[root@localhost cdrom]# sudo docker run hello-world
Unable to find image 'hello-world:latest' locally
latest: Pulling from library/hello-world
9bb5a5d4561a: Pull complete
Digest: sha256:f5233545e43561214ca4891fd1157e1c3c563316ed8e237750d59bde73361e77
Status: Downloaded newer image for hello-world:latest