Managing SQL Server remotely

I've combed through SQL Help to find the answer to my question but I think it's telling me it can't be done. I work both from an office with my servers and from home. When I'm at home I would like to access my SQL server remotely using a tool such as MS SQL Server Management Studio. But it appears there is no way to access my SQL Server for management purposes using Management Studio over a remote internet connection. I can access the server using Management Studio while I'm on the internal office network but not from home.

Has anyone been able to do this or might recommend a third party tool as robust as Management Studio?

Thanks

From what I know, either you can remote into that machine and manage your SQL Server, or if your SQL Server has a dedicated IP you can register the server on your local machine and manage the server from there.

|||

I can find no place in Management Studio (linked or registered) to enter an IP number? If anyone stumbles on documentation to pull this off I'd sure appreciate it. It appears all references to 'remote' in the help files means within the same internal network.

Again, a third party tool is doable.

|||

I used File > Connect Object Explorer..., and in the 'Server Name' field, typed the IP of my Database server.

|||

So you have been able to connect to SQL off your network usng Management Studio. That is good news. However, I keep getting errors but I probably don't have all my ducks in a row on settings. I tried a raw IP, then raw IP with server name (67.81.4.38/ServerName) and then with the http (http://67.81.4.38/SeverName) and still no luck.

I get the feeling I need to open a port or two on the server. Some documentation sure would help. I'll keep at it but if anyone knows of some good documentation please let me know. Thanks.

TITLE: Connect to Server
----------

Cannot connect to XX.XX.XX.X.

----------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

|||

Hi,

You may try these steps below.

1, Enable the remote connection of your SQLServer. Start->Microsoft SQL Server 2005->Configuration Tools->SQL Server Configuration Manager-> select SQL Server 2005 Network Configuration, right click on the "TCP/IP", enabled.

2, you can use ping command to check if the remote server can be accessible
3, because the default port of SqlServer is 1433. So you can use telnet command (such as telnet x.x.x.x 1433) to check if the port works. If error occurs in this step, you may
try to check the following issue:

1) If the SqlServer service is running on your remote machine
2) Check if the SQL Server port number is 1433. (If not, you may use your customerize port and retry step2 ).
3) Open your firewall, check if the 1433 port has been forbidden.

4, Try to use enterprise manager or query analyzer to connect the romote server.If the error still occurs, maybe the authentication mode on your remote SqlServer is Windows Authentication Only.You should enable the SqlServer authentication and restart yourSqlServer. (Right click on your server node, choose properties, switch to security tab, choose "SQL Server and Windows Authentication Mode")

Hope that helps. Thanks.

|||

Thank you so much. I'm going to go in tomorrow and try it out. You'll probably be hearing back from me.

Managing large database

I am developing an office automation software for a government department. To start with, I have decided to first automate the salary section of the department. Because of some issues like, TCO, easy support and maintenance, scalability etc., I have decided to use SQL Server 2005 database with VB 2005 front-end.

I have planned the layout for this first part and found that in only employee salary table, 40,000 records per month will be stored. That comes out to be around 480,000 records annually. This is for one table alone, excluding data in other tables.

Since each section of the department will be integrated into this later, this application will become the backbone the department. The employee service, leave, salary, allowance, deduction and other records shall be maintained.

For this type of mission-critical application, I want to get some queries cleared:

(1) What backup strategy should be followed? I want to have schedule and maual backups both. Is there any way to have a mirror image on another server?

(2) What considerations to keep in mind in the inital stage of database design?

(3) How to keep the design scalable and configurable to meet future needs?yes you can have a mirror image of the database in another server by configuring log shipping or database mirroring both are high availability and disaster recovery solutions............you can refer the below articles for the same,
www.sql-articles.com
www.sql-articles.com/articles/dbmrr.htm and
www.sql-articles.com/articles/lship/lship.htm regarding the rest i am not sure........you can configure mirroring or log shipping depending on the criticality of the database........
|||Initially, I would like to start with the Express Edition. Does Express Edition provides these features? I also want to know whether SQL Server 2005 now supports Multi-Version Concurrency Control (MVCC) now?|||

For database mirroring you can have your monitor server as express edition but the partners need to enterprise or std edition.......for log shipping you need to have either enterprise,std or workgroup edition..........

|||

Those are fairly broad questions but in regards to the other issues, a backup strategy is determined by the business needs. You need to look at any SLAs that will be in place, issues of data loss, time to recover, frequency of inserts and updates, frequency of log backups, location of backups, size of the database and backups, if backups go to tape or network storage, etc. A general starting point for many databases is a daily full backup and then log backups depending on many of the already mentioned factors. Having both scheduled and manual backups - I'm not sure what you mean by this. Most backup routines are scheduled, automated. You can always do an ad hoc or manual backup in addition. And you generally do additional backups prior to activities such as applying service packs, implemented changes.

The initial considerations would be to just follow normal database development practices in terms of normalization, keys, selecting appropriate data types for columns, placement of log and data files, usage of tempdb, security considerations, etc. Chosing your indexes and accounting for index space would be important as well as considering potential data archiving strategies that may be needed. You'd want to consider other maintenance tasks as well such as regular integrity checks, reorgs or rebuilds of your indexes to address fragmentation. You would want to consider the server as a whole, not just the one database and any impacts the various database have on each other, system resources.

-Sue

|||

Sue has given you lots of good info to consider.

I'd also add that you probably want to go with at least Workgroup Edition for a business-critical database such as this.

There are limitations built into Express which you will run into sooner or later (such as the 4GB limit on DB size).

Don't design your overall solution for a small corner and then grow - design for your eventual big picture and fill in the pieces as you get to them.

Managing large database

I am developing an office automation software for a government department. To start with, I have decided to first automate the salary section of the department. Because of some issues like, TCO, easy support and maintenance, scalability etc., I have decided to use SQL Server 2005 database with VB 2005 front-end.

I have planned the layout for this first part and found that in only employee salary table, 40,000 records per month will be stored. That comes out to be around 480,000 records annually. This is for one table alone, excluding data in other tables.

Since each section of the department will be integrated into this later, this application will become the backbone the department. The employee service, leave, salary, allowance, deduction and other records shall be maintained.

For this type of mission-critical application, I want to get some queries cleared:

(1) What backup strategy should be followed? I want to have schedule and maual backups both. Is there any way to have a mirror image on another server?

(2) What considerations to keep in mind in the inital stage of database design?

(3) How to keep the design scalable and configurable to meet future needs?yes you can have a mirror image of the database in another server by configuring log shipping or database mirroring both are high availability and disaster recovery solutions............you can refer the below articles for the same,
www.sql-articles.com
www.sql-articles.com/articles/dbmrr.htm and
www.sql-articles.com/articles/lship/lship.htm regarding the rest i am not sure........you can configure mirroring or log shipping depending on the criticality of the database........
|||Initially, I would like to start with the Express Edition. Does Express Edition provides these features? I also want to know whether SQL Server 2005 now supports Multi-Version Concurrency Control (MVCC) now?|||

For database mirroring you can have your monitor server as express edition but the partners need to enterprise or std edition.......for log shipping you need to have either enterprise,std or workgroup edition..........

|||

Those are fairly broad questions but in regards to the other issues, a backup strategy is determined by the business needs. You need to look at any SLAs that will be in place, issues of data loss, time to recover, frequency of inserts and updates, frequency of log backups, location of backups, size of the database and backups, if backups go to tape or network storage, etc. A general starting point for many databases is a daily full backup and then log backups depending on many of the already mentioned factors. Having both scheduled and manual backups - I'm not sure what you mean by this. Most backup routines are scheduled, automated. You can always do an ad hoc or manual backup in addition. And you generally do additional backups prior to activities such as applying service packs, implemented changes.

The initial considerations would be to just follow normal database development practices in terms of normalization, keys, selecting appropriate data types for columns, placement of log and data files, usage of tempdb, security considerations, etc. Chosing your indexes and accounting for index space would be important as well as considering potential data archiving strategies that may be needed. You'd want to consider other maintenance tasks as well such as regular integrity checks, reorgs or rebuilds of your indexes to address fragmentation. You would want to consider the server as a whole, not just the one database and any impacts the various database have on each other, system resources.

-Sue

|||

Sue has given you lots of good info to consider.

I'd also add that you probably want to go with at least Workgroup Edition for a business-critical database such as this.

There are limitations built into Express which you will run into sooner or later (such as the 4GB limit on DB size).

Don't design your overall solution for a small corner and then grow - design for your eventual big picture and fill in the pieces as you get to them.