Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Sql Server Setup, Linking to application
My firm is doing our first SQL Sever project so we are definitely NUB's.
MS Access 2013 is the Front End and we are using SQL Server Developer 2014 for the backend DB.
Our first step was to install SQL Server Developer 2014 on each of the workstations involved in the project. After that I created a 'File' DSN on the local machine and the developers were able to link to the SQL DB and do some preliminary testing/conversion work. The only DB's the local machines could use, was the one on their machine.
This setup has been working well to get the staff familiar with using SQL Server.
The next step is to install SQL on our office server. This step has been completed, so the DB is sitting on the server but none of the local machines can see it.
Now we need the individual workstations to be able to see/link to the SQL Server DB on our office server. At this point I'm unsure of the next step, which I believe is setting up DNS(s) to link to SQL on our office Server.
Do each of the individual machines set up there own DSN to link to the instance on the server, or is the DSN set up on the server and referenced by the local machines?
I see three types of DSN's, 'User', 'System' and 'File'. As I mentioned above, up to this point we have created 'File' DSN's on each of the local machines which connect to the SQL DB on the local machine. What type of DSN will be used to connect to the SQL DB on our office Server?
Any help with the next step would be appreciated.
MS Access 2013 is the Front End and we are using SQL Server Developer 2014 for the backend DB.
Our first step was to install SQL Server Developer 2014 on each of the workstations involved in the project. After that I created a 'File' DSN on the local machine and the developers were able to link to the SQL DB and do some preliminary testing/conversion work. The only DB's the local machines could use, was the one on their machine.
This setup has been working well to get the staff familiar with using SQL Server.
The next step is to install SQL on our office server. This step has been completed, so the DB is sitting on the server but none of the local machines can see it.
Now we need the individual workstations to be able to see/link to the SQL Server DB on our office server. At this point I'm unsure of the next step, which I believe is setting up DNS(s) to link to SQL on our office Server.
Do each of the individual machines set up there own DSN to link to the instance on the server, or is the DSN set up on the server and referenced by the local machines?
I see three types of DSN's, 'User', 'System' and 'File'. As I mentioned above, up to this point we have created 'File' DSN's on each of the local machines which connect to the SQL DB on the local machine. What type of DSN will be used to connect to the SQL DB on our office Server?
Any help with the next step would be appreciated.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Before Setting up a DSN, I'd make sure the SQL server is even accessible.
To start, make sure that the SQL Server Browser service is up and running (You can check all services in services.msc)
Also, I'd make sure that the server is listening to remote connections over port 1433, you'll need to check the configuration manager.
Once you've done that and once you are certain there are no firewall blocks over port 1433, go to any workstation and create a simple text file on the desktop, then change the extension to UDL which allows you to test connections to an SQL server.
To start, make sure that the SQL Server Browser service is up and running (You can check all services in services.msc)
Also, I'd make sure that the server is listening to remote connections over port 1433, you'll need to check the configuration manager.
Once you've done that and once you are certain there are no firewall blocks over port 1433, go to any workstation and create a simple text file on the desktop, then change the extension to UDL which allows you to test connections to an SQL server.
1st try to ping the "main" sql server box from user(s) PC
2nd check if sql server service on the "server" is running ( check windows services)
2.1 check if sql server network protocols are set\enabled (e.g. TCP\IP)
3rd check with person who installed this sql server -- the sql server name to be able to connect it
and sql login with DB permissions was set :AD group based or sql login...
4rd try to connect the new sql server from developer's PC by using SSMS (click SQL Server Management Studio)
check
Lesson 1: Connecting to the Database Engine
https://msdn.microsoft.com/en-us/library/ms345332(v=sql.120).aspx
Lesson 2: Connecting from Another Computer
https://msdn.microsoft.com/en-US/library/ms345343(v=sql.120).aspx
5th set MS Access link to sql server
see video how-to set it
Linking MS Access to MS SQL Server 2008 R2
https://www.youtube.com/watch?v=4bLRsYgtL-Q
more
https://support.office.com/en-us/article/Link-to-SQL-Server-data-0474c16d-a473-4458-9cf7-f369b78d3db8
2nd check if sql server service on the "server" is running ( check windows services)
2.1 check if sql server network protocols are set\enabled (e.g. TCP\IP)
3rd check with person who installed this sql server -- the sql server name to be able to connect it
and sql login with DB permissions was set :AD group based or sql login...
4rd try to connect the new sql server from developer's PC by using SSMS (click SQL Server Management Studio)
check
Lesson 1: Connecting to the Database Engine
https://msdn.microsoft.com/en-us/library/ms345332(v=sql.120).aspx
Lesson 2: Connecting from Another Computer
https://msdn.microsoft.com/en-US/library/ms345343(v=sql.120).aspx
5th set MS Access link to sql server
see video how-to set it
Linking MS Access to MS SQL Server 2008 R2
https://www.youtube.com/watch?v=4bLRsYgtL-Q
more
https://support.office.com/en-us/article/Link-to-SQL-Server-data-0474c16d-a473-4458-9cf7-f369b78d3db8
Sorry, I haven't abandoned the question just haven't had a chance to try anything of the suggestions yet.
SQL Server is installed remotely so to access it from a remote workstation and since everybody has SQL Server Management Studio (SSMS) installed you just need to open SSMS and when requesting you to give the server name just replace the local server name with the remote server name.
For this work you'll need to have SQL Server Browser service running in the server side and if the server is behind a firewall allow the port TCP 1433 and UDP 1434 in the firewall.
For this work you'll need to have SQL Server Browser service running in the server side and if the server is behind a firewall allow the port TCP 1433 and UDP 1434 in the firewall.
Thanks for all the suggestions although I don't really know how to implement most of the them. I'm trying to locate someone in the area that can assist me with this.
The person that installed SQL Server on the office server is unfortunately no longer available.
The person that installed SQL Server on the office server is unfortunately no longer available.
I am getting abandoned notices on tis question. I have not abandoned it but have not been able to implement any of the above suggestions yet.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial