How can I convert download SQL Server 2005 to a licensed version.

I downloaded a 180 day trial version of SQL Server 2005, and have it running. I have purchased a 5 user workgroup version. I would like to apply the 5 user license to the version that is currently installed.

My preference is to not reinstall everything since I have everything configured as I would like, and it is working great. What is the best approach?

Paul

The only way to do this is to Upgrade your Evaluation Edition to the Workgroup SKU. To do this you should run the Workgroup installation program and select the edition of SQL Server that you already have installed instead of installing a new edition.

Michelle

|||

Will this leave the installed database and all of its configuration as is?

Or will it change any of the configuration?

The reason I ask, is that due to a lack of proper planning we did not have SQL Server installed prior to a vendor arriving to install their application. I quickly downloaded the trial version, and installed it and ordered the Workgroup Edition. The applicationn is in and running, and I now want to make everything legal, and to have it run past the 180 day evaluation period.

If upgrading will change the application, I will need to pay the vendor to reconfigure things, and cause me problems.

Paul

sql

How can i configure sql server2000 with vb.net2003

Hi

I got an exe file of an application with program debug database.

Once i am running that program it is giving an error.

Can anyone suggest me how to configure SQL server2000 database with this application.

I have installed client tool of SQL server2000 on my system.

I am very new in MS platform.

................................................................................................................................

The error is like this

it is showing this error

System.Data.Sql.Client.qlException: SQL server does not exist or access denied.
at DataAccess.DataAccess.ExecuteInsertUpdateDeleteQuery(String prsConnString, String StoredProcName,sqlParameter[]parameterLost)

Thanks

I'm not exactly sure what it is you're trying to do, but if you want to set up replication using Enterprise Manager (are these the tools you're referring to?) then you should start by reading Replication topic in Books Online.sql

How can I cancel a backup?

I'm running SBS 2003 Premium. I've got several databases set up to be backed
up nightly, but I have deleted one of them since I set up the backup jobs.
Unfortunately, even though the database is deleted, it still tries to back it
iup each night and I get an error in the "Monitoring and Reporting" page in
Server Management.
I set up the backups through SQL server. I can't seem to find where the
backups jobs are "stored." I looked at Scheduled Tasks, the registry, and
in Enterprise Manager under Maintenance Plans and under Management -> Backup.
The backups were not set with Maintenance Plans, they were set by right
clicking on the individual databases and selecting "Backup Database."
How do I cancel the backup?
Thank you in advance.
JonEM, Management, SQL Server Agent, Jobs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jonathan R. Karp" <JonathanRKarp@.discussions.microsoft.com> wrote in message
news:8FBB877F-E6FF-47F4-8900-FEFBD92E0653@.microsoft.com...
> I'm running SBS 2003 Premium. I've got several databases set up to be backed
> up nightly, but I have deleted one of them since I set up the backup jobs.
> Unfortunately, even though the database is deleted, it still tries to back it
> iup each night and I get an error in the "Monitoring and Reporting" page in
> Server Management.
> I set up the backups through SQL server. I can't seem to find where the
> backups jobs are "stored." I looked at Scheduled Tasks, the registry, and
> in Enterprise Manager under Maintenance Plans and under Management -> Backup.
> The backups were not set with Maintenance Plans, they were set by right
> clicking on the individual databases and selecting "Backup Database."
> How do I cancel the backup?
> Thank you in advance.
> Jon
>|||That worked! Thank you very much!
Jon
"Tibor Karaszi" wrote:
> EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jonathan R. Karp" <JonathanRKarp@.discussions.microsoft.com> wrote in message
> news:8FBB877F-E6FF-47F4-8900-FEFBD92E0653@.microsoft.com...
> > I'm running SBS 2003 Premium. I've got several databases set up to be backed
> > up nightly, but I have deleted one of them since I set up the backup jobs.
> > Unfortunately, even though the database is deleted, it still tries to back it
> > iup each night and I get an error in the "Monitoring and Reporting" page in
> > Server Management.
> >
> > I set up the backups through SQL server. I can't seem to find where the
> > backups jobs are "stored." I looked at Scheduled Tasks, the registry, and
> > in Enterprise Manager under Maintenance Plans and under Management -> Backup.
> >
> > The backups were not set with Maintenance Plans, they were set by right
> > clicking on the individual databases and selecting "Backup Database."
> >
> > How do I cancel the backup?
> >
> > Thank you in advance.
> >
> > Jon
> >
>
>

How can I cancel a backup?

I'm running SBS 2003 Premium. I've got several databases set up to be backed
up nightly, but I have deleted one of them since I set up the backup jobs.
Unfortunately, even though the database is deleted, it still tries to back it
iup each night and I get an error in the "Monitoring and Reporting" page in
Server Management.
I set up the backups through SQL server. I can't seem to find where the
backups jobs are "stored." I looked at Scheduled Tasks, the registry, and
in Enterprise Manager under Maintenance Plans and under Management -> Backup.
The backups were not set with Maintenance Plans, they were set by right
clicking on the individual databases and selecting "Backup Database."
How do I cancel the backup?
Thank you in advance.
Jon
EM, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jonathan R. Karp" <JonathanRKarp@.discussions.microsoft.com> wrote in message
news:8FBB877F-E6FF-47F4-8900-FEFBD92E0653@.microsoft.com...
> I'm running SBS 2003 Premium. I've got several databases set up to be backed
> up nightly, but I have deleted one of them since I set up the backup jobs.
> Unfortunately, even though the database is deleted, it still tries to back it
> iup each night and I get an error in the "Monitoring and Reporting" page in
> Server Management.
> I set up the backups through SQL server. I can't seem to find where the
> backups jobs are "stored." I looked at Scheduled Tasks, the registry, and
> in Enterprise Manager under Maintenance Plans and under Management -> Backup.
> The backups were not set with Maintenance Plans, they were set by right
> clicking on the individual databases and selecting "Backup Database."
> How do I cancel the backup?
> Thank you in advance.
> Jon
>
|||That worked! Thank you very much!
Jon
"Tibor Karaszi" wrote:

> EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jonathan R. Karp" <JonathanRKarp@.discussions.microsoft.com> wrote in message
> news:8FBB877F-E6FF-47F4-8900-FEFBD92E0653@.microsoft.com...
>
>

How can I cancel a backup?

I'm running SBS 2003 Premium. I've got several databases set up to be backe
d
up nightly, but I have deleted one of them since I set up the backup jobs.
Unfortunately, even though the database is deleted, it still tries to back i
t
iup each night and I get an error in the "Monitoring and Reporting" page in
Server Management.
I set up the backups through SQL server. I can't seem to find where the
backups jobs are "stored." I looked at Scheduled Tasks, the registry, and
in Enterprise Manager under Maintenance Plans and under Management -> Backup
.
The backups were not set with Maintenance Plans, they were set by right
clicking on the individual databases and selecting "Backup Database."
How do I cancel the backup?
Thank you in advance.
JonEM, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jonathan R. Karp" <JonathanRKarp@.discussions.microsoft.com> wrote in messag
e
news:8FBB877F-E6FF-47F4-8900-FEFBD92E0653@.microsoft.com...
> I'm running SBS 2003 Premium. I've got several databases set up to be bac
ked
> up nightly, but I have deleted one of them since I set up the backup jobs.
> Unfortunately, even though the database is deleted, it still tries to back
it
> iup each night and I get an error in the "Monitoring and Reporting" page i
n
> Server Management.
> I set up the backups through SQL server. I can't seem to find where the
> backups jobs are "stored." I looked at Scheduled Tasks, the registry, and
> in Enterprise Manager under Maintenance Plans and under Management -> Back
up.
> The backups were not set with Maintenance Plans, they were set by right
> clicking on the individual databases and selecting "Backup Database."
> How do I cancel the backup?
> Thank you in advance.
> Jon
>|||That worked! Thank you very much!
Jon
"Tibor Karaszi" wrote:

> EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jonathan R. Karp" <JonathanRKarp@.discussions.microsoft.com> wrote in mess
age
> news:8FBB877F-E6FF-47F4-8900-FEFBD92E0653@.microsoft.com...
>
>sql

How can I be sure that MSDE is installed and running

How can I check to be sure that the MSDE is installed properly and that it is running?

Should I be able to see the Service Manger in the tray?

Thank you,Check it in task manager under the processes tab.

How can i backup sql 2000 using win 2003 backup util

Hi
I have a windows 2003 server running sql. I would like to backup SQL 2000
databases using the built in backup software to a network location.
Could anyone tell me how i do this.
Thanks
Nick
To do this properly, you would have to take the databases offline, or stop
the SQL Server service.
Are you not able to use the built-in-and-highly-effective SQL Server backup
and then take those .bak files to a network location?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nick" <andync55@.hotmail.com> wrote in message
news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
> Hi
> I have a windows 2003 server running sql. I would like to backup SQL 2000
> databases using the built in backup software to a network location.
> Could anyone tell me how i do this.
>
> Thanks
> Nick
>
|||Hi
Thanks for the fast reply. Can i do that by right clicking on each databse
and selecting backup. Is that all i need to do. Is there any other sql
setting i should backup other than just the databases.
Thanks
Nick Comper
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> To do this properly, you would have to take the databases offline, or stop
> the SQL Server service.
> Are you not able to use the built-in-and-highly-effective SQL Server
> backup and then take those .bak files to a network location?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Nick" <andync55@.hotmail.com> wrote in message
> news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
>
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nick" <andync55@.hotmail.com> wrote in message
news:eLJqeOb1FHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks for the fast reply. Can i do that by right clicking on each databse
> and selecting backup. Is that all i need to do. Is there any other sql
> setting i should backup other than just the databases.
> Thanks
> --
> Nick Comper
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>
|||Hi
Thanks for the fast reply. Can i do that by right clicking on each databse
and selecting backup. Is that all i need to do. Is there any other sql
setting i should backup other than just the databases.
Thanks
Nick Comper
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> To do this properly, you would have to take the databases offline, or stop
> the SQL Server service.
> Are you not able to use the built-in-and-highly-effective SQL Server
> backup and then take those .bak files to a network location?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Nick" <andync55@.hotmail.com> wrote in message
> news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
>

How can i backup sql 2000 using win 2003 backup util

Hi
I have a windows 2003 server running sql. I would like to backup SQL 2000
databases using the built in backup software to a network location.
Could anyone tell me how i do this.
Thanks
NickTo do this properly, you would have to take the databases offline, or stop
the SQL Server service.
Are you not able to use the built-in-and-highly-effective SQL Server backup
and then take those .bak files to a network location?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nick" <andync55@.hotmail.com> wrote in message
news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
> Hi
> I have a windows 2003 server running sql. I would like to backup SQL 2000
> databases using the built in backup software to a network location.
> Could anyone tell me how i do this.
>
> Thanks
> Nick
>|||Hi
Thanks for the fast reply. Can i do that by right clicking on each databse
and selecting backup. Is that all i need to do. Is there any other sql
setting i should backup other than just the databases.
Thanks
--
Nick Comper
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> To do this properly, you would have to take the databases offline, or stop
> the SQL Server service.
> Are you not able to use the built-in-and-highly-effective SQL Server
> backup and then take those .bak files to a network location?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Nick" <andync55@.hotmail.com> wrote in message
> news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
>> Hi
>> I have a windows 2003 server running sql. I would like to backup SQL 2000
>> databases using the built in backup software to a network location.
>> Could anyone tell me how i do this.
>>
>> Thanks
>> Nick
>|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nick" <andync55@.hotmail.com> wrote in message
news:eLJqeOb1FHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks for the fast reply. Can i do that by right clicking on each databse
> and selecting backup. Is that all i need to do. Is there any other sql
> setting i should backup other than just the databases.
> Thanks
> --
> Nick Comper
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>> To do this properly, you would have to take the databases offline, or
>> stop the SQL Server service.
>> Are you not able to use the built-in-and-highly-effective SQL Server
>> backup and then take those .bak files to a network location?
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>>
>> "Nick" <andync55@.hotmail.com> wrote in message
>> news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
>> Hi
>> I have a windows 2003 server running sql. I would like to backup SQL
>> 2000 databases using the built in backup software to a network location.
>> Could anyone tell me how i do this.
>>
>> Thanks
>> Nick
>>
>|||Hi
Thanks for the fast reply. Can i do that by right clicking on each databse
and selecting backup. Is that all i need to do. Is there any other sql
setting i should backup other than just the databases.
Thanks
--
Nick Comper
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> To do this properly, you would have to take the databases offline, or stop
> the SQL Server service.
> Are you not able to use the built-in-and-highly-effective SQL Server
> backup and then take those .bak files to a network location?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Nick" <andync55@.hotmail.com> wrote in message
> news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
>> Hi
>> I have a windows 2003 server running sql. I would like to backup SQL 2000
>> databases using the built in backup software to a network location.
>> Could anyone tell me how i do this.
>>
>> Thanks
>> Nick
>

How can i backup sql 2000 using win 2003 backup util

Hi
I have a windows 2003 server running sql. I would like to backup SQL 2000
databases using the built in backup software to a network location.
Could anyone tell me how i do this.
Thanks
NickTo do this properly, you would have to take the databases offline, or stop
the SQL Server service.
Are you not able to use the built-in-and-highly-effective SQL Server backup
and then take those .bak files to a network location?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nick" <andync55@.hotmail.com> wrote in message
news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
> Hi
> I have a windows 2003 server running sql. I would like to backup SQL 2000
> databases using the built in backup software to a network location.
> Could anyone tell me how i do this.
>
> Thanks
> Nick
>|||Hi
Thanks for the fast reply. Can i do that by right clicking on each databse
and selecting backup. Is that all i need to do. Is there any other sql
setting i should backup other than just the databases.
Thanks
Nick Comper
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> To do this properly, you would have to take the databases offline, or stop
> the SQL Server service.
> Are you not able to use the built-in-and-highly-effective SQL Server
> backup and then take those .bak files to a network location?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Nick" <andync55@.hotmail.com> wrote in message
> news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
>|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nick" <andync55@.hotmail.com> wrote in message
news:eLJqeOb1FHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks for the fast reply. Can i do that by right clicking on each databse
> and selecting backup. Is that all i need to do. Is there any other sql
> setting i should backup other than just the databases.
> Thanks
> --
> Nick Comper
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>|||Hi
Thanks for the fast reply. Can i do that by right clicking on each databse
and selecting backup. Is that all i need to do. Is there any other sql
setting i should backup other than just the databases.
Thanks
Nick Comper
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> To do this properly, you would have to take the databases offline, or stop
> the SQL Server service.
> Are you not able to use the built-in-and-highly-effective SQL Server
> backup and then take those .bak files to a network location?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Nick" <andync55@.hotmail.com> wrote in message
> news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
>

How can I audit when a Trigger is disabled??

We are running SQL Server 2000 and are having a problem with database data
being changed. We have a trigger which logs when changes are made and what
the old and new data is. I really need to know if or when someone is
disabling that trigger and changing data. It is possible that the trigger i
s
being disabled and then the data is being changed and I would like that even
logged and then an operator notified by email.
Any ideas?
Thanks!
ToddTodd,
Can you lock down the database a bit more? ALTER TRIGGER requires ddl_admin
or db_owner privileges. Don't give users this level of access and they won't
be able to disable the trigger, just give them the minimum rights to do what
they need to do.
SQL Server 2005 has DDL triggers, but there's nothing in SQL Server 2000
that will allow auditing of this. I'm not actually sure if SQL Server 2005
can do it either.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"MCSA_Todd" wrote:

> We are running SQL Server 2000 and are having a problem with database data
> being changed. We have a trigger which logs when changes are made and wha
t
> the old and new data is. I really need to know if or when someone is
> disabling that trigger and changing data. It is possible that the trigger
is
> being disabled and then the data is being changed and I would like that ev
en
> logged and then an operator notified by email.
> Any ideas?
> Thanks!
> Todd

How can I access SQL server from SCO Unix?

We are running SCO Unix 5.0.5. We need a command line sql client that can connect to a MS SQL server running on a windows server. Can someone point me in the right direction? I don't want to write a sql client. I just want a command line sql client binary that is ready to work.

I want to do something like this in a unix shell script:

# sql -s 10.1.2.3 -u username -p password -f sqlcommandsinafile.txt -o sqlresults.txt

Any help would be appreciated....

I am not aware of such a cmd line tool on unix. To achieve what you want, you would need to get a hold of a 3rd party unix ODBC driver for SQL server. There is an odbcsql sample that ships with MDAC SDK, you can download the sample and port it to run on Unix.

Hope this helps.

sql

How can I access SQL server from SCO Unix?

We are running SCO Unix 5.0.5. We need a command line sql client that can connect to a MS SQL server running on a windows server. Can someone point me in the right direction? I don't want to write a sql client. I just want a command line sql client binary that is ready to work.

I want to do something like this in a unix shell script:

# sql -s 10.1.2.3 -u username -p password -f sqlcommandsinafile.txt -o sqlresults.txt

Any help would be appreciated....

I am not aware of such a cmd line tool on unix. To achieve what you want, you would need to get a hold of a 3rd party unix ODBC driver for SQL server. There is an odbcsql sample that ships with MDAC SDK, you can download the sample and port it to run on Unix.

Hope this helps.

How accurate is dm_db_index_usage_stats

All,

I've been running this script below regularly to see the supposed usage of my indexes:

SELECT object_name(s.object_id) AS ObjectName

, s.object_id

, i.name as IndexName

, i.index_id

, user_seeks

, user_scans

, user_lookups

, user_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i

ON i.object_id = s.object_id

AND i.index_id = s.index_id

WHERE database_id = db_id ()

AND objectproperty(s.object_id,'IsUserTable') = 1

ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC

Before I start disabling/dropping indexes, just how accurate is the this dmv?

Thanks,

Ian

The data for the dmvs is real time.|||

It records all activity since SQL was last started, in real-time. I have found it to be very reliable and helpful in deciding which indexes are actually being used for reads. Try running this query to get a better picture:

-- Possible bad Indexes (writes > reads)

DECLARE @.dbid int

SELECT @.dbid = db_id()

SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,

'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,

'Difference' = user_updates - (user_seeks + user_scans + user_lookups)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

ON s.object_id = i.object_id

AND i.index_id = s.index_id

WHERE objectproperty(s.object_id,'IsUserTable') = 1

AND s.database_id = @.dbid

AND user_updates > (user_seeks + user_scans + user_lookups)

ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC

How accurate is dm_db_index_usage_stats

All,

I've been running this script below regularly to see the supposed usage of my indexes:

SELECT object_name(s.object_id) AS ObjectName

, s.object_id

, i.name as IndexName

, i.index_id

, user_seeks

, user_scans

, user_lookups

, user_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i

ON i.object_id = s.object_id

AND i.index_id = s.index_id

WHERE database_id = db_id ()

AND objectproperty(s.object_id,'IsUserTable') = 1

ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC

Before I start disabling/dropping indexes, just how accurate is the this dmv?

Thanks,

Ian

The data for the dmvs is real time.|||

It records all activity since SQL was last started, in real-time. I have found it to be very reliable and helpful in deciding which indexes are actually being used for reads. Try running this query to get a better picture:

-- Possible bad Indexes (writes > reads)

DECLARE @.dbid int

SELECT @.dbid = db_id()

SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,

'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,

'Difference' = user_updates - (user_seeks + user_scans + user_lookups)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

ON s.object_id = i.object_id

AND i.index_id = s.index_id

WHERE objectproperty(s.object_id,'IsUserTable') = 1

AND s.database_id = @.dbid

AND user_updates > (user_seeks + user_scans + user_lookups)

ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC

How access SqlServer from the Web

Hello:
I apologize my question involves some Web contents, but I don`t know where
expose it.
I have a local Sql Server database running under W2000 server. This machine
have permanent Internet access . Can I connect a Visual Basic program running
in a remote PC machine to the Sql Server Database, directly through the Web ?
I would appreciate any suggestion or reference to technical article.
My best regards.
Roberto
Yes, you can do that.
My recommendation is to use a VPN to the SQL Server rather than directly (if
possible) and lock down the IP address of the client machine on the server
firewall.
Alternatively you can open up the port that SQL Server listens on (incoming)
to the SQL Server machine and open all (outgoing) from the SQL Server
machine. I'd set SQL Server to use a different port other than the default
1433, say 2025 or something (on the server use the Netowkr Configuration
Utility in the SQL program group) - you can get the client to use that port
using the client config utility on the client.
Hope that helps.
Last but not least, get it on SQL Sp4.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Roberto Carabajal" <RobertoCarabajal@.discussions.microsoft.com> wrote in
message news:849989E8-B257-4E25-9A2F-2E86CA164C67@.microsoft.com...
> Hello:
> I apologize my question involves some Web contents, but I don`t know
> where
> expose it.
> I have a local Sql Server database running under W2000 server. This
> machine
> have permanent Internet access . Can I connect a Visual Basic program
> running
> in a remote PC machine to the Sql Server Database, directly through the
> Web ?
> I would appreciate any suggestion or reference to technical article.
> My best regards.
> Roberto
|||Tony:
Thanks very much.
Roberto

How access SqlServer from the Web

Hello:
I apologize my question involves some Web contents, but I don`t know where
expose it.
I have a local Sql Server database running under W2000 server. This machine
have permanent Internet access . Can I connect a Visual Basic program runnin
g
in a remote PC machine to the Sql Server Database, directly through the Web
?
I would appreciate any suggestion or reference to technical article.
My best regards.
RobertoYes, you can do that.
My recommendation is to use a VPN to the SQL Server rather than directly (if
possible) and lock down the IP address of the client machine on the server
firewall.
Alternatively you can open up the port that SQL Server listens on (incoming)
to the SQL Server machine and open all (outgoing) from the SQL Server
machine. I'd set SQL Server to use a different port other than the default
1433, say 2025 or something (on the server use the Netowkr Configuration
Utility in the SQL program group) - you can get the client to use that port
using the client config utility on the client.
Hope that helps.
Last but not least, get it on SQL Sp4.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Roberto Carabajal" <RobertoCarabajal@.discussions.microsoft.com> wrote in
message news:849989E8-B257-4E25-9A2F-2E86CA164C67@.microsoft.com...
> Hello:
> I apologize my question involves some Web contents, but I don`t know
> where
> expose it.
> I have a local Sql Server database running under W2000 server. This
> machine
> have permanent Internet access . Can I connect a Visual Basic program
> running
> in a remote PC machine to the Sql Server Database, directly through the
> Web ?
> I would appreciate any suggestion or reference to technical article.
> My best regards.
> Roberto|||Tony:
Thanks very much.
Roberto

How access SqlServer from the Web

Hello:
I apologize my question involves some Web contents, but I don`t know where
expose it.
I have a local Sql Server database running under W2000 server. This machine
have permanent Internet access . Can I connect a Visual Basic program running
in a remote PC machine to the Sql Server Database, directly through the Web ?
I would appreciate any suggestion or reference to technical article.
My best regards.
RobertoYes, you can do that.
My recommendation is to use a VPN to the SQL Server rather than directly (if
possible) and lock down the IP address of the client machine on the server
firewall.
Alternatively you can open up the port that SQL Server listens on (incoming)
to the SQL Server machine and open all (outgoing) from the SQL Server
machine. I'd set SQL Server to use a different port other than the default
1433, say 2025 or something (on the server use the Netowkr Configuration
Utility in the SQL program group) - you can get the client to use that port
using the client config utility on the client.
Hope that helps.
Last but not least, get it on SQL Sp4.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Roberto Carabajal" <RobertoCarabajal@.discussions.microsoft.com> wrote in
message news:849989E8-B257-4E25-9A2F-2E86CA164C67@.microsoft.com...
> Hello:
> I apologize my question involves some Web contents, but I don`t know
> where
> expose it.
> I have a local Sql Server database running under W2000 server. This
> machine
> have permanent Internet access . Can I connect a Visual Basic program
> running
> in a remote PC machine to the Sql Server Database, directly through the
> Web ?
> I would appreciate any suggestion or reference to technical article.
> My best regards.
> Roberto|||Tony:
Thanks very much.
Roberto

How about getting a dedicated server to run and manage SQL jobs

Hi all:

Give me some feedback.

In my company, we have 10+ sql servers and many sql jobs running on every SQl server.

What about the idea that running all jobs on a dedicated sql servers?

at least 2 advantages

1.easy to managment

2.if agent server down, we restart the server not to impact the downtime of the production server.

any suggestion is welcome.

We had a similar setup which worked well.
Basically had big machines for SQL server and then used blades for "distributed" SSIS jobs.
All you really need is fast procs, lots of memory and not much disk space.
The idea was that as one box would become loaded, we would introduce another. All boxes would have the same jobs on it, but some disabled. The packages were run from a network location so should a box die, we can switch over quite quickly.

The issues are:
Licensing
Network performance
Cost

But if those are not a problem, it is a nice solution.

How (un)secure is my remote SQL Server?

I've been provided with a server at a hosting company. The server is running W2K3 SP2 in its own workgroup (i.e., non-AD) configuration, but is not behind any type of hardware firewall; there is no VPN in place, either. I connect to the server via RDP using an extremely long and complex password. I'm using the newest version of the RDP client. The article "Hacking RDP" and the ensuing reader comments (http://mcpmag.com/columns/article.asp?EditorialsID=1699) indicate that using RDP in this fashion is relatively safe.

I installed SQL Server 2005 SP2 on this server. I set server authentication to 'SQL Server and Windows Authentication mode'. I created one obscure SQL Server login, using another extremely long and complex password. I also disabled the login for the 'sa' account.

Since installing SQL Server on this server, I've noticed thousands of Failure Audit events in the server's Application log:

Source: MSSQLSERVER

Description: Login failed for user X

where X equals 'administrator', 'root', 'server', 'database' 'sql', 'sa', etc.

These failure events occur almost non-stop, about a dozen per second, and come from a small pool of unknown IP addresses. The IP address seems to change every few hours. I'm guessing that someone is hoping that one of these names is an actual SQL Server login and is trying a brute-force attack to try to stumble upon a matching password. None of these logins are valid, but it's still disconcerting. Is this anything to be concerned about? I could have the hosting company block the IP addresses, but that seems like a losing battle.

Lastly, I used the Surface Area Configuration tool to allow local and remote connections, using TCP/IP only--so that I could begin interacting with this SQL Server from my PC, using both SQL Server Management Studio and my own Visual Studio code. For each method, I'm using the obscure SQL Server login that I created earlier--the one with the extremely long and complex password. How (un)secure is my traffic to/from this SQL Server? I don't believe that my credentials are encrypted, but I'm not sure how much of a risk this is nor do I know how else to more securely connect to SQL Server.

Given these circumstances, is there any way to make this resource more secure? Thanks!

Every hacker in the world (or so it seems) has automated tools that are always pinging port 1433.

You can increase your 'invisibility' by changing the IP port -preferrably to a high IP number, and make sure that port 1434 is closed on the windows firewall, and that the SQL Browser service is not running.

You are correct, that without a VPN, or SSL connection, you are sending the SQL password in clear text across the wire -in the context of your RDP connection.

While you have a small vulnerablity, it is relatively insignificant. Should you be concerned? If the database has sensitive data, protected data, or heads would roll if the data was hacked -get a VPN (or SSL) connection. Most hackers are seeking the 'low hanging fruit' of open and unprotected servers.

|||

Thanks. Please pardon my ignorance. How does blocking port 1434 on the Windows firewall help? Are there any negative side-effects of stopping the SQL Server Browser service?

>>You are correct, that without a VPN, or SSL connection, you are sending the SQL password in clear text across the wire -in the context of your RDP connection.

I think I was unclear in my original explanation. I'm using SQL Server Management Studio and Visual Studio-based applications from my PC to connect (via clear text) to the server's SQL Server over the Internet--not via the RDP connection. I mentioned RDP just in the context of how I administer the server, when necessary; RDP is not in use otherwise.

I'm not sure where to begin with a VPN; do you have any recommendations? The hosting company mentioned OpenVPN, but I'm not making much progress with that. I might make better progress via a SSL connection. Can you recommend any references on how to get started with that--in particular, regarding SQL Server traffic? Or any articles in general that apply to my kind of scenario? (unprotected server at a hosting company)

|||

If you are managing the SQL Server 'in the wild' (and not over the RDP) then you are really exposed and totally dependent upon the strength of your passwords and security model used in SQL Server.

Whatever solution you settle upon, it will have to be something that the hosting company is willing to deal with and support -since it has to be on their end and you probably cannot physically go there and set it up or mainain it.

If they suggested OpenVPN, I would start exploring that product. Personally, I have no experience with it.

Setting up SSL should be something that the host company should easily handle -but there is a cost for the certificate. (But not too onerous for the level of 'security' it provides.)

|||

Thanks again, Arnie. I forgot to mention that the SQL Server Browser service is already set to Disabled.

Your earlier comment got me thinking. I configured the Windows firewall to block all traffic other than port 3389 (RDP)--and that, with only the somewhat-static IP address of my PC. Of course, this had the immediate side-effect of preventing me from accessing SQL Server via SQL Server Management Studio on my PC. Next, I further configured the Windows firewall to allow port 1433 traffic--again, with just the IP address of my PC. SQL Server Management Studio was then able to connect to the server. That seems about as secure as I know how to make it for the time being. Now I just have to make sure that the IP address of my PC doesn't change!

I've opened another dialog with the hosting company. It's not clear to me how setting up a VPN will thereafter protect the server; is it that the server no longer responds to any other traffic other than that over the VPN port?

I don't mind purchasing an SSL certificate; just by itself, however, it's not clear to me how that will protect the server as a whole--like a VPN might. Wouldn't SSL result in just SQL Server traffic being encrypted?

|||

It may be time to bite the bullet and pay the extra for a static IP address (business expense and all...)

A VPN tunnel is somewhat similar to the RDP connection -with the extension that what is on the other end of the tunnel appears to be local to your network/computer. ALL internet traffic is encrypted between the endpoints. It is impossible for anyone to 'sniff' your packets and pick out data (or passwords).

For this usage, I think that VPN may be a better option that setting up SSL -unless your business needs SSL for its website.

|||

I agree that a VPN is the way to go. I've begun perusing the OpenVPN documentation, but it's a bit disjointed.

Can you recommend a different VPN?

I can't help but wonder whether Windows' RRAS might work; however, I have yet to find any helpful documentation about that feature.

Hotfix for SP1 didn't work. There is still no Select All option.

I installed SP1 on a box running SQL Server 2005. I was puzzled as to why the Select All option had disappeared on my multi-value parameters. I found out on on this message board that the SP1 install removed this option. I contacted Microsoft yesterday and after being on hold for over 75 minutes, I was asble to speak with someone who sent me an email containing a link to the hotfix download. I installed the hotfix, rebooted the server, and my reports still do not have the Select All option. Has anyone else had this problem? I sent an email to the support rep I was in contact with at Microsoft but I was hoping that someone here might have some suggestions while I wait to here from Microsoft.

Any thoughts would be greatly appreciated. Thanks!

Amedeo

The removal of Select All is by design, so I would recommend just using the work around rather than trying the hotfix. I modified all my reports after SP1, and it isn't a big deal to implement. Here's a starting point.

http://msdn2.microsoft.com/en-US/library/aa337234.aspx

|||

If I understand your suggestion correctly, you are stating I should try the "ALL" parameter in place of the (Select All) option once provided by SSRS. I followed the article listed below and it worked great. Although it is a really good workaround, the reason I want the (Select All) that originally came with SSRS is because when you choose or unchoose (Select All) it checks or unchecks all of the boxes for the other parameter choices.

http://blogs.msdn.com/chrishays/archive/2006/04/18/AllParameterValueRedux.aspx

I'm working with an engineer from Microsoft right now as to why the hotfix didn't work. I'll be sure to post the solution assuming there is one.

|||I don't know how difficult it would be, but maybe you could write some custom code that would select all your multi-select options. Good luck on finding the answer.|||

I worked with several different people at Microsoft regarding this issue and I wanted to share the solution. Just to clarify my initial post, there were several patches I had to run in order to install the hotfix. One of the patches would not successfully install. The problem was that IIS still thought it had a SSL certificate when it really didn't.

Clean up the certificate by going to IIS Manager and removing the certificate from IIS. When you rerun setup, it should succeed. This link was very helpful.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=90906&SiteID=1

|||Where can I get the patches?|||If you would like to get a patch before SP2 is released, you have to
contact Support Services and ask for Knowledge Base Article #919478