Just another WordPress.com site
I have found some interesting information in Denali Database property Windows.
I have installed two instance of Denali x64 version of Enterprise Edition into two different server but in both instances i found this interesting information. In database properties windows both General and Files page showing different sizes of database. I am not sure whether this a bug in Denali or not
In General Page:
In File Page:
Recently we have moved a SQL Server to a different hardware. The existing Sql Server version is 2008 R2 and the server is
registered a to Utility Control Point server running separate Instance.
As usual in new hardware we keep all log file in separate drive with Raid 10 configuration.
To figure out the how large the log drive should in order to avoid run out disk space error we used
UCP for historical log file size data.
During this historical data period several process or job running to server may have increased the log file size
so the information we had was fair enough to justified the log file growth in future.
We can use the Utility control GUI tool to find out the largest log file size but if we have too many databases in
one server like in share environment it wouldn’t be a feasible in some cases.
But using the following query can find the historical log file size for databases to a specific or all instances.
I also have added the minimum processing date for log file when maximum size have reached for specific databases
in the query. The reason I add this date to the query that if any database existing log file size is quite smaller
compare to maximum size we can go back to that date to find out what was running on that date and this can help us
to figure out future log file size growth if that process run again so we don’t have to face run out of disk space issue.
use sysutility_mdw — Change with your UCP database name
go
WITH MaxTranUsages
(processing_time,database_name,dbfile_name,used_space_bytes,allocated_space_bytes,RowNumber) AS
(
select
processing_time,database_name,dbfile_name
,used_space_bytes
,allocated_space_bytes
,ROW_NUMBER() over (partition by database_name
order by allocated_space_bytes desc,processing_time) RowNumber
from sysutility_ucp_core.space_utilization_internal
where server_instance_name = ‘Your Server Instance Name’
— Ignore this line if all instance are use
and object_type = 7 — Use any or all object Type depending on Report
—- Object_type — 7 Log File
—- Object_type — 6 Data File
—- Object_type — 4 Database
)
select
database_name,CONVERT(VARCHAR(20), processing_time, 100) as processing_time,dbfile_name
,CAST((used_space_bytes/(1024*1024)) as numeric(10,2)) as used_space_Mbytes
,CAST((allocated_space_bytes/(1024*1024)) as numeric(10,2)) as allocated_space_Mbytes
,RowNumber
from MaxTranUsages
where RowNumber = 1
order by database_name
Recently one of our DBAs Installed a Named Instance of SQL server 2008 R2 x 64 enterprise edition to one of our test environments. Integration Services were also installed and incorporated to the installation. Due to the company’s security policy, we had to change the TCP/IP default port number in SQL Server Network Configuration, from dynamic to static, with a specific port number.
The Developmental team members were not part of the local Server Administration group so we followed several steps, described in detail by Microsoft KB, to configure a method of connecting Integration Services Server remotely.
But the Developmental team members complained that they couldn’t connect to Integration Services from their local machine.
Investigation Technical Details
During the investigation process I tried to connect to integration services with a test user profile, but I wasn’t able to connect. However, when I did the RDP to the server, I was able to connect Integration successfully. Through further investigation, I have found the following information related to SQL Server:
o Port 135 was opened in firewall for Integration Service.
o SQL Browser was running.
o Alias was created for SQL Server.
o Using netstat command I found that SSIS was not running under port 135.
Also, I have verified, through netstat command, that SSIS was running under dynamic port and each time I restarted the SSIS, the service was running under different ports within the range of 49152 to 65535. For testing purposes, I opened above range dynamic ports in firewall and was able to connect Integration Services remotely with a test user. However, company policy prohibits opening a range of ports in firewall.
Solution
After thorough investigation and research online, we took several steps to fix this issue, so the SSIS can connect remotely. In summary:
1. Open new static port for SSIS in Windows firewall
2. Edit registry entry. HKEY_CLASSES_ROOT\AppID\{xxxxx-xxxx-xxxx-xxxxxx} (replace with the appropriate value corresponding to your server. The end of the post summarizes how to find the AppID).
3. Create new REG_MULTI_SZ
4. Name its Endpoints
5. Value = ncacn_ip_tcp,0,#
#= new static SSIS port.
6. Restart SSIS services.
Below, the sample screenshot shows how the registry will look once the procedures, above, are followed.
Hopefully this article will aid the process of troubleshooting SSIS remote connection.
How to Find AppID
1. Type Dcomcnfg.exe to the command prompt and press enter ,it will open the Component Services in a new window.
2. Expand Component Services- Computer and My Computer
3. Expand DCOM Config
4. Find MsDtsServer100
5. Right click MsDtsServer100 and select properties.
6. A new Property Window will open and you will see the AppID next to Application ID under general tab.
Here is the screenshot in our environment:
sqlorcl 