Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Access 2016)
遠隔地SQL Serverの1回目OPENに時間がかかる
投稿日時: 22/05/24 11:41:51
投稿者: hareru

SQL Server2019std(本番用DB)への接続時の相談です。
DBは遠隔地にあります。
 
以下のコードでsqldbに接続する時、初回OPEN時に10秒程時間がかかるのですが何が原因かが不明です。
---------------------------------------------------------
        pStrSrvPC = "172.xx.xxx.xx"
        pStrSvrInst = pStrSrvPC & "\INST_TEST,1433"
        pStrConnSvr = "Provider=SQLOLEDB" _
                    & ";Persist Security Info=False" _
                    & ";Connect Timeout=120" _
                    & ";Data Source=" & pStrSvrInst _
                    & ";Initial Catalog=" & "TestDb" _
                    & ";Integrated Security=SSPI;"
         
        Set SqlCn = New ADODB.Connection
        SqlCn.Open pStrConnSvr
---------------------------------------------------------
以下の内容で確認してみました。
前提は、
・「フォーム1」、「フォーム2」の各ボタンに上記のコードを実装。
・「フォーム1」から「フォーム2」を呼び出すボタンを配置。
・Access起動時の"フォームの表示"に「フォーム1」を登録
2パターンのテストを実行しました。
@Access起動後「フォーム1」で上記コードを実行後(約10秒かかる)、「フォーム2」を呼び出し上記コードを実行するとほぼゼロ秒。
AAccess起動後「フォーム1」より「フォーム2」を呼び出して上記コードを実行後(約10秒かかる)、「フォーム1」へ戻り上記コードを実行すると約10秒かかる。
 
最初のフォームでOPEN処理を実行しておけば、そこから呼ばれるフォームのOPENには時間がかからないようなのですが、最初のフォームでも時間をかけないようにするにはどのようにすると良いでしょうか?
調べてみると「接続プール」が関係しているように考えられるのかもしれませんがそれにしても初回OPENに10秒は時間がかかりすぎると思っています。
遠隔地サーバでは無くAccess実行の近くにあるDBであれば、このような現象はありません。
どなたか回避出来るアドバイスを頂けないでしょうか?

回答
投稿日時: 22/05/24 12:02:59
投稿者: Suzu

同じ遠隔地にあるマシンから同じ操作をした場合の接続時間はどうなっていますか?
ファイヤーウォールのポート設定は?
 
 
どうしても解決できない時には、
接続を Public として、システムを立ち上げた時に接続してしまいます。

投稿日時: 22/05/24 14:02:23
投稿者: hareru

Suzuさん、回答ありがとうございます。
>同じ遠隔地にあるマシンから同じ操作をした場合の接続時間はどうなっていますか?
ご指摘の環境でテストが出来ない状況です。(私も実行してみたいと思っていました)
 
>ファイヤーウォールのポート設定は?
「TCPポート1433を開いています。」
知識不足ですが、これで回答になっていますでしょうか?
 
>どうしても解決できない時には、
>接続を Public として、システムを立ち上げた時に接続してしまいます。
Publicにしても1回目のOPENに時間がかかるのは避けれないとの認識ですが、合っていますでしょうか?
 
ちなみに当たり前ですがExcelで実行した場合も10秒程かかります。
しかしExcelの場合はAccessとちがい、単独のBOOKで利用するケースが多い為に(メニュー画面から各処理を実行する利用方法では無いです)、毎回OPENする都度、時間がかかってしまいます。
(Excelの方が毎回OPENする度に"待ち"が発生するので大きな問題なのですが、今はExcelでsqldbに接続しているBookが極わずかなので、最悪、Accessへ移行も可能と考えています)
 
質問に合致した回答になって無いかもしれません。
  
以上、よろしくお願いいたします。

回答
投稿日時: 22/05/25 09:31:55
投稿者: Suzu

原因の 可能性が高いのは
・プログラム
・自マシン
・ネットワーク(自セグメント)
・ネットワーク(遠隔地セグメント)
・サーバー
 
どこで問題が発生しているのか 突き止める には ローカル環境
つまり遠隔地と同じセグメントでテストをしてみるのが手っ取り早いのですがね・・
 
 
 

引用:
>ファイヤーウォールのポート設定は?
「TCPポート1433を開いています。」
知識不足ですが、これで回答になっていますでしょうか?

 
当方でも、ポートまでは判りかねますが、
サーバー側でポート番号を変えていないのであれば、それで良いと思います。
一応、ローカルマシンのファイヤーウォールを無効にし、テストをしてみて、
接続時間に違いが無いか確認してみてください。
 
早くなったなら
 NETSTAT にて 使用しているポートを確認し、
 ファイヤーウォールを有効にしNETSTATを再度実行
 ポートの違いを確認し、ファイヤーウォールの開放ポート設定
 
 
遅いままなら
 SQLServer に対し、Test-NetConnection にて ポート1433の疎通確認。
 
Configure the Windows Firewall to Allow SQL Server Access
https://docs.microsoft.com/ja-jp/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?redirectedfrom=MSDN&view=sql-server-ver16
 
のポートも確認してみてください。
 
 
 
引用:
>接続を Public として、システムを立ち上げた時に接続してしまいます。
Publicにしても1回目のOPENに時間がかかるのは避けれないとの認識ですが、合っていますでしょうか?

 
そうですね。
でも、AutoExcec等で接続を確立させてしまえば
ボタンを押すまでの時間を稼ぐ事ができますよね。
 
 
テストとしては、VBA内で接続文字列を用意するのではなく DSNを使用するのも試せますね。
 
ODBC の DSN を設定 (アプリケーションのビットに合わせたODBCデータソースを使用してください)
 DSNの設定画面での接続テストでの反応時間
 Accessリンクテーブルに DSNを指定し、そのリンクテーブルを開いてみる
 Accessリンクテーブルに DSNを指定し、そのリンクテーブルを VBA で開いてみる 
 DSNを VBA内 接続文字列に使用し開いてみる

回答
投稿日時: 22/05/25 09:37:54
投稿者: Suzu

テスト 追加
・その遠隔マシンへの SQLServerではなく、ファイル・フォルダへの参照権限があるのであれば
 事前に、エスクプローラー開き、その後、SQLServer サービスに接続してみる。

投稿日時: 22/05/25 17:05:09
投稿者: hareru

Suzuさん、丁寧な回答ありがとうございます。
 
連絡頂いた内容を全て実行は出来ませんが、出来るものからやってみようと思います。
(内容が私には敷居の高いものもありますので調べながらになります)
 
また、下記は参照権限が無い為に実行できません。
>テスト 追加
>・その遠隔マシンへの SQLServerではなく、ファイル・フォルダへの参照権限があるのであれば
> 事前に、エスクプローラー開き、その後、SQLServer サービスに接続してみる。
 
テストの結果を後日、連絡させて頂きます。
 
以上、よろしくお願いいたします。

回答
投稿日時: 22/05/26 10:30:25
投稿者: Suzu

引用:
また、下記は参照権限が無い為に実行できません。
>テスト 追加
>・その遠隔マシンへの SQLServerではなく、ファイル・フォルダへの参照権限があるのであれば
> 事前に、エスクプローラー開き、その後、SQLServer サービスに接続してみる。

 
ファイル共有でなくとも良いのです。
SQLServer の PCへの接続情報がある事で、速くなる事があったので
同じ事ができないかな。と言うことです。
  FTPや、SQL Server Management Studio でも 良いと思います。

投稿日時: 22/05/26 12:36:33
投稿者: hareru

Suzuさん、返信ありがとうございます。
>SQLServer の PCへの接続情報がある事で、速くなる事があったので
>同じ事ができないかな。と言うことです。
> FTPや、SQL Server Management Studio でも 良いと思います。
→SQL Server Management Studioは接続可能です、但し、接続(砂時計が消える迄の)時間は約1分程かかります。
 この接続時間が正常なのかはわかりません。
 近くのPCにセットアップしたSQL Server Expressへの接続は1秒です。
 
>遅いままなら
> SQLServer に対し、Test-NetConnection にて ポート1433の疎通確認。
 結果は、「TcpTestSucceeded:True」なので問題無いと思います。
 
>Configure the Windows Firewall to Allow SQL Server Access
→実施済み項目は以下の2つです。
 1.TCP1433の解放
 2.「セキュリティが強化された Windows Defender ファイアウォールを使用して、ファイアウォールにプログラムの例外を追加するには」で"sqlserver.exe"の接続許可。(これは本日行いました、db再起動後テストしましたが結果変わらず。名前付きインスタンスを使用しているのでやってみました)
 
以上、よろしくお願いいたします。

回答
投稿日時: 22/05/26 13:36:09
投稿者: Suzu

ファイヤーウォール の設定は、サーバー側でも必要ですが、
今 試されたのは クライアント側では?
 
 
 

引用:
ポート1433の疎通確認。
 結果は、「TcpTestSucceeded:True」なので問題無いと思います。

確認したのは、SQLServer のマシンと TCP 1433 で接続できるか?
 
名前付きインスタンスの場合、動的ポート の様ですから それ以外のポートも使う。
クライアント側で ファイアウォールに SQLServer を追加しても、
サーバー側からは、それ以外のポートで通信するため、
ポートを探す処理が入って時間を要しているのではないかと思います。
 
 
引用:
参照権限が無い為に実行できません。

参照権限が無いのであれば、サーバーの管理者権限も無いのですよね?
と言うことは、そのサーバーの管理者が他に居るのですよね?
 
回答者としては、推測で話すしかありませんが、
管理者が居るのであれば、その方に どの様な設定にすれば良いのか聞く方が確実と思います。

投稿日時: 22/05/26 16:41:24
投稿者: hareru

Suzuさん、返信ありがとうございます。
 
>ファイヤーウォール の設定は、サーバー側でも必要ですが、
>今 試されたのは クライアント側では?
サーバー側で試した結果を連絡させて頂きました。
※「サーバー側でも」との事ですがシステム開発では必要で本番運用でクライアントPCに設定は必要ないと思っています。
一応、Test-NetConnectionはクライアント、サーバーの両方で実施しOKである事を確認しました。
 
「クライアントからServerの参照権限が無い」と連絡させて頂きましたが間違っていました。
権限がありました。(Adninstrators)
フォルダを共有する事でクライアントから参照する事が出来ました。
エクスプローラでフォルダを参照した状態で、接続確認を実行したのですが結果は変わらずでした。
 
やはり、Serverと同一セグメントPCで接続確認を実施してみたいところです。
インフラ部門に確認してみます。
 
 

回答
投稿日時: 22/05/27 09:15:47
投稿者: Suzu

引用:
フォルダを共有する事でクライアントから参照する事が出来ました。
エクスプローラでフォルダを参照した状態で、接続確認を実行したのですが結果は変わらずでした。

 
そうなんですね。リピートの通信であれば、速くなる事が無い事が確認できました。
ファイル共有 がさほど遅くなく、サーバー/クライアント の通信が特に遅い訳ではないのでしょう。
 
通信機器間間 での 初回通信が遅い事に起因するのではなく
SQLServerサービスの接続 が 遅い事が判ります。
 
 
引用:
>ファイヤーウォール の設定は、サーバー側でも必要ですが、
>今 試されたのは クライアント側では?
サーバー側で試した結果を連絡させて頂きました。
※「サーバー側でも」との事ですがシステム開発では必要で本番運用でクライアントPCに設定は必要ないと思っています。
一応、Test-NetConnectionはクライアント、サーバーの両方で実施しOKである事を確認しました。

 
Test-NetConnection は、1433 固定ポートの疎通確認であり
 
Configure the Windows Firewall to Allow SQL Server Access
https://docs.microsoft.com/ja-jp/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?redirectedfrom=MSDN&view=sql-server-ver16
 
引用:
データベース エンジンで使用されるポート
既定では、SQL Server と関連データベース サービスで使用される一般的なポート:TCP 1433、4022、135、1434、UDP 1434。 次の表では、これらのポートについてより詳細に説明します。 名前付きインスタンスでは動的ポートが使用されます。

引用:
動的ポート
既定では、名前付きインスタンス (SQL Server Express を含む) は動的ポートを使用します。 は、データベースエンジンが開始されるたびに、使用可能なポートを識別し、そのポート番号を使用します。 名前付きインスタンスがインストールされているデータベースエンジンの唯一のインスタンスの場合は、TCP ポート1433が使用されることがあります。 データベースエンジンの他のインスタンスがインストールされている場合は、別の TCP ポートが使用されることがあります。

 
名前付きインスタンスであり、それが唯一の名前付きインスタンスでないなら
1434 以外のポートで、通信可能なポートを 探し通信を行う と言うことです。
 
探す分、時間を要する事となると考えます。
なので、1434 がOKだから、ポートが通信に時間を要する原因にならない と言うわけではないと思います。
 
ですから、ポートブロックが原因でない事を確認するためにも
一時的に、サーバー/クライアントのファイアウォールを 無効にし、
サーバー/クライアントのファイアウォールが 要因でない事を確認したいのです。
 (ルーター等でポートが閉鎖されている可能性はありますが)
 
 
 
引用:
やはり、Serverと同一セグメントPCで接続確認を実施してみたいところです。
インフラ部門に確認してみます。

 
それが出来るのであれば良いですね。
リモートで同一セグメントマシンを操作できたらラクチンですね。

投稿日時: 22/05/30 12:12:58
投稿者: hareru

Suzuさん、詳細な回答をありがとうございます。
 
動的ポート番号について
 

引用:

動的ポート
名前付きインスタンスがインストールされているデータベースエンジンの唯一のインスタンスの場合は、TCP ポート1433が使用されることがあります。

ADO接続パラメータにポート番号をしています、またSSMSから接続する時もポート番号を指定していて接続出来ているので動的ポートとは言えども利用しているポート番号は1433だと思っています。
 
構成マネージャを変更し動的ポートを設定後、再接続してみましたが現象は変わりませんでした。
構成接続マネージャ
@SQL serverネットワークの構成→インスタンスのプロトコル→TCP/IP→IPアドレスタブ内の「IPALL」→TCP動的ポート=0に設定。(通常は空白で設定)
ASQL Server BrowerとSQL Server(インスタンス)の再起動
Bもう一度、@の動的ポートをみて設定されたポート番号を確認。
C上記のポート番号を指定してado接続
        pStrSvrInst = pStrSrvPC & "\INST_TEST,新ポート番号"
結果、変わらずです。
 
仮にServerと同一セグメントPCで接続し、OPENの時間が掛からないとしたらファイアウォールの問題ととらえて良いのでしょうか?
 
以上、よろしくお願いいたします.[/quote]

回答
投稿日時: 22/05/30 14:56:48
投稿者: Suzu

引用:
ADO接続パラメータにポート番号をしています、またSSMSから接続する時もポート番号を指定していて接続出来ているので動的ポートとは言えども利用しているポート番号は1433だと思っています。

 
その辺りは NETSTATで確認ください。
 
 
引用:
構成マネージャを変更し動的ポートを設定後、再接続してみましたが現象は変わりませんでした。

1433を指定しても、固定ではなく、動的に動作しているのであれば、
固定から動的に変更しても 変わりは無いでしょう。
 
 
何にしても、netstatでリッスンしているプロセスを特定すれば、
どのポートを使っているか確認できますね。
 
 
ポートが関係ないのだとすれば、あとはサーバー側の設定でしょうか。
遅いのが、SQLの実行ではなく、あくまでも接続なのですから、SQLの実行計画は関係ないですよね。
 
接続なのであれば、AUTO_CLOSE はどうなっていますでしょうか。
ON になっているのであれば、OFFにしてください。

投稿日時: 22/05/31 18:51:18
投稿者: hareru

Suzuさん、回答ありがとうございます。
 
返信がおそくなり、すみませんでした4.
返信したつもりで送っていませんでした。

引用:

ポートが関係ないのだとすれば、あとはサーバー側の設定でしょうか。
遅いのが、SQLの実行ではなく、あくまでも接続なのですから、SQLの実行計画は関係ないですよね。
  
接続なのであれば、AUTO_CLOSE はどうなっていますでしょうか。
ON になっているのであれば、OFFにしてください。

やはり、サーバ側の設定でしょうか。
 
今出来る事としてSQL Serverのインスタンス無しでの接続確認をやってみたいのですが、
一度uninstallしないとインスタンス無しで作ることは出来ないみたいなのですぐには出来ないかもしれません。
 
AUTO_CLOSEはDBをアタッチした時にOFFにしてあります。
 
[/quote]

回答
投稿日時: 22/06/01 10:47:35
投稿者: Suzu

引用:
AUTO_CLOSEはDBをアタッチした時にOFFにしてあります。

そうなのですね。残念。
 
 
引用:
やはり、サーバ側の設定でしょうか。

当方の確認方法を含め、思いつくのは既に述べています。それらについてスルーされている部分もありますし
思いつく部分はもうありませんので、当方では力及ばず というところです。

投稿日時: 22/06/01 11:51:03
投稿者: hareru

Suzuさん、回答ありがとうございます。
 
質問を見落としてしまっていたようですみませんでした。
 
Server担当部門に確認し、ファイアウォールをオフにして実行してみました。(クライアントもオフで)
実行結果は変わらずでした。
 
関係ないかもしれませんがTest-NetConnectionの応答も同じように10秒位かかります。
 
あとはSQL Serverの再インストールをやってみる事ぐらいしか思いつきません。
インスタンス無しも作成してみよう思っています。
これでダメなら諦めます。
 
ご親切に回答を頂きましてありがとうございました。