Microsoft SQL Serverに挑戦してみる⑨他のPCから接続設定2

前回の残タスク

  • 別のPCからのDBへの接続
  • SQL Server BrowserServiceを使用した接続

に早速挑戦。

 

 

別のPCからのDBへの接続

 

MSサイトでは別のPCにもManagement Studioをインストールしてあることが

前提となっている。

 

でも別のPCではMSSQLもManagement Studioもインストールしていないし

今後も利用するつもりがない。

 

Microsoft SQL Server

　　　↑　　↓

　　　↑　　↓

Microsoft Access

 

な感じで基本Accessで更新追加を行うつもり。

 

で、AccessとMSSQLは同じMicrosoftとは言っても

互換性があるわけではない。

 

ODBCというAPIが必要。

 

ODBCってなんやねんという場合はここのサイト様がわかりやすく

噛み砕いている。

  • AccessからMSSQLへ接続するためのツール。
  • ODBCにMSSQLのサーバー情報を設定するとAccessからもデータを触れる。

私みたいな素人軍団が難しいこと考えずに設定できる便利ツール。

これがあればデータが触れるということは、接続確認にも使えるはず。

 

 

ODBC設定

 

ということで接続確認のためにODBC接続の設定を行う。

 

ODBC側でMSSQLの設定を保存しておくことで

ODBC設定を意識しなくてもAccess→MSSQL接続ができるらしい。

 

コントロールパネル

└管理ツール

　　└データ ソース (ODBC)

 

が入っていたので、このショートカットをダブルクリックで開く。

 

ODBCデータソースアドミニストレーターが開くので、

ユーザーDSNの追加をクリック。

 

 

データソースの新規作成画面が開く。

Microsoft SQL Serverの設定を保存したいので、SQL Serverを選択して

完了をクリック。

 

 

SQL Serverに接続するためのデータソースを作成する画面が開くので

名前と説明は自分がわかりやすいものを適当にセットして良さげ。

 

サーバーはちゃんと指定しないと駄目っぽい。

ManagementStudio起動で調べた【表示されている名前のルール】の①が

サーバー名っぽかったので、これを今回はセットして次へ。

 

 

ログインIDの権限画面が表示される。

 

ネットワークへのログインIDで、WindowsNTの認証メカニズムを使う？？

ユーザーが入力するSQL Server用のログインIDとパスワードを使う？？

 

ネットワークへのログインID何それ。

そんなもの設定していない。

 

とりあえずどっちがいいのか色々先生に確認したら、有用な情報が出てきた。

SQL-ServerのWindows認証にMicrosoftアカウントで接続できません。

⇒ドメインを構成してない環境で、クライアントPCからサーバー上の
SQLに接続しようとしているということででしょうか？ だとしたら不可能です。 

はっきりと不可能と言っていただけていて、とても助かる。
ドメイン構成とか、わからないので今回はWindows認証は諦める。

ってことは、
SQL Server用のログインIDとパスワードを設定すれば接続できそう。

SQL Server Express にリモート接続を参考にさせていただき、
サーバー側でsaアカウントを有効に設定。

 

これでODBC設定に戻って、

  • ユーザーが入力するSQL Server用のログインIDとパスワードを使う

を選択し、

  • SQL Server に接続して追加の構成オプションの既定設定を取得する

のチェックボックスをONにして、sa認証で設定したログインIDとパスワードを

入力して次へ。

 

次へを押してもサクッと画面が遷移しない。

 

？？？？

って思っていたらエラーメッセージ発生。

 

すんなり次の画面に行くこともあるけれど、

どうやらTCP/IPの設定かサーバー名のどちらかが間違っているときに

発生するメッセージみたい。

 

sa認証を設定した画面にクライアントの設定ボタンがあるのでクリック。

ネットワークライブラリ設定の編集画面が開く。

 

ここでTCP/IPのポートを動的に決定するチェックボックスをOFFにして

ポート番号1433を設定してOK。

これでスムーズに次の画面に遷移するようになる。

 

 

既定のデータベースを以下のものに変更するのチェックボックスをON。

 

以前作成したデータベース【Tigers】を選択して次へ。

 

この画面では何も変更せずに完了。

 

 

以下の設定でデータソース作成しますという画面が表示するので

画面左下のデータソースのテストをクリック。

 

 

テストは無事に完了しました。と表示されればODBC設定は完了。

 

ODBC設定がうまくいったということは

これで別のPCで設定したMSSQLとAccessが繋がるということに！

 

別のPCから接続できた。感動。

でも、この設定はあくまでローカルネットワーク上のPC同士の接続。

 

怖がってファイアウォールのプロファイルをプライベートにしてしまったので
別のネットワークからはまだ繋げない。

 

別のネットワークでもいけるのか？を次回は検証！！

 

 

Microsoft SQL Serverに挑戦してみる⑧他のPCから接続設定１

本当に接続できるのかを実践する日がついにやってきた。
っていうか本当に接続できるんだよね？


初期状態の確認

他のPCから接続できるのは、とてもありがたい状況だけれど

設定を間違えちゃうと無関係なPCからも接続できてしまうかなり危険な状態。

なのでインストールした状態では他のPCからのアクセスは制御

されているらしい。安心安全Microsoft。

では何をするとアクセスできるようになるのかを
このMSサイトで確認してみる。

  • プロトコルの有効化
  • 固定ポートの構成
  • ファイアウォールでポートを開く
  • 別のPCからのDBへの接続
  • SQL Server BrowserServiceを使用した接続

このとおりやればきっと接続できると信じて、

このレッスンのとおりにやってみる。


プロトコルの有効化

よく聴くフレーズだけれどプロトコルがなんなのかが
いまいちわからないので、まずそこから調べてみる。

※プロトコルとは

 

こちらの素敵なサイトを要約すると

データ通信の手順とかルール＝プロトコル
らしい。


で、プロトコル（手順とかルール）の有効化って何なのか。
MSサイトに書いてある内容をもう一度読んでみる。

------------------------------------------------------
Management Studio は、共有メモリ プロトコルを使用して

データベース エンジンに接続します。

 このプロトコルは既に有効になっています。

別のコンピューターから データベース エンジンに接続する場合は、

TCP/IP などのプロトコルを有効にする必要があります。
------------------------------------------------------

自分のPCだけで完結する処理用の共有プロトコルは

最初から有効になっているけれど

他のPCとの接続に使うプロトコルは有効じゃないから

有効にしましょうっていうことらしい。

よく聴くフレーズだけどよくわかっていない

 TCP/IP 接続についても、先ほどの素敵サイトを確認。


※ TCP/IP 接続とは


TCPはデータを伝えるときの手順とかルール。
IPはネットワーク間の手順とかルール。

らしいけど漠然としすぎててわからない。。。
 

更にかみ砕くと、

 

IPは何も気にせずデータを送受信する

気にしないから高速でデータ送受信ができる。

 

TCPはIPが送受信したデータを確認して間違っていないか

チェックしてから送受信する。

 

みたいな感じかも。

なので、TCP/IP などのプロトコルを有効にすることで

別のPCとの接続をしてくれるし、無関係なPCからの接続を

遮断してくれるって覚えておけば良さげ。

 


他のPCからの TCP/IP 接続を有効にする

有効にしてみないと話が始まらないようなので実際に操作。

プロトコルを有効化しただけでは他のPCから接続できないみたいなので

とりあえず実践してもOK。

 

MSSQLをインストールした時に一緒にインストールされたソフトで

設定できるらしい。

 

すべてのプログラム

└Microsoft SQL Server 2014

　 └構成ツール

 

の中にある【SQL Server 2014 構成マネージャー】を開く。

 

インストールしたときに一番最後にみたやつ。

早速使う日が来るとは。。

 

設定は簡単。

SQL Serverネットワークの構成の横の＋マークをクリックして表示される

MSSQLSERVERのプロトコルを選択。

 

画面のように一番下にTCP/IPが表示されたら選択して右クリックで

メニューを表示して、有効化を選ぶだけ。

 

 

有効化を選ぶとこんな警告表示。

この画面からサービスを再起動するまでは有効にならないということらしい。

そして再起動は次のステップで結局やるみたいなので今やらなくてOK。

 

 

固定ポートの構成

 

構成の前にそもそもポートってなんなのかをわかっておいたほうが良さげ。

なのでまたザッと調べてみた。

 

※ポートとは

 

ポート＝データの通り道。

PCにはいろんなデータの通り道がある。

ポートはセキュリティ強化のために普段は閉じている。

 

他のPCと通信するにはポートを開放しないといけない。

開放しないといけないけれど、誰でも通していいわけではない。

もちろん、必要なポート以外を開放してはいけない。

 

ざっくり説明だとこんな感じ。

だから今回はMSSQLのデータの通り道を開放して

他のPCと通信する。

 

 

MSSQLのポート構成

 

各ソフトのポートには番号が割り振られている（固定されている）。
DBエンジンの既定のインスタンスのポート番号は1433。

MSSQLをインストールした時に名前付きインスタンスで設定した場合は

1433ではないので特定のポートを開放するように設定する必要があるらしい。

これをしないと、起動のたびに適当なポート番号を開放してしまう。

どこが開いているかわからないと危険だし怖いから番号を指定するらしい。

今回は既定のインスタンスでインストールしたので、ここは割愛。
固定ポートで番号が1433。


MSSQLの特定ポートの開通

 

ようやくポートの開通作業。

先ほどTCP/IPのプロトコルを有効化した時に使った

【SQL Server 2014 構成マネージャー】を開く。

 

SQL Serverネットワークの構成の横の＋マークをクリックして表示される

MSSQLSERVERのプロトコルを選択。

 

画面のように一番下にTCP/IPが表示されたら選択して右クリックで

メニューを表示して、プロパティを選択。

 

 

プロパティ内のIPアドレスタブに移動して

IP ALL欄のTCPポートが1433になっているか確認。

なっていなかったら1433と入力してOKをクリック。

 

 

SQL Serverのサービスを選択し、SQLServer(MSSQLSERVER)を選択。

 

画面のように一番下にTCP/IPが表示されたら選択して右クリックで

メニューを表示して、再起動を選択。

 

サービスを停止して

サービスを開始。

 

これでMSSQLのデータの通り道が1433で、開放を許可しますよという

ルール付けの設定完了。

 

開放されたわけではないのがポイント。

あくまで開放する道を作る作業。

 

 

ファイアウォールでポートを開く

 

ファイアウォール＝不正アクセスを防いでくれる機能。

なので、基本ファイアウォールは有効になっている。

なっていないとヤバイ。

 

別のPCからMSSQLに接続するには、ファイアウォールで

ポート番号1433を開くように設定する。

 

この作業をすることでようやく1433をデータが通れる。

 

ファイアウォールでポート番号1433を開く

 

ファイアウォールの設定画面を開く。

 

スタートボタンをクリックし、プログラムとファイルの検索欄に、

 WF.mscと入力して表示されたプログラムをクリック。

 

セキュリティが強化された Windows ファイアウォール画面が起動。

左側の受信の規則をクリックし、右側の新しい規則 をクリック。

 

新規の受信の規則ウィザードが起動。

ポートを選んで次へ。

 

TCPと特定のローカル ポートを選択し、テキストボックスに1433と入力。

次へをクリック。

 

 

接続を許可するを選択し、次へ。

 

どんなネットワーク状況でServerに接続したいか決める。

 

プロファイルってなんやねんっていう人はMSサイトで

ざっくり内容を把握しておいたほうが良い。

 

さらにちゃんと知りたい場合はここを見る。

 

公共の電波を使ってSQLServerに接続するなんて怖すぎる。

パブリックをONにするのであれば

IPアドレス

コンピューター名

とかでアクセスできる条件を別に設定するべき。

 

あとで変更もできるみたいなので

今回はプライベートのチェックボックスだけをONにして次へ。

 

 

作成した規則に名前をつける。

今回は名前をMSSQLポート設定。説明にMSSQL用のポート開放用

と入力し、完了。

 

完了すると、自動でこの規則が有効になる。

 

 

ようやく下準備完了。

 

残りの

  • 別のPCからのDBへの接続
  • SQL Server BrowserServiceを使用した接続

は長くなったので、次回へ持ち越し。

 

接続できますように。。。。

 

Microsoft SQL Serverに挑戦してみる⑦テーブル作成

テーブルに何を格納するか決める

 

他のPCからの接続テストのためにとりあえず簡単にテーブルを作ってみる。

 

作成する前に何をデータとして保有したいかを決める。

 

前回作ったデータベース【Tigers】

名前のとおり、かの有名かつ最高な野球球団阪神タイガースの

選手情報テーブルを作ってみる。

 

テーブルの中身は

  • ID
  • 選手名
  • 背番号
  • 生年月日

を入力することに。

 

背番号：１
選手名：鳥谷　敬
生年月日：1981/06/26


背番号：44
選手名：梅野　隆太郎
生年月日：1991/06/17

とりあえずあくまでテストDBなので、この二人の情報だけを格納してみる。

 

 

テーブル作成

 

格納する情報を決めたのでデータベース【Tigers】に新規テーブルを作成。

 

【Tigers】の下の階層の【テーブル】を選択している状態で右クリックして

メニューを表示。一番上に表示されているテーブルを選択。

 

 

しばらくするとテーブルの情報を入力する画面が表示される。

Accessのテーブルのデザインビューに似ている。

 

 

今回は画像の①②③の部分を入力編集。

 

 

①列名情報

 

ここで列名・データ型・NULL許容を決める。

  • ID　＝ 自動で付与されるユニーク番号を IDにする。
  • 選手名　＝ name
  • 背番号　＝ uniform_number
  • 生年月日 = DOB

と命名。

 

 

②テーブル名

 

画像内で Table_1となっている部分がこのテーブル名になる。

 

Table_1のままだと何が格納されているかイメージがつかないので

今回はPlayer_info_tableに変更。

 

 

③IDENTITYの指定

 

Accessではデータ型でオートインクリメントが選べたけれど

MSSQLではこの項目で指定するらしい。

  • IDENTITYの指定：はい
  • （IDである）：はい
  • IDの増分：１
  • IDENTITYシード：１

内容をざっくり説明すると以下のとおり。

  • IDENTITYの指定：連番にするかしないか
  • （IDである）：IDなのか
  • IDの増分：次の番号に加算する数値
  • IDENTITYシード：一番はじめのNoは何にするか

何番からスタートさせて、いくつずつ増加させるかを簡単に設定できる。

Accessより既にめちゃめちゃ便利な香りがする。

 

すべて入力編集を終えたら保存する。

保存はCtr+Sでも、メニューのファイルからでもどちらからでもOK.

 

保存されるとエクスプローラーにこんな感じでテーブルが保存される。

表示されていない場合は更新ボタンを押すと表示されるはず。

 

 

テーブルにデータ入力

 

Accessをベースに運用する予定なので直接編集をする予定は

ないのだけれど、一応データ入力方法も確認。

 

編集したいテーブルを選択している状態で右クリックして

メニューを表示。上位200行の編集をクリック。

 

 

Accessのテーブルのデータシートビューに似ている画面が表示される。

 

 

Accessと同じ感じにデータを入力していく。

ちなみに今回IDは自動で付与されるようにしているので入力も編集もできない。

 

！が表示されているのは保存されていない状態の意味らしい。

保存されると！は消えてくれる。

 

Access出身者は今回も楽勝。

次回は他のPCからの接続に挑戦したいなあ。

Microsoft SQL Serverに挑戦してみる⑥データ格納場所作成

データベースの概念

元々Access使いまくっていたので
・テーブル
・クエリ（SQL）
・リレーション
・データ型
などなどの概念は備わっているのでこの辺は楽勝な気がする。

なので概念系の記述はスルー。


どうにかこうにかしてAccessで作ってあるDBをSQL Serverに
移管することが第一目的なので、ここでの操作方法は
なんとなーく覚えておけばいい。

概念的なものをしっかり勉強したいのであれば
Google先生にお世話になったほうがよい。


SQLServer Management Studioで作成

SQLServer Management Studioを起動する。


しばらく待っているとサーバーへの接続画面が表示。

④で初期設定は完了しているので、このまま接続をクリックするだけでOK。

 

 

オブジェクトエクスプローラーに表示されている項目を見てみると

データベース

セキュリティ

サーバーオブジェクト

レプリケーション

管理

と階層がある。

 

名前からしても一番上のデータベースってところにデータを作る。

 

ただし、データベースの下の階層にあるシステムデータベースの中に

データを作ってはいけない。

システムに影響を与える情報が入っているんだろうから当然といえば当然。

 

データベースの部分を右クリックするとメニューが表示される。

一番上の新しいデータベースをクリック。

 

少し待つとこんな画面が表示される。

データベース名をここで決めるらしいので、とりあえず私は【Tigers】と入力。

 

余談だけれど

命名時に日本語を使うのか、使わないほうがいいのかという議論やら意見には

目を通して置いたほうがいい。

 

視認性を重視して日本語（マルチバイト文字）を使うか

予期せぬ不具合があったら困るので通例のとおりシングルバイト文字を使うか

自分なりのルールをこの時点で決めて置いたほうがいい。

 

ちなみに私はAccessではド素人軍団向けに作っているために

視認性を重視して日本語で命名していたけれど、

そのせいでデータ移管とかバージョンアップとかで過去に苦戦したことがある。

 

なので、これを機にシングルバイト派で行くことにする。

 

閑話休題

 

データベース名を入力すると、データベースファイル欄の倫理名が入力される。

データ格納ファイルと文字通りログ格納ファイルの場所と名前。

ファイルパスとか初期サイズとか変更できるみたいだけど今回はこのままで

OKをクリック。

 

これでデータベース階層の下にデータベース名Tigersというデータを格納する

場所ができあがる。

 

 

データ格納場所作成　＝　データベース作成　となる。

SQL Serverには複数のデータベースを作ることができる。

 

ここまで楽勝。

 

早く移行したいけれど、次回はテーブル作成した方がいいのかな。

 

 

 

 

Microsoft SQL Serverに挑戦してみる⑤ネットワークとは

ネットワークサーバーって何なのか

 

前回ネットワークへサーバーへの設定ができなかった。

 

何が駄目で何をすべきか全くわからないので

SQL Serverとネットワークについて諸々先生に確認。

 

◆SQL Server 2012 XE にTCP/IP接続する方法
SQL Server Expressのデフォルトの設定では、リモート接続を許可していないため、

クライアントPCからサーバマシンに接続できません。

 

⇒接続許可したら他のPCから接続できるってこと！？

 

◆「SQL Server Browser」とは？
SQL Server Browserは，デフォルトでは有効化されていない。

SQL Server Browserの主な目的は，ネットワーク上にあるシステムにおける

SQL Serverデータベース・エンジンの名前付きインスタンスの場所を特定する・・・

 

⇒ネットワーク上のって書いてある。。。でもこのBrowserがって説明だし。。。

 

◆SQL Server インストール後に外部からの接続をするための設定
SQL Server をインストールした後に外部の端末から接続をできるように

するための一連の設定になります。

 

⇒外部の端末から接続をできるように！？！？やっぱり！！

 

 

既存ネットワーク上にデータを置くわけではない


社内LAN上のフォルダにSQL Serverで作成したデータを

格納しないといけないと思っていた。

 

インストールもして、Server接続まで終わっているのに（笑）

目からウロコ。今更知った衝撃の事実。

ネットワークの構築ってなんか特別なPCと特別な機器がないとできないと思ってた。

Accessをネットワークフォルダにおいて接続するのと何が違うのか
ようやく理解。

 

※SQL ServerとAccessとのデータベースの違い

　両者の違いは歴然！なのによくわかってなかった。。。

 

SQL Serverに他のパソコンから接続できるようになる。

 

ナニコレマジ便利。

 

ってことでサーバー名とか直さなくても良いことが発覚したので

次回はかんたんなデータ作成を。