Solved

Setup another database for my application

Posted on 2014-10-26
14
115 Views
Last Modified: 2014-11-04
Hello there,

I have a server with windows server 2003 on it. I have my application which is using sql server 2008. This server is used by many users and as my users are increasing I have seen drop in performance because the sql server 2008 is providing service to all different users(Web site, desktop application, reports). Now I want to setup another server with its own sql server 2008 and shift some of the users(website and reports) to this new server. My problem is how can I every night at a particular time update this new server's DB. So the next morning the website/reports users will see yesterdays data up to date. Please help me. I have been reading on the web but I am little confused they talk about mirroring and replication.
0
Comment
Question by:zolf
  • 5
  • 5
14 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40405723
I assume that you already have some procedures in place for updating those tables required for web and reporting, so what you can do is setup new server and add old server as linked server and then on the new server start those procedures with data source based on your old server by using linked server... or you can complete those procedures on the old server and later copy them to new server...
0
 

Author Comment

by:zolf
ID: 40405727
Thanks for your comments.I have 3 application as i mentioned. the main application(where the users are entering data), the website and the report application. All of them are using the same database. my goal is to reduce the workload on this main server and shift the website and report to another server with its OWN DB. this new server does not have to have data to to minute. It can have one day old data on it. So I want some way to update the database on this new server every night. so next morning the website users and the report users can use this server.
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40405729
Basically, you can prepare those tables on the primary server and then copy/transfer them to new server by using something like this

drop table server2.repo.dbo.table1

insert into server2.repo.dbo.table1
select * from server1.core.dbo.table1
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:zolf
ID: 40405753
I want to transfer the full database from my primary server to this new server and most important it has to be done automatically in the night when no one is using the application
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40405781
You have to take into consideration size of the DB and the time required to move the file(s)  between two servers.. There are batch scripts which can do the job of automatic backup, transfer and restore ...
0
 

Author Comment

by:zolf
ID: 40405811
can you provide me the link or if you have the script to do those tasks
0
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 80 total points
ID: 40405819
No scripts but have the links ;)

http://www.howtogeek.com/50295/backup-your-sql-server-database-from-the-command-line/

serverfault.com/questions/522363/backing-up-database-fully-from-remote-server-and-restoring-to-another-remote-se

etc...
0
 

Author Comment

by:zolf
ID: 40405888
Thanks for those links. Can you please help me to write the script.Lets take this script and elaborate

I have one server with IP - 192.168.0.2 and I take full backup on the D drive(D:\backup) of this server every night.
The new server IP is 192.168.0.3 and I have setup sql server 2008 in this server and want to restore its db with the backup which i have on that 192.168.0.2(D:\backup). How will the belwo script look like.

@ECHO ON

set backupscript=c:\path\backup.sql
set restorescript=c:\path\restore.sql

set livestore=\\server\share
set devstore=\\server\share

set liveserver=server\inst
set devserver=server\inst


sqlcmd -E -S%liveserver% -i "%backupscript%"
copy "%livestore%\*" %devstore%
sqlcmd -E -S%devserver% -i "%restorescript%"

Open in new window

0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40405909
Hi,

taking the credits into consideration and maybe for you to understand and learn the script you can check this link:

http://www.codeproject.com/Articles/110908/SQL-DB-Restore-using-PowerShell
0
 

Author Comment

by:zolf
ID: 40406029
This link was not helpful
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 420 total points
ID: 40422317
After copying over the database one time, why not use  Transactional Replication?

Also you may want to consider Micro$oft's: SQL Server Replication

:p
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question