Solved

Setup another database for my application

Posted on 2014-10-26
14
117 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

828 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