?
Solved

SQL Server Reports for all Branches

Posted on 2011-05-09
11
Medium Priority
?
317 Views
Last Modified: 2012-05-11
I have an application installed on each branches which uses SQL Server 2008 R2 Express to store about 1000 records per day. Now, I need to be able generate a single report for all branches on the head office.

What is the best way to accomplish this?
0
Comment
Question by:Thomasian
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 35725982
Thomasian,

A good way to do this is to have a reporting server that you replicate data from the branch offices to.  That way you can do your reporting on static data versus against the transactional datasets across WAN.  You can do this at low network utilization times and just report on data historically, i.e., as of previous night.

If you need realtime reporting, on server in your main office you can have linked servers to the other servers and run your query joining the data sets.

Hope that helps!
0
 
LVL 22

Author Comment

by:Thomasian
ID: 35726014
Thanks for the suggestion.

What about the database structure at the server?

Do I create a single table for all transactions or separate table for each branch?

If I create a single table, then I will need another field for the branchId. How will the replication work then?

If I create separate tables, then would I have to create dynamic queries to join all the tables?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35726037
If you are always looking at the data as one collective, then probably would work well as one.  You can use partioning or separate via some other criteria like by period (i.e., month, quarter or year) for example.  Guess you could have multiple tables and then create a view that brings these together as one.  Since you would be using UNION there you could add in the branchid at that point.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 22

Author Comment

by:Thomasian
ID: 35726146
>>You can use partioning or separate via some other criteria like by period (i.e., month, quarter or year) for example.
Can you explain what you mean? Do I create a table new table for each period of time?


Let's say I need to get the average transactions per day for each branch for the month of january.

Do I create a view "unioning" all the tables adding a "branch" column, and then group by the branch? If so, will it be able to use the indexes?
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35726614
With SQL Server 2008 R2 Express you can't use replication. Unless, perhaps, when the main office has a Standard, or higher, version of SQL Server. I suppose the branches are connected to the main office, and in the same domain. If not you need complete different techniques. But if they are you might be able to use linked server. This way you can link the databases of your branch offices to the sql server of your main office. At the main office you can use a union query to combine the data of all the branch offices.
0
 
LVL 22

Author Comment

by:Thomasian
ID: 35726683
>>This way you can link the databases of your branch offices to the sql server of your main office.

So I can just install a SQL Server Express at the main office and use that to create linked server to the branch offices?
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35726717
If you can access the branch servers from the main office, yes.
http://msdn.microsoft.com/en-us/library/ms188279.aspx
0
 
LVL 22

Author Comment

by:Thomasian
ID: 35726751
What happens when one of the servers is not online?

Is it possible to just ignore it and display the result for the connected servers?
0
 
LVL 22

Assisted Solution

by:Nico Bontenbal
Nico Bontenbal earned 1000 total points
ID: 35727267
I tested it and it seems possible when you use try catch and dynamic sql. I used this script:
declare @sql varchar(1000)
create table #tmp(branch varchar(10), cnt int)
begin try
set @sql = 'insert into #tmp(branch, cnt) select ''branch1'', count(*) from [Branch1Server].dbname.dbo.tblTest'
exec (@sql)
end try
begin catch
end catch
select * from #tmp
drop table #tmp

Open in new window

You could repeat the code between begin try and end catch for every branch. Or use a cursor over the query:
select * from sys.servers where is_linked = 1
so all your linked server are queried automatically and you only need to add or remove linked servers.
I tested it by stopping sql server on the linked server. I don't know if other issues like networking would create the same results.

You might be able to use OPENROWSET instead of linked server, but I think you are better of with linked servers.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35727827
Need to install SQL Server express with Advance service to get BIDS and SSRS in it.

0
 
LVL 22

Author Closing Comment

by:Thomasian
ID: 35743642
Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

839 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